CODEWITHSUNDEEP
sqlsql-serversql-except
Steve Gietz
Steve Gietz

Reputation: 71

SQL Server 2008 EXCEPT statement

Here is my example script:

SELECT c2, c3, c4 FROM Table1
EXCEPT
SELECT c2, c3, c4 FROM Table2

I'm successfully returning unique records from the left table that do not also exist in the right table. Both tables have identical schemas and for the most part identical data. The problem is that the unique id (let's call it column c1) does not match, so I need to exclude it in the EXCEPT query above. How can I return the same set of records, but with the unique IDs included?

I was thinking of using temporary tables, cursors and long WHERE statements inside the cursor, but that doesn't seem like a very elegant solution. is there another way to accomplish this seemingly simple task?

Upvotes: 0

Views: 3947

Answers (4)

Matt H
Matt H

Reputation: 640

Can you take your supplied query, and simply inner join it with table 1 to get your 'c1' column?

SELECT T1.* FROM Table1 T1 INNER JOIN(
  SELECT c2, c3, c4 FROM Table1
  EXCEPT
  SELECT c2, c3, c4 FROM Table2
) a on a.c2=T1.c2 and a.c3=T1.c3 and a.c4=T1.c4

Upvotes: 2

Philip Kelley
Philip Kelley

Reputation: 40309

This is kind of ugly and, on large tables lacking "useful" indexes, might perform very poorly, but it will do the work:

SELECT t1.c1, t1.c2, t1.c3, t1.c4
 from Table1 t1
  inner join (--  Unique tuples
              SELECT c2, c3, c4 FROM Table1
              EXCEPT
              SELECT c2, c3, c4 FROM Table2
             ) xx
   on xx.c2 = t1.c2
    and xx.c3 = t1.c3
    and xx.c5 = t1.c4

Upvotes: 0

Anssssss
Anssssss

Reputation: 3262

You probably can accomplish it using "NOT EXISTS" rather than "EXCEPT" since with "NOT EXISTS" you can specify conditions. Here's a thread that points this out: EXCEPT vs NOT EXISTS.

Upvotes: 1

rs.
rs.

Reputation: 27427

Try this

SELECT A.c1, A.c2, A.c3, A.c4 
FROM Table1 A
LEFT OUTER JOIN Table2 B ON A.c2 = B.C2 AND A.c3 = B.C3 AND A.c4 = B.C4
WHERE B.c1 IS NULL;

Upvotes: 1

Related Questions