Opening drill
Interview question
Find users who have never placed an order
Uses NOT EXISTS to express an anti-join without null traps or duplicate parent rows.
TL;DR
Uses NOT EXISTS to express an anti-join without null traps or duplicate parent rows.
Anti-joins, NOT EXISTS, null behavior, parent-row preservation, and index-aware filtering.
Practice the problem like a real interview: restate, reason, implement, and test.
Tables: Users(UserId, Email, IsActive) and Orders(OrderId, UserId, CreatedAt). Return active users for whom no order row exists. Sort by UserId so the result is deterministic.
Next in SQL Practice: Match Every Tag
Users 1 and 2 are active. User 1 has one order and user 2 has none. The result contains only user 2.
I express the business rule directly: keep the user when no correlated order exists. NOT EXISTS stops caring after it finds the first match and avoids the null-sensitive behavior of NOT IN. An index on Orders(UserId) gives the database a cheap existence check for each candidate user.
SELECT u.UserId, u.Email
FROM dbo.Users AS u
WHERE u.IsActive = 1
AND NOT EXISTS
(
SELECT 1
FROM dbo.Orders AS o
WHERE o.UserId = u.UserId
)
ORDER BY u.UserId;
With an index on Orders(UserId), the optimizer can use an anti-semi join and probe for a match. The query does not need to materialize or deduplicate joined order rows.
UserId.UserId in Orders to expose a NOT IN implementation.NOT IN without accounting for nulls.WHERE o.OrderId IS NULL.Share the question with a concise Core-answer excerpt and invite other developers to add their perspective.
Help improve the interview library.
Say thanks with a standalone, one-time $5 contribution. No account required.
Aporeon is shaped by Aleksandar Tomovski, a software developer with experience on both sides of technical interviews. Content is reviewed for accuracy, natural spoken delivery, useful depth, and honest trade-offs.