How do I query for foreign keys that don't match their constraints?

Question

SQL Server 2005.

I'm adding Foreign Key constraints to the database of an application that allegedly didn't need them. Naturally, the data has become unreliable and there are orphaned entries in the foreign key field.

Setup:
Two tables, TableUser and TableOrder. TableUser has Primary Key 'UserID', and TableOrder has Foreign Key 'UserID'.

How do I find the rows where TableOrder.UserID has no matching entry in TableUser.UserID?

For example, TableOrder.UserID has a value of 250, but there is no matching TableUser.UserID key for 250.

Answer 1

Here's one way:

select * from TableOrder where UserID not in (select UserID from TableUser);

There are many different ways to write this sort of query.

Answer 2

The other common approach is a left-outer join:

SELECT * FROM TableOrder o
LEFT OUTER JOIN TableUser u ON o.UserID = u.UserID
WHERE u.UserID is NULL

This query can also be useful without the where clause, to browse through and see the corresponding values (if they exist), and see which ones have no match.