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.)

[Former Member]

The answer will depend on what the question is - are you looking for rows that are the same, rows that are different, or both?

See the answers in this thread that can get you started.

[Former Member]

I'll try to clarify. If you are looking in two tables to see if data in one field is the same in both tables is there a benefit to using a join or INTERSECT/EXCEPT. This could be if the fields are the same, different or both. Generally speaking. Will a join provide the same results as INTERSECT/EXCEPT?