SQL Subqueries - Complete Guide
What is Subquery
A subquery (also called nested query or inner query) is a query written inside another query. The
inner query executes first and its result is used by the outer query.
Practice Questions:
1 Define a subquery in SQL.
2 What are other names for a subquery?
3 Which query executes first: inner or outer?
4 Can a subquery return multiple rows?
5 Where do we place subqueries in SQL?
Where to use Subquery (SELECT, FROM, WHERE, HAVING)
Subqueries can be placed in different parts of a SQL statement: - SELECT clause (as a column) -
FROM clause (as inline view) - WHERE clause (as condition) - HAVING clause (as filter on groups)
Practice Questions:
1 Give an example of subquery in SELECT clause.
2 How do we use a subquery in FROM clause?
3 Can a subquery be used in HAVING clause?
4 Which clause is most commonly used with subqueries?
5 Write a query using subquery in WHERE clause.
Types of Subqueries: Single-row, Multi-row, Multi-column
- Single-row: returns one row (used with =, >, <) - Multi-row: returns multiple rows (used with IN,
ANY, ALL) - Multi-column: returns multiple columns, compared as tuples
Practice Questions:
1 What is a single-row subquery?
2 Which operators are used with single-row subquery?
3 Give an example of multi-row subquery.
4 What is a multi-column subquery?
5 Can we compare multiple columns in subquery?
Operators: IN, ANY, ALL
- IN: matches any value in list - ANY: condition is true if it matches at least one value - ALL:
condition is true if it matches all values
Practice Questions:
1 What is the difference between IN and ANY?
2 Give an example query using ALL.
3 When to use ANY operator?
4 Which operator checks against all values of a subquery?
5 Write a query using IN with subquery.
EXISTS and NOT EXISTS
- EXISTS: returns true if subquery returns at least one row - NOT EXISTS: returns true if subquery
returns no rows
Practice Questions:
1 What does EXISTS operator check?
2 When does NOT EXISTS return true?
3 Give an example of EXISTS subquery.
4 Can EXISTS be used with correlated subquery?
5 What is difference between IN and EXISTS?
Scalar Subquery
A scalar subquery returns exactly one value. It can be used like a column in SELECT.
Practice Questions:
1 What is a scalar subquery?
2 Can a scalar subquery return multiple values?
3 Give an example of scalar subquery in SELECT clause.
4 Where is scalar subquery commonly used?
5 Why must scalar subquery return exactly one value?
Nested Subqueries
A subquery inside another subquery. Useful for multi-level filtering.
Practice Questions:
1 What is a nested subquery?
2 Give an example of nested subquery with 2 levels.
3 Can nested subqueries be used in WHERE clause?
4 Is there a limit to nesting levels?
5 Why do we use nested subqueries?
Inline Views (Subquery in FROM)
A subquery used in the FROM clause acts like a temporary table.
Practice Questions:
1 What is an inline view?
2 Why do we use subquery in FROM clause?
3 Give an example of inline view to get top 2 salaries.
4 Does inline view act like a temporary table?
5 Can we use alias with inline views?
Correlated Subqueries
A correlated subquery references columns from the outer query. It executes once for each row in
the outer query.
Practice Questions:
1 What is a correlated subquery?
2 How does correlated subquery differ from normal subquery?
3 Give an example of correlated subquery for max salary per dept.
4 Why are correlated subqueries slower?
5 When to use correlated subqueries?
Subqueries in DML (INSERT, UPDATE, DELETE)
Subqueries can also be used in DML statements: - INSERT INTO ... SELECT ... - UPDATE ...
WHERE col IN (subquery) - DELETE ... WHERE col IN (subquery)
Practice Questions:
1 Give an example of INSERT with subquery.
2 How to use subquery in UPDATE statement?
3 Write a DELETE query using subquery.
4 Can we use subqueries with DML in all databases?
5 Why are subqueries useful in DML operations?
Performance vs JOIN
Joins are usually faster than subqueries, especially correlated ones. However, subqueries can be
easier to write and understand in some scenarios.
Practice Questions:
1 Which is generally faster: JOIN or subquery?
2 Why are joins faster than correlated subqueries?
3 When should we prefer subquery over join?
4 Can a join always replace a subquery?
5 Give a case where subquery is easier than join.
