What is it?
A subquery is a SELECT statement nested inside another SQL statement. The inner query runs first and its result is used by the outer query.
Why does it matter?
Some logic is naturally expressed as two steps -- first find the average, then find rows above average. Subqueries make this natural and readable without intermediate tables or multiple round-trips.
Learn scalar subqueries, IN, NOT IN, EXISTS, correlated subqueries, and derived tables.
Real-World Use Cases
- 🔍 Above-average products - WHERE price > (SELECT AVG(price) FROM products) -- no need to fetch the average in PHP first.
- 📋 Active customer list - WHERE user_id IN (SELECT user_id FROM orders WHERE status='paid') -- users who have a paid order.
- 🏆 Top spending category - Derived table: group orders by category, sum totals, then select the top result -- all in one query.
- 🔔 Unread notifications - WHERE id NOT IN (SELECT notification_id FROM read_notifications WHERE user_id=?) -- unread items.
Scalar Subquery -- Single Value
-- Find products priced above the average
SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;IN and NOT IN Subqueries
-- Users who placed at least one paid order
SELECT name, email FROM users
WHERE id IN (
SELECT DISTINCT user_id FROM orders WHERE status = 'paid'
);
-- Users who have NEVER ordered (guard against NULL in NOT IN!)
SELECT name FROM users
WHERE id NOT IN (
SELECT DISTINCT user_id FROM orders WHERE user_id IS NOT NULL
);EXISTS and Derived Table
-- EXISTS stops at first match -- faster for large tables
SELECT name FROM users u
WHERE EXISTS (
SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.total > 1000
);
-- Derived table (subquery in FROM clause)
SELECT u.name, s.total_spent
FROM users u
JOIN (
SELECT user_id, SUM(total) AS total_spent
FROM orders GROUP BY user_id HAVING total_spent > 5000
) AS s ON s.user_id = u.id
ORDER BY s.total_spent DESC;Q: Should I use a subquery or a JOIN?
JOINs are usually faster because the query optimiser handles them better. For NOT IN, prefer NOT EXISTS -- it handles NULLs correctly and is faster on large datasets.
Comments (0)
No comments yet. Be the first!
Leave a Comment