UNION / INTERSECT / EXCEPT in SQL Server 2000

[James Foreman]

Hi,

I'm coming back to Sql Server after 4 years away, using other RDBMS,
and there's a few things I'm struggling to remember how to do (if I
could do them in the first place...)

Main amongst those is EXCEPT syntax.

In DB2, if I have two sets of data and I want to exclude the second set
from the first, I can do:

SELECT col1, col2, col3, ... colN
FROM table1
EXCEPT
SELECT col1, col2, col3, ... colN
FROM table2
;

But SQL Server balks at this. I've had a quick look in the T-SQL help
for EXCEPT, but I didn't find that particularly enlightening. Any
pointers as to how I should be doing this?

Thanks

James

[Ed Murphy]

James Foreman wrote:

In DB2, if I have two sets of data and I want to exclude the second set
from the first, I can do:

SELECT col1, col2, col3, ... colN
FROM table1
EXCEPT
SELECT col1, col2, col3, ... colN
FROM table2
;

Say table1 and table2 both have a primary key of col1, then do:

select table1.col1, table1.col2, table1.col3, ..., table1.colN
from table1
left join table2 on table2.col1 = table1.col1
where table2.col1 is null

[James Foreman]

Thanks. From looking around a bit more on this group, INTERSECT &
EXCEPT are implemented in SQL Server 2005, but not in 2000. What
you've said is fine where there's a primary key available, but we've
had situations where that isn't the case and EXCEPT has been an easier
way to deal with this (eg you get given a lot of customer address data
that nobody has bothered to form a key on, and you only want to read in
rows you haven't already got. You *can* do it by comparing columns,
but once you have lots of columns to compare the code gets rather
ugly).

Plus I find EXCEPT easier to read, but that's more personal preference
than a good reason...

James

[Hugo Kornelis]

On 21 Sep 2006 06:33:52 -0700, James Foreman wrote:

(snip)

>Plus I find EXCEPT easier to read, but that's more personal preference
than a good reason...

Hi James,

Try talking yoour boss into upgrading to SQL Server 2005 :-)

Alternatively, consider this alternative. Definitely not as clean as
just writing EXCEPT, but (esp. with long column lists) shorter than the
outer join approach:

SELECT col1, col2, col3, ... colN
FROM (SELECT col1, col2, col3, ... colN, 'table1' AS tab
FROM table1
UNION ALL
SELECT col1, col2, col3, ... colN, 'table2' AS tab
FROM table2) AS d
GROUP BY col1, col2, col3, ... colN
HAVING MIN(tab) = 'table1'
AND MAX(tab) = 'table1';
--
Hugo Kornelis, SQL Server MVP

[James Foreman]

Thanks Hugo, that's a neat way of doing it. I'm still badgering my
boss about getting 2005 - desperate to get my hands on MERGE INTO as
well...

[Hugo Kornelis]

On 3 Oct 2006 03:04:55 -0700, James Foreman wrote:

>Thanks Hugo, that's a neat way of doing it. I'm still badgering my
boss about getting 2005 - desperate to get my hands on MERGE INTO as
well...

Hi James,

Just FTR, there's no MERGE INTO in SQL Server 2005. Unfortunately. :-(

--
Hugo Kornelis, SQL Server MVP