SQL Subqueries

Medium

A subquery is a query nested inside another query, used to compute intermediate results for the outer query.

Why It Matters

Subqueries are essential for multi-step data retrieval. They appear frequently in placement SQL rounds, especially for 'find the max/min within a group' patterns.

How It Works

Types: Scalar subquery (returns one value), Row subquery (one row), Table subquery (multiple rows). Placement: in WHERE (most common), in FROM (derived table), in SELECT (correlated scalar). Correlated subquery: references the outer query — runs once per outer row (can be slow). Non-correlated: independent of outer query — runs once, result reused. Common patterns: WHERE col IN (SELECT ...), WHERE col = (SELECT MAX(...)), WHERE EXISTS (SELECT 1 FROM ... WHERE ...). IN vs EXISTS: EXISTS is usually faster for large subquery results; IN is clearer for small lists.

Example

-- Find employees earning more than their department average SELECT name, salary, dept_id FROM employees e WHERE salary > ( SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id -- correlated );

Common Mistakes

Using = instead of IN when the subquery can return multiple rows. Forgetting that NOT IN fails silently when the subquery contains NULL values (always returns empty). Using correlated subqueries where a JOIN would be much more efficient.

Test Your Understanding

Easy Q1

What's the difference between a correlated and non-correlated subquery?

Easy Q2

What does EXISTS check?

Hard Q3

Table: orders(id, customer_id). Some customer_id values are NULL. Why might this return zero rows even though some customers have no orders? SELECT * FROM customers WHERE id NOT IN (SELECT customer_id FROM orders);

Ready to Master SQL Subqueries?

Practice with adaptive questions. Track your misconceptions. Build real mastery.

Free account. No credit card required.

Related Concepts

SQL GROUP BY and HAVING

GROUP BY collapses rows sharing common values into summary rows; HAVING filters these groups after aggregation.

SQL JOINs

SQL JOINs combine rows from two or more tables based on a related column.

← All Sql Concepts Practice All Topics →