What are Subqueries
A subquery is a SELECT query nested inside another SQL statement. The inner query runs first and its result is used by the outer query. Subqueries enable dynamic filtering, computed values, and complex conditions without temporary tables.
Subquery Basics
- A subquery is placed inside parentheses: (SELECT ...)
- Can appear in SELECT, FROM, WHERE, and HAVING clauses
- The inner query executes first; result feeds the outer query
- Types: scalar (returns 1 value), row (1 row), table (multiple rows), correlated (references outer query)
- Subqueries vs JOINs: subqueries are often more readable, JOINs are often faster
Subquery Anatomy
-- Basic structure
SELECT columns
FROM table
WHERE col = (SELECT value FROM other_table WHERE condition);
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ inner query
-- The inner query runs first:
-- Step 1: SELECT AVG(total_amount) FROM orders → 563.32
-- Step 2: SELECT * FROM orders WHERE total_amount > 563.32
-- Simple scalar subquery example
SELECT name, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders);
-- Subquery in SELECT clause
SELECT
name,
price,
(SELECT AVG(price) FROM products) AS avg_price,
price - (SELECT AVG(price) FROM products) AS diff_from_avg
FROM products;Quick Quiz
Tip
Tip
Practice What are Subqueries in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Inner query runs first, results used by outer query. CTEs are often more readable.
Common Mistake
Warning
A common mistake with What are Subqueries is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of What are Subqueries from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A subquery is a SELECT query nested inside another SQL statement.
- A subquery is placed inside parentheses: (SELECT ...)
- Can appear in SELECT, FROM, WHERE, and HAVING clauses
- The inner query executes first; result feeds the outer query