↓Skip to main content
  1. LeetCode/

LeetCode 183: Customers Who Never Order

·1 min· ·
LeetCode Easy Database
Wei Yi Chung
Author
Wei Yi Chung
Working at the contributing of open source, distributed systems, and data engineering.
Table of Contents

Meta Data
#

Difficulty: easy First Attempt: 2021-10-09

  • Submissions: Accepted (first try)

Approach
#

Find rows absent from another table (SQL Template 3): Customers LEFT JOIN Orders leaves order columns NULL when unmatched, filtered by o.id IS NULL. NOT IN (SELECT customerId FROM Orders) works too, but a NULL in the subquery makes NOT IN return nothing (SQL Template 2), so LEFT JOIN ... IS NULL or NOT EXISTS is safer.

My Attempts
#

Passed on the first try with NOT IN (SELECT CustomerId FROM Orders).

Solution
#

SELECT c.name AS Customers
FROM Customers c
LEFT JOIN Orders o ON c.id = o.customerId
WHERE o.id IS NULL;

Complexity
#

Near O(n + m) with an index.

Mistakes to Avoid
#

  • Test NULL with IS NULL, never = NULL.
  • NOT IN breaks when the subquery contains NULL.

Interview Explanation
#

I left-join Orders onto Customers and keep rows whose order columns are NULL, the customers who never ordered; NOT IN also works but misbehaves if the subquery contains NULL.

Related

LeetCode 175: Combine Two Tables
·1 min
LeetCode Easy Database
LeetCode 175 note: people without an address must still appear, so LEFT JOIN, leaving city and state NULL when unmatched
LeetCode 181: Employees Earning More Than Their Managers
·1 min
LeetCode Easy Database
LeetCode 181 note: self-join Employee on managerId = id to pair employees with managers, then compare salaries
LeetCode 182: Duplicate Emails
·1 min
LeetCode Easy Database
LeetCode 182 note: group by email and keep groups with HAVING COUNT(*) > 1
LeetCode 196: Delete Duplicate Emails
·2 mins
LeetCode Easy Database
LeetCode 196 note: it must be a DELETE, not a SELECT; keep only the smallest id per email and delete the rest
LeetCode 26: Remove Duplicates from Sorted Array
·2 mins
LeetCode Easy Array Two-Pointers
LeetCode 26 note: overwrite in place with a write pointer for the next slot, writing only values different from the last one written
LeetCode 14: Longest Common Prefix
·2 mins
LeetCode Easy Array String Trie
LeetCode 14 note: bound by the shortest string and check each position across all strings; the first mismatch gives the answer’s length