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 except 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
- Dozens more answers to tough SQL questions from Rudy Limeback.
- The Best SQL Web Links: tips, tutorials, scripts, and more.
- Have an SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, SQL Server, DB2, object-oriented and data warehousing gurus are waiting to answer your toughest questions.