[Solved] You want to list all products with their category name, but some products have no category assigned (category_id is NULL).... | SQL

SQL - Subqueries

You want to list all products with their category name, but some products have no category assigned (category_id is NULL). Which query correctly uses a scalar subquery in SELECT to show category names or 'Uncategorized' if none?

Options:
A) SELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;
B) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;
C) SELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;
D) SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

ASELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products;

BSELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' AS category FROM products;

CSELECT product_name, (SELECT category_name FROM categories WHERE id = products.category_id) AS category FROM products WHERE category_id IS NOT NULL;

DSELECT product_name, IFNULL((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products WHERE category_id IS NOT NULL;

Step-by-Step Solution

Solution:

Step 1: Handle NULL category_id with scalar subquery
Use COALESCE to replace NULL result from subquery with 'Uncategorized'.
Step 2: Check each option's correctness
The query with COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; correctly uses COALESCE and includes all products. The query with (SELECT category_name FROM categories WHERE id = products.category_id) OR 'Uncategorized' uses invalid OR syntax. The queries with WHERE category_id IS NOT NULL exclude products with NULL category_id.
Final Answer:
SELECT product_name, COALESCE((SELECT category_name FROM categories WHERE id = products.category_id), 'Uncategorized') AS category FROM products; -> Option A
Quick Check:
Use COALESCE with scalar subquery for NULL handling [OK]

Quick Trick: Use COALESCE to handle NULL from scalar subquery [OK]

Common Mistakes:

MISTAKES

Using OR instead of COALESCE or IFNULL
Filtering out NULL category_id rows
Not handling NULL results from subquery

Master "Subqueries" in SQL

9 interactive learning modes - each teaches the same concept differently

Learn Why Deep Visual Try Challenge Project Recall Time

More SQL Quizzes

Which query correctly uses a scalar subquery in SELECT to show category names or 'Uncategorized' if none?

Step 1: Handle NULL category_id with scalar subquery

Step 2: Check each option's correctness

Final Answer:

Quick Check:

Want More Practice?