SQLTeam.com | Weblogs | Forums

T-SQL queries to simulate INTERSECT and EXCEPT but with records in the same table

sql2008

#1

Hi everyone,

Here's the scenario. I have a table (let's call it MyTable) that consists of four fields: Id, Source, FirstField, and SecondField, where Source only takes one of two values: Source1 and Source2.

The records in this table look as follows: http://i58.tinypic.com/ea2jcp.png (sorry for linking to an external image, but being a new user in this forum I am still not allowed to post images).

I need to return, using 3 different T-SQL queries:

1) Products that exist only in Source2 (in red above)

2) Products that exist only in Source1 (in green above)

3) Products that exist both in Source1 and Source2 (in black above)

For 1) so far I've been doing something along the lines of

SELECT * FROM MyTable WHERE Source=Source1 AND FirstField NOT IN (SELECT DISTINCT(FirstField) FROM MyTable WHERE Source=Source2)

Not being a T-SQL expert myself, I'm wondering if this is the right or more efficient way to go. I have read about INTERSECT and EXCEPT, but I am a little unclear if they could be applied in this case out of the box.

I hope I've explained myself, and would appreciate any tips or suggestions anyone can share. Thanks in advance.


Gabriel A. Cánepa



#2

--Products that exists in both
SELECT a.FirstField, a.SecondField
FROM MyTable a
inner join MyTable b
on a.FirstField = b.FirstField
WHERE a.Source='Source1'
AND b.Source='Source2'

--Products that exists only in source1
SELECT a.FirstField, a.SecondField
FROM MyTable a
LEFT OUTER JOIN MyTable b
on a.FirstField = b.FirstField
WHERE a.Source='Source1'
AND b.Source='Source2'
AND b.FirstField is null

--Products that exists only in source2
SELECT b.FirstField, b.SecondField
FROM MyTable a
RIGHT OUTER JOIN MyTable b
on a.FirstField = b.FirstField
WHERE a.Source='Source1'
AND b.Source='Source2'
AND a.FirstField is null


#3

EXCEPT and INTERSECT are made just for situations like this.

  1. Products that exist only in Source2 (in red above)

    select * from source2
    except
    select * from source1

  2. Products that exist only in Source1 (in green above)

    select * from source2
    except
    select * from source1

  3. Products that exist both in Source1 and Source2 (in black above)
    select * from source2
    intersect
    select * from source1


#4

SELECT FirstField, CASE WHEN MIN(Source) < MAX(Source) THEN 'Both tables' ELSE 'Only table ' + MIN(Source) END FROM dbo.MyTable GROUP BY FirstField