Posted by Hugo Kornelis on 09/21/06 20:25

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

• Next in forum: Point in Time Backup (impossible for some points?)
• Prev in forum: Re: SQL server connection
• Thread view: Re: UNION / INTERSECT / EXCEPT in SQL Server 2000

[Reply to this message]