Problem solve Get help with specific problems with your technologies, process and projects.

NOT IN subselect with a compound primary key

I have two identical tables in SQL Server. I want get a list of all the rows that exist in one table, but not in the other table. I have done this in the past using a subquery where I select the key where NOT IN the other table. The problem is that this table does not have a single primary key, but a primary key of two columns. I couldn't find a way to make the IN clause work with both columns. The only way I found to make this work was to do an outer join into a temporary table and then select out the rows that returned nulls in the two fields from the table1. Is there a way to do this without using a temp table?

You were so close! The answer involves an outer join and nulls, but no temporary table. You're going to kick yourself...

select T1.pkA, T1.pkB, T1.foo, T1.bar
  from table1 T1
left outer
  join table2 T2
    on T1.pkA = T2.pkA
   and T1.pkB = T2.pkB
 where          T2.pkA is null

In a left outer join, every row in the left table will be returned, together with rows from the right table that match, i.e. satisfy the ON condition(s). Rows which do not have a match from the right table will have nulls in the columns that come from the right table.

But then the WHERE clause comes into play. From the joined rows that are returned, use the WHERE clause to select only those rows where one of the primary keys from table2 is null. There's no need to check both keys; just one will do.

Of course, there's a much more elegant way to do it:

  select pkA, pkB, foo, bar
    from table1 
  select pkA, pkB, foo, bar
    from table2

The EXCEPT operator is similar to UNION, in that it operates on two sets, but instead of combining the sets produced from the two subselects, it figures out which ones are in the first set that aren't in the second. EXCEPT is a standard SQL operator, but not all databases support it.

For More Information

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.