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
*
;
2
Answers
You can use
NATURAL FULL OUTER JOIN
here. TheNATURAL
key word will join on all columns that have the same name.Just testing if the tables are identical could then be:
This will show "orphaned" rows in either table.
You might use
except
operators.For example the following would return an empty set if both tables contain the same rows:
If you want to find rows in t1 that are different to those in t2 you could do
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.