Write a SQL query that returns active users who have never placed an order.

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.

Users 1 and 2 are active. User 1 has one order and user 2 has none. The result contains only user 2.

• An order at any date means the user is not returned.
• Inactive users are excluded.
• The query must remain correct even if unrelated nullable columns exist.

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.

• No users in the system.
• An active user with several orders.
• An inactive user with no orders.
• Legacy order rows with a null UserId.

• Add a null UserId in Orders to expose a NOT IN implementation.
• Give one user many orders to catch unnecessary duplicate processing.
• Verify inactive users are filtered independently of the anti-join.

• Using NOT IN without accounting for nulls.
• Using a left join but forgetting WHERE o.OrderId IS NULL.
• Selecting order columns even though the rule only needs existence.
• Missing the supporting index on the foreign key.