Verify two columns of two different tables match exactly

Question

When writing views and nesting views within deeper views I sometimes miss something and end up losing rows/data. How can I check that columns from two different tables have an exact match of data?

Example:

select count(distinct table1.col1)
  from table1
 where table1.col1 not in (select distinct table2.col1 
                             from table2);

This would return the number of values in table1.col1 that are not in table2. However, I don't know that this is a good solution as it doesn't count the table2.col1 values that do not exist in table1.col1.

Answer 1

You can use two EXCEPT queries (or union them) to check:

SELECT DISTINCT col1
FROM table1
EXCEPT
SELECT DISTINCT col1
FROM table2

That will show values that exist in table1, but not table2. Then run again with table names flipped for the opposite.

Answer 2

Use:

SELECT MAX(x.t1_missing_count) AS t1_missing_count, 
       MAX(x.t2_missing_count) AS t2_missing_count
  FROM (
SELECT COUNT(DISTINCT t1.col1) AS t1_missing_count,
       NULL AS t2_missing_count
  FROM TABLE_1 t1
 WHERE t1.col1 NOT IN (SELECT DISTINCT t2.col1 
                         FROM TABLE_2 t2)
UNION ALL
SELECT NULL,
       COUNT(DISTINCT t2.col1),           
  FROM TABLE_2 t2
 WHERE t2.col1 NOT IN (SELECT DISTINCT t1.col1 
                         FROM TABLE_1 t1)) x