EXCEPT Operator

Hello everyone,

I have two tables athat are structurally identical. I wish to obtain rows that are in the first table that are not in the second one (e.g. the EXCEPT operator). EXCEPT is not supported directly in TSQL...

Does anyone know how to do this?!?!?

Thank you for your time

Chris

SQL Server 2005 adds support for EXCEPT and INTERSECT set operators. So you can use it if you are running SQL Server 2005. Otherwise, you will have to use EXISTS like:

select *

from first_table as t1

where not exists(

select *

from second_table as t2

where t2.key_col1 = t1.key_col1

and t2.key_col2 = t1.key_col1

...

)

Note that the EXISTS approach is slightly different in that you match against the key columns. The EXCEPT operator works based on whatever columns you have in the SELECT list which can be just the key columns. If you want to match on the non-key columns then you can replace the key columns in the WHERE clause with those.

|||

In case there are duplicate rows in first_table (admittedly a bad idea), you need DISTINCT to match the behavior of EXCEPT: select DISTINCT * from first_table as t1 where not exists( select * from second_table as t2 where t2.key_col1 = t1.key_col1 and t2.key_col2 = t1.key_col1 ... ) -- Steve Kass -- Drew University -- http://www.stevekass.com Umachandar Jayachandran - MS@.discussions.microsoft.com wrote:
> SQL Server 2005 adds support for EXCEPT and INTERSECT set operators. So
> you can use it if you are running SQL Server 2005. Otherwise, you will
> have to use EXISTS like:
>
> select *
> from first_table as t1
> where not exists(
> select *
> from second_table as t2
> where t2.key_col1 = t1.key_col1
> and t2.key_col2 = t1.key_col1
> ...
> )
>
>