MediumSQL challenge #148JOIN

Users Without Reviews

Find users who never wrote a review.

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

Solve This SQL Challenge

The task

Find users who never wrote a review.

A correct answer returns 46 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
id	first_name	last_name	email	phone	city	country	created_at	status
1	Jennifer	Torres	jennifer.torres57@icloud.com	+1-263-376-2371	Houston	Canada	2024-04-13	active
2	Donna	Adams	donna.adams52@gmail.com	+1-753-780-6670	Fort Worth	Canada	2020-09-15	inactive
3	Kenneth	Young	kenneth.young94@proton.me	+1-440-743-9503	Austin	USA	2023-09-05	active

`reviews` 180 rows

id INTEGER (PK) · product_id INTEGER · user_id INTEGER · rating INTEGER · comment TEXT · reviewed_at TEXT · helpful_votes INTEGER

First 3 rows of the reviews table
id	product_id	user_id	rating	comment	reviewed_at	helpful_votes
1	23	45	5	Works perfectly. Very happy with this purchase.	2022-09-02	40
2	101	68	5	Excellent build quality. Will buy again!	2023-01-05	96
3	40	39	1	Would give 6 stars if I could!	2023-05-15	17

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
id	first_name	last_name	email	phone	city	country	created_at	status	is_premium
1	Jennifer	Torres	jennifer.torres57@icloud.com	+1-263-376-2371	Houston	Canada	2024-04-13	active	0
2	Donna	Adams	donna.adams52@gmail.com	+1-753-780-6670	Fort Worth	Canada	2020-09-15	inactive	0
4	Michael	Carter	michael.carter46@yahoo.com	+1-967-275-1964	San Antonio	France	2024-12-19	active	1
9	Sandra	Jackson	sandra.jackson9@yahoo.com	+1-206-632-4419	Philadelphia	Australia	2024-08-07	active	1
12	Richard	Thomas	richard.thomas32@hotmail.com	+1-946-863-1084	Charlotte	Canada	2023-04-18	active	1

First 5 of 46 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 reviews

Show the step-by-step approach

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

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

Model solution

Checking your session…

What to watch for

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.