on 2011 Apr 18 10:43 AM
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.
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:
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.)
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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?
User | Count |
---|---|
75 | |
10 | |
10 | |
10 | |
10 | |
9 | |
8 | |
7 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.