Yesterday, we covered the INTERSECT operator and today I will talk about the opposite of that, the EXCEPT operator. This operator can be used to return records that exist in one table but not the other. Previously you could do this with the NOT EXISTS operator, but the syntax was a lot more code as you can see below.
SELECT Table1.Column1, Table1.Column2, Table3.Column3 FROM Table1
WHERE NOT EXISTS
(SELECT Column1, Column2, Column3 FROM Table2
WHERE Table1.Column1 = Table2.Column1
AND Table1.Column2 = Table2.Column2
AND Table1.Column3 = Table2.Column3)
That's a ton of code that can be simply replaced by the code below:
SELECT Column1, Column2, Column3 FROM Table1
EXCEPT
SELECT Column1, Column2, Column3 FROM Table2
This is another useful time saving operator that will prove useful in the future.
Read the complete post at http://www.dotnettipoftheday.com/blog.aspx?id=169