Subqueries
35 minA subquery (or inner query) is a query nested within another SQL query, typically embedded in the WHERE clause, FROM clause, or SELECT clause. Subqueries enable you to break complex problems into smaller, manageable parts. They can return single values, multiple values, or entire tables. Understanding subqueries is essential for writing sophisticated SQL queries. Subqueries are executed first, and their results are used by the outer query.
Subqueries can be used with SELECT, INSERT, UPDATE, and DELETE statements along with operators like `=`, `<`, `>`, `>=`, `<=`, `IN`, `BETWEEN`, `EXISTS`, etc. Subqueries in WHERE clauses filter results based on subquery results. Subqueries in SELECT clauses return calculated values. Subqueries in FROM clauses create derived tables. Understanding subquery placement enables you to use them effectively.
Scalar subqueries return a single value and can be used with comparison operators. Scalar subqueries must return exactly one row and one column. If they return zero rows, the result is NULL. If they return multiple rows, an error occurs. Understanding scalar subqueries enables you to use subqueries with comparison operators.
Subqueries returning multiple values are used with operators like IN, NOT IN, ANY, ALL, or EXISTS. IN checks if a value exists in the subquery results. EXISTS returns true if the subquery returns any rows. ANY and ALL compare values to all subquery results. Understanding these operators enables you to work with multi-row subqueries.
Correlated subqueries reference columns from the outer query, creating a relationship between inner and outer queries. Correlated subqueries execute once for each row processed by the outer query, making them potentially slower than non-correlated subqueries. However, they're powerful for row-by-row comparisons. Understanding correlated subqueries enables you to write queries that compare rows across tables.
Best practices include using JOINs when possible (often more efficient than subqueries), using EXISTS instead of IN when checking for existence, avoiding correlated subqueries when non-correlated alternatives exist, and testing subqueries independently before embedding them. Understanding when to use subqueries vs JOINs helps you write efficient queries.
Key Concepts
- Subqueries are queries nested within other queries.
- Subqueries can return single values, multiple values, or tables.
- Scalar subqueries return single values for comparison.
- Correlated subqueries reference outer query columns.
- Subqueries can be used with SELECT, INSERT, UPDATE, DELETE.
Learning Objectives
Master
- Writing subqueries in WHERE clauses
- Using scalar subqueries for single-value comparisons
- Using subqueries with IN, EXISTS, ANY, ALL
- Understanding correlated vs non-correlated subqueries
Develop
- Complex query design thinking
- Understanding when to use subqueries vs JOINs
- Writing efficient, maintainable subqueries
Tips
- Test subqueries independently before embedding them in outer queries.
- Use EXISTS instead of IN when checking for existence (often more efficient).
- Consider using JOINs instead of subqueries when possible.
- Understand that correlated subqueries execute once per outer row.
Common Pitfalls
- Writing subqueries that return multiple rows when single values are expected.
- Using correlated subqueries unnecessarily, causing performance issues.
- Not understanding subquery execution order, causing confusion.
- Using subqueries when JOINs would be more efficient.
Summary
- Subqueries are nested queries that enable complex data retrieval.
- Scalar subqueries return single values for comparison.
- Correlated subqueries reference outer query columns.
- Understanding subqueries enables sophisticated query writing.
- Subqueries are powerful but should be used judiciously.
Exercise
Write a query to find all customers who have placed an order.
SELECT CustomerName FROM Customers
WHERE CustomerID IN (SELECT CustomerID FROM Orders);