What is a Column Subquery?
A column subquery returns multiple values in one column (a list).
Simple rule: Inner query = list, Outer query uses IN or NOT IN
How Column Subquery Works
Basic Syntax
SELECT columns
FROM table
WHERE column IN (SELECT column FROM other_table);Example 1: Find Students with Orders
SELECT name
FROM students
WHERE id IN (SELECT student_id FROM orders);Step 1: Inner query → list of student_ids (1, 2) Step 2: Outer query → WHERE id IN (1, 2)
Example 2: Find Students WITHOUT Orders
SELECT name
FROM students
WHERE id NOT IN (SELECT student_id FROM orders);Uses NOT IN to find students missing from orders.
Column Subquery Rules
- Returns a list - multiple rows, one column
- Use with IN - check if value is in list
- Use with NOT IN - check if value is NOT in list
Try It Below
Practice finding students with/without orders!
What Comes Next
Next: Row Subqueries - return one row with multiple columns.