Join two tables on all columns to determine if they contain identical information-CodePudding

I want to check if tables table_a and table_b are identical. I thought I could full outer join both tables on all columns and count the number of rows and missing values. However, both tables have many columns and I do not want to explicitly type out every column name.

Both tables have the same number of columns as well as names. How can I full outer join both of them on all columns without explicitly typing every column name?

I would like to do something along this syntax:

select
  count(1)
  ,sum(case when x.id is null then 1 else 0 end) as x_nulls
  ,sum(case when y.id is null then 1 else 0 end) as y_nulls
from
  x
full outer join
  y
on
  *
;

CodePudding user response：

You can use NATURAL FULL OUTER JOIN here. The NATURAL key word will join on all columns that have the same name.

Just testing if the tables are identical could then be:

SELECT *
FROM x NATURAL FULL OUTER JOIN y
WHERE x.id IS NULL OR y.id IS NULL

This will show "orphaned" rows in either table.

CodePudding user response：

You might use except operators.

For example the following would return an empty set if both tables contain the same rows:

select * from t1 
except
select * from t2;

If you want to find rows in t1 that are different to those in t2 you could do

select * from t1
where not exists (select * from t1 except select * from t2);

Provided the number and types of columns match you can use select *, the tables' columns can vary in names; you could also invert the above and union to return combined differences.