SQL Subqueries
intermediateadvanced
2 min read
What are Subqueries?
A subquery is a query nested inside another query. It can appear in the SELECT, FROM, or WHERE clause. The inner query executes first, and its result is used by the outer query.
Syntax
-- In WHERE
SELECT * FROM table1
WHERE column IN (SELECT column FROM table2);
-- In FROM (derived table)
SELECT * FROM (SELECT column, COUNT(*) AS cnt FROM table GROUP BY column) sub;
-- In SELECT (scalar subquery)
SELECT name, (SELECT COUNT(*) FROM orders WHERE orders.student_id = students.id) AS order_count
FROM students;When to Use
- Filtering by a dynamically computed list
- Computing per-row aggregates without GROUP BY
- Breaking complex queries into logical steps
Key Points
- Scalar Subquery — Returns a single value. Can be used in SELECT or WHERE with
=. - Correlated Subquery — References columns from the outer query. Executes once per outer row.
- Derived Table — A subquery in FROM must have an alias.
- Performance — Correlated subqueries can be slow. Consider JOINs or CTEs as alternatives.
- EXISTS vs IN —
EXISTSis often faster thanINfor correlated subqueries.
Guided Practice
Solve the challenge below. Use hints when stuck and check your answer for instant feedback.
Practice challengeGuided learning mode
SQL Subqueries Challenge
Write a query that find students who have at least one enrollment.
Expected result
Only students who appear in the enrollments table.
Hidden checks
- Returned rows and values
- Output columns and result shape
- Final database state after the query runs
Lesson guidance
What are Subqueries?
Initializing database...Each run starts from fresh sample data.
More Examples
Scalar subquery in SELECT
Show each student with their enrollment count.
Initializing database...Each run starts from fresh sample data.
Derived table
Find grades with more than 5 students using a subquery.
Initializing database...Each run starts from fresh sample data.
Frequently Asked Questions
What is a correlated subquery?
A correlated subquery references a column from the outer query. It is re-executed for each row of the outer query.
Should I use a subquery or a JOIN?
Both can achieve the same result. JOINs are generally more readable and optimizable. Subqueries are useful when the logic is clearer as a nested operation.