The forum will be unavailable for maintenance at some point from Friday, April 13 at 19:00 EDT until Sunday, April 15 at 23:59 EDT. Downtime will be minimized but the exact timing is unknown.

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.

asked 18 Apr '11, 09:43

zippidydo's gravatar image

accept rate: 0%

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.
permanent link

answered 18 Apr '11, 11:29

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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

(18 Apr '11, 11:34) zippidydo
Replies hidden

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, from T1 full outer join T2 on = where is null or is null

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

(18 Apr '11, 11:49) Volker Barth

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.

permanent link

answered 18 Apr '11, 10:14

Glenn%20Paulley's gravatar image

Glenn Paulley
accept rate: 43%

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?

(18 Apr '11, 11:07) zippidydo
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:


question asked: 18 Apr '11, 09:43

question was seen: 1,046 times

last updated: 18 Apr '11, 11:49