SQLMaxx
Sign in Start free
N018-E3 Tier 2 · Core SQL · easy ecommerce · Brightlane

Return the category name and product name for every category. Empty categories should still appear, with the product column missing

Part of LEFT JOIN and RIGHT JOIN in SQL

The problem

Brightlane's catalogue team wants to see every category alongside any products assigned to it — including categories that currently have no products.

Write a query to return the category name and product name for every category. Empty categories should still appear, with the product column missing.

Assumptions:

  • The categories table contains every defined category.
  • The products table contains every product in the catalogue; category_id on each product points to a category.
  • The result is anchored on the categories side — every category appears, including those with no matching products.

Output:

  • One row per category-product pair, plus one row per empty category, with columns category_name and product_name.
Schema · ecommerce 5 tables
categories
id integer
name text
parent_id? integer
products
id integer
name text
category_id integer
price numeric
stock_qty integer
attributes? jsonb
order_items
id integer
order_id integer
product_id integer
quantity integer
unit_price numeric
customers
id integer
name text
email text
city? text
country text
created_at timestamptz
is_active boolean
orders
id integer
customer_id integer
ordered_at timestamptz
status text
total_amount numeric
Check answerShift Ctrl ↵

Run previews · Check grades

Write a query, then run it to see results here.
Worked solution Try it yourself first
Solution query
SELECT
  cat.name AS category_name,
  p.name AS product_name
FROM
  products p
  RIGHT JOIN categories cat ON p.category_id = cat.id

The shape

categories is the table that has to be enumerated in full — including the two empty ones, Clothing and Home & Garden. RIGHT JOIN preserves the right-side table, so writing products RIGHT JOIN categories keeps every category and fills product_name with NULL for the categories that have no products.

Clause by clause

  • SELECT cat.name AS category_name, p.name AS product_name returns the category's name from the preserved right side and the product's name from the left side. For empty categories, p.name is NULL — that's the unmatched signal.
  • FROM products p is the left table. With a RIGHT JOIN, the left side is the one that may lose rows: a product whose category_id doesn't match any category in categories would drop out, but in this schema every product has a real category, so nothing is dropped on the left.
  • RIGHT JOIN categories cat ON p.category_id = cat.id flips the preservation direction. Every category appears at least once, regardless of whether any product points to it. The two empty categories appear as single rows with NULL in product_name.

Why this and not categories LEFT JOIN products

The two queries are equivalent. Swap the table order and change RIGHT JOIN to LEFT JOIN and you get the same result rows:

SELECT cat.name AS category_name, p.name AS product_name
FROM categories cat
LEFT JOIN products p ON cat.id = p.category_id

Most analysts standardise on LEFT JOIN and arrange the tables so the one to preserve comes first. It reads more naturally — the anchor sits at the start of the FROM clause, where the eye lands. RIGHT JOIN exists in the SQL standard and shows up in legacy code, but it offers no expressive power that LEFT JOIN doesn't already have.

You practiced RIGHT JOIN and saw that it's just LEFT JOIN with the table order reversed. The recurring rule: any A LEFT JOIN B can be rewritten as B RIGHT JOIN A with identical results — most analysts standardise on LEFT JOIN because it reads more naturally.

How you actually get good at SQL

Reading explains SQL. Writing it, over and over with instant feedback, is what makes you fluent.

That's the whole SQLMaxx loop: 600+ real problems, instant AI feedback, mastery you can actually see, and spaced review that won't let you forget.

A stack of SQL practice problem cards, the top card showing an employees table.
615 problems · 66 concepts

Real problems. Not toy examples.

615 hand-built problems spanning all 66 concepts, from basic SELECTs to window functions, built on real schemas and real business questions, the kind you'll actually get asked on the job. Enough reps to make SQL automatic.

A retro computer showing a SQL query marked correct with a green checkmark.
Instant AI feedback

Write a query. Know if it's right in one second.

No copying an answer and hoping it clicked. The AI grader checks your real query against real data, catches exactly what's wrong, and explains the fix in plain English, like a senior analyst reading over your shoulder on every problem.

A circular mastery progress dial filling from blue to green, the SQLMaxx diamond at its center.
Mastery tracking

Stop guessing whether you actually know it.

SQLMaxx tracks every concept and shows you what you've mastered and what's still shaky. Your skills fill in one concept at a time, so 'I think I get joins' becomes something you can prove.

A SQL query editor circled by a blue return arrow with a clock, scheduled to come back for review.
Spaced review

Learn it once. Keep it for good.

Most of what you learn this week fades by next week. So when a concept comes due for review, SQLMaxx hands you a fresh problem to solve from a blank editor, not a flashcard to re-read. A research-backed spaced-repetition algorithm (FSRS) times each return for right before you'd forget, so your SQL is still there months later, when the interview or the job actually needs it.

Practice, feedback, mastery, review. That's the loop that turns reading into real skill.

Start free

No account, no credit card. Start solving in under a minute.