HardSQL challenge #200JOINFILTERANTI JOIN

Find Customers Who Never Placed an Order

Return the users who have no orders at all.

The challenge runs on the SQLabHub practice database (SQLite) and uses the users and orders tables. You can solve it in the browser, with nothing to install.

Solve This SQL Challenge

The task

Find users who never placed an order.

A correct answer returns 36 rows with 10 columns: id, first_name, last_name, email, phone, city, country, created_at, status, is_premium.

Tables used in this challenge

users 150 rows

id INTEGER (PK) · first_name TEXT · last_name TEXT · email TEXT · phone TEXT · city TEXT · country TEXT · created_at TEXT · status TEXT · is_premium INTEGER

First 3 rows of the users table
idfirst_namelast_nameemailphonecitycountrycreated_atstatusis_premium
1JenniferTorresjennifer.torres57@icloud.com+1-263-376-2371HoustonCanada2024-04-13active0
2DonnaAdamsdonna.adams52@gmail.com+1-753-780-6670Fort WorthCanada2020-09-15inactive0
3KennethYoungkenneth.young94@proton.me+1-440-743-9503AustinUSA2023-09-05active0

orders 200 rows

id INTEGER (PK) · user_id INTEGER · total REAL · discount REAL · item_count INTEGER · status TEXT · order_date TEXT · shipped_date TEXT · shipping_city TEXT · shipping_country TEXT

First 3 rows of the orders table
iduser_idtotaldiscountitem_countstatusorder_dateshipped_dateshipping_cityshipping_country
1432638.110.253cancelled2024-04-24NULLLos AngelesUSA
2829470.195delivered2024-03-152023-04-09ChicagoUSA
3136683.240.082delivered2023-02-152023-07-10Los AngelesUSA

The first rows of each table are shown. See how all the tables relate on the database schema page.

Expected output

Expected output of the challenge
idfirst_namelast_nameemailphonecitycountrycreated_atstatusis_premium
3KennethYoungkenneth.young94@proton.me+1-440-743-9503AustinUSA2023-09-05active0
9SandraJacksonsandra.jackson9@yahoo.com+1-206-632-4419PhiladelphiaAustralia2024-08-07active1
12RichardThomasrichard.thomas32@hotmail.com+1-946-863-1084CharlotteCanada2023-04-18active1
14JeffreyLopezjeffrey.lopez33@icloud.com+1-604-369-1028HoustonUSA2021-08-02inactive0
17StephanieMitchellstephanie.mitchell84@icloud.com+1-257-668-4753San AntonioUSA2024-10-15active1

First 5 of 36 rows. This is the output of the model solution on the practice database. The task does not fix a sort order, so your rows may come back in a different order.

What you practise

  • Filtering rows with a WHERE condition
  • Testing for missing values with IS NULL and IS NOT NULL
  • Keeping every row of the left table with LEFT JOIN, matched or not
  • Finding rows with no match using LEFT JOIN … IS NULL

Hint and approach

Try the challenge first. Open these only when you need them.

Show the hint

LEFT JOIN

Show the step-by-step approach

How the model solution is built, in the order the database evaluates it:

  1. Read from the users table.
  2. LEFT JOIN orders on users.id = orders.user_id. Rows from the left side that have no match are kept, with NULL in the joined columns.
  3. Keep only the rows where the joined orders columns are NULL. Those are the rows that found no match in the join.
  4. Return every column of users.

Model solution

Checking your session…

What to watch for

The LEFT JOIN keeps every user and fills the order columns with NULL where nothing matched; filtering on o.id IS NULL keeps only those. NOT EXISTS expresses the same thing. NOT IN is the risky variant: one NULL user_id in orders would make it return no rows.

  • Anti join. Test a column of the joined table that can never be NULL in a real row, normally its primary key. Testing a nullable column also returns matched rows where that column happens to be empty.
  • LEFT JOIN. A WHERE condition on the right-hand table removes the unmatched rows and turns the LEFT JOIN back into an inner join. Conditions on that table go in the ON clause.
  • IS NULL. Comparing with = NULL or <> NULL never matches any row. NULL has to be tested with IS NULL or IS NOT NULL.
Solve This SQL Challenge

Same technique, other tables

More practice on this pattern with different columns:

Keep learning