Abstract / Summary

Find customers who have not placed any orders from a list of customers and orders.

Note

The full problem description, including helpful examples, can be found on LeetCode.

This problem is ranked as

and has an acceptance rate of about 72% (1+ million submissions).

Every single line of code was written by me, 100% from scratch, between August 2023 and August 2024.
However, two years later I set up this website and used various AI tools to generate most of the walkthrough / explanations below.

See this dedicated page for more details.

Overview

The provided SQL statement extracts the names of customers who have never placed an order. It does this by selecting from the Customers table while excluding any customer identifiers that appear in the orders table. The query uses an alias to present the result column as Customers, which clarifies the output for downstream consumption.

Key Steps

The first step isolates the target column from the Customers table. The alias Customers is applied directly in the SELECT clause, ensuring the output column is clearly labeled.

SELECT name AS `Customers`

The second step defines the source table for the selection. By referencing Customers in the FROM clause, the query establishes the primary dataset from which records will be drawn.

FROM Customers

The third step introduces a filtering condition that removes any rows whose id appears in a subquery. The WHERE clause employs the NOT IN operator to achieve this exclusion.

WHERE id NOT IN (SELECT DISTINCT customerId
                 FROM orders)

The subquery itself selects distinct customerId values from the orders table. The DISTINCT keyword eliminates duplicate identifiers, ensuring the outer NOT IN comparison works against a minimal set of values.

SELECT DISTINCT customerId
FROM orders

Edge Cases Handled

The use of DISTINCT within the subquery naturally handles scenarios where a single customer has placed multiple orders. By collapsing duplicate customerId entries, the outer filter does not need to evaluate redundant values, which could otherwise lead to unnecessary comparisons.

When the orders table contains no rows, the subquery returns an empty result set. In this situation, the NOT IN condition evaluates to true for every row in Customers, resulting in the full list of customer names being returned. This behavior correctly reflects the absence of any ordering activity.

If the Customers table includes rows where the id column is NULL, those rows are automatically excluded by the NOT IN predicate because NULL does not compare equal to any value, including other NULLs. Consequently, the query safely omits ambiguous identifiers without raising errors.

The query also gracefully handles the case where the orders table contains NULL values in the customerId column. Because NULL values are ignored by the DISTINCT operation and do not affect the NOT IN comparison, they do not interfere with the identification of customers lacking orders.

Performance Optimizations Observed

The inclusion of DISTINCT in the subquery reduces the number of values that the outer NOT IN clause must compare against. By eliminating duplicate customerId entries early, the database engine can perform the exclusion check more efficiently, especially when the orders table contains many rows per customer.

The query’s structure allows the optimizer to potentially transform the NOT IN predicate into an anti-join operation. This transformation can leverage existing indexes on the id column of Customers and the customerId column of orders, enabling rapid lookups and minimizing full table scans.

Using an alias for the output column (AS \Customers``) does not affect execution but clarifies the result set, which can simplify downstream processing and reduce the need for additional column renaming steps in application code.

Overall, the query demonstrates a concise approach to identifying non-participating customers while employing mechanisms that support efficient execution on typical relational database platforms.

Imagine a big toy store that keeps a giant list of every child who has ever visited. There is also a special box that holds the names of every child who has already finished picking out a new toy.

The code is like a helper walking through the toy store with a notebook. It looks at the giant list of names and checks each one against the notes in the box. If the helper sees a name on the main list that is not found anywhere inside the box, those children get written down on a new piece of paper for a special invitation. The computer is simply sorting through the names to find out which friends have not started their shopping yet.

Several LLMs ranked my code quality on a scale from 1 (very poor) to 10 (outstanding):

not analyzed yet

I wrote a basic crawler to find other solutions to that problem.
Obviously, I did that after I solved the problem myself.

In general, AlgoMonster and WalkCCC solutions are high-quality and always worth a look.
I haven't checked most of the other solutions, but they might be helpful as well.

And of course, the LeetCode forum is always a good place (or even the best) to look for alternative approaches.

(click on a column header to sort the table)

	author
author	solutions
www	AlgoMonster		C++	Java	Python	TS
	WalkCCC	SQL
	leetcode.ca	SQL			Python
	LeetcodeHelp		C++	Java	Python
	Progiez		C++	Java	Python
github	everthis	SQL
	fsq	SQL
	keineahnung2345		C++
	Krazune	SQL
	suxrobGM	SQL
	trung-hn	SQL
AlgoMonster	C++, Python, Java, TypeScript
WalkCCC	SQL
leetcode.ca	Python, SQL
LeetcodeHelp	C++, Java, Python
Progiez	C++, Java, Python
everthis	SQL
fsq	SQL
keineahnung2345	C++
Krazune	SQL
suxrobGM	SQL
trung-hn	SQL

There are a few extra pages giving you some insights into my setup:

• Tech Stack - my development environment and tools
• AI - how AI helped me create this website (NOT solving the problems)
• Progress - progression over time
• Quality - code quality