intersect & except

[thesti]

hi,

how to find intersections and difference on two-sets operations?
can i use the intersect and except keyword?


thank you very much

[Brad Orders]

Hi Thesti

I am assuming you want to work this out in SQL Server and not .NET...

Here is the approach I would take:
Assuming I had TableA and TableB. I want to find out all the records in TableA that aren't in TableB, based on a column RecordId. First step, find out all the records in TableA that ARE in TableB:

SELECT a.RecordId
FROM TableA a
INNER JOIN TableB b
ON a.RecordId = b.RecordId

Then, expand the code so that you return the records that AREN'T in this resultset:

SELECT TableA.*
FROM TableA
WHERE TableA.RecordId NOT IN
(
SELECT a.RecordId
FROM TableA a
INNER JOIN TableB b
ON a.RecordId = b.RecordId
)

This can also be written as a OUTER JOIN with a WHERE TableB.SomeColu mn IS NULL. A query written this way will also run faster, but I find it harder to read, so I didn't write it that way.

HTH