Ways To Check For Non-Existence

Brent Ozar shows two methods for finding records missing associated child records:

You’re writing a query, and you wanna check to see if rows exist in a table.

I’m using the free Stack Overflow database, and I wanna find all of the users who have not left a comment. The tables involved are:

• In dbo.Users, the Id field uniquely identifies a user.
• In dbo.Comments, there’s a UserId field that links to who left the comment.

A quick way to write it is:

1 2 3

SELECT u.*   FROM dbo.Users u   WHERE NOT EXISTS (SELECT * FROM dbo.Comments c WHERE c.UserId = u.Id);

And this works fine. When you read the query, you might think SQL Server would run that SELECT * FROM dbo.Comments query for every single row of the Users table – but it’s way smarter than that, bucko. It scans the Comments index first because it’s much larger, and then joins that to the Users table.

But this isn’t the only way to query these tables, and Brent shows how to tell which method works better.