All 7 Joins questions
More in Joins
What is an INNER JOIN, and how does it combine data from two tables? What is the difference between a LEFT JOIN and a RIGHT JOIN? What is a FULL OUTER JOIN, and when would you use one instead of a LEFT or RIGHT JOIN? What common mistakes do developers make when writing multi table joins that can lead to unexpectedly large or incorrect result sets? How does a CROSS JOIN work, and what are some legitimate use cases for it despite the risk of accidentally creating a huge result set? How does SQL Server's query optimizer decide which physical join algorithm, such as nested loops, merge join, or hash join, to actually use for a given query?
Advertisement
Back to results for “SQL Queries”
SQL Server › Joins

How do you find rows that exist in one table but have no matching row in another table using a join?

Intermediate

Answer

You use a LEFT JOIN and then add a WHERE clause checking that the joined column from the right table is NULL, which identifies exactly the rows from the left table that did not find any matching row in the right table during the join operation.
SELECT c.CustomerId, c.Name
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId
WHERE o.OrderId IS NULL;
Real-world example A marketing team identifies customers who have never placed a single order by using a LEFT JOIN combined with a WHERE clause checking for NULL order ids, targeting them with a special first purchase discount.

Common follow-ups: Is there a more modern alternative to this pattern using NOT EXISTS?;Does this approach perform well on very large tables?

Subqueries;Aggregate Functions & GROUP BY
All 7 Joins questions