Table data comparison

[Former Member]

When comparing data (fields) between two tables is there an advantage to using EXCEPT, INTERSECT, or, in the case of looking for data in ONE field between two tables, can a JOIN of the two tables accomplish the same thing?

ex: //join two tables and look for matching fields: select t.FIELD, t2.FIELD from tableone t join tabletwo t where t.FIELD = t2.FIELD

ex2: //two tables comparing the same two fields using intersect:

ex3: //two tables comparing the same two fields using except:

Appreciate any help provided.

[Former Member · Accepted solution]

A join, by definition, will produce a result set with matches. EXCEPT will produce a result set containing differences.

INTERSECT is roughly equivalent to inner join except that:

1. INTERSECT handles NULL values quite differently than join does, and
2. the number of rows you get in the result won't match the join either, in general.

[Former Member · Accepted solution]

I think I got it. Difference being the handling of NULL. Much appreciated.

[VolkerBarth · Accepted solution]

That being said, an OUTER JOIN can also be used to produce a difference, as long as you just select those rows that have no match (or differ in other ways), say

select T1.pk, T2.pk from T1 full outer join T2 on T1.pk = T2.pk where T1.pk is null or T2.pk is null

(However, IMHO, EXCEPT would be much more comprehensible here.)