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

declare @count int, @count2 int


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

select @count2 = count(distinct table2.col1)
  from table2
 where table2.col1 not in (select distinct table1.col1 
                             from table1)

select @count + @count2

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

Answer 3

select count(*) from (

select
table1.col1 from table1 left join table2 on table1.col1 = table2.col2
where table2.col1 is null

union

select table2.col1 from table2 left join table 1 on table2.col1 = table1.col1
where table1.col1 is null

)

Answer 4

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.