DataTerminology.comNEW
Also known as: Nested Query, Inner Query
A query embedded within another SQL statement to compute intermediate results used by the outer query.
DESCRIPTION
Subqueries allow SQL statements to reference results of another query inline. They can be scalar, row, or table subqueries and may be correlated with the outer query. Typical uses include filtering with EXISTS/IN, computing derived metrics, and simplifying complex predicates. While powerful, excessive nesting can harm readability and performance. Alternatives include joins and CTEs, which may be clearer and faster.
ALTERNATIVE DEFINITIONS
Oracle
A subquery is a query within a query. You can use a subquery to return data that will be used in the main query as a condition to further restrict the data to be retrieved.
Clarifies use of subquery for filtering in the main query.
Microsoft Learn
A subquery is a query nested inside another query. Subqueries can be used in SELECT, INSERT, UPDATE, or DELETE statements.
Highlights presence across multiple SQL statements, not only SELECT.
RELATED TERMS
SQL Join SELECT WHERE Clause Aggregation Correlated Subquery View Window Function Index
COMPETING TERMS
KNOWLEDGE AREAS
Programming Languages Data Storage & Operations Data Warehousing & BI
TARGET AUDIENCE
Data Engineer Analytics Engineer Data Analyst BI Specialist Software Developer
EXTERNAL SOURCES
- Wikipedia - Select (SQL)#Subqueries
- PostgreSQL - Subquery functions and usage
- MySQL - MySQL subqueries documentation
- SQLite - SQLite subqueries reference
TOP QUESTIONS
What is a Subquery in SQL?
A Subquery is a query nested inside another SQL statement, returning intermediate results for filtering or computation.
Category: Definition
When should I use a Subquery versus a Join?
Use joins for combining related tables; subqueries can simplify conditional filters or scalar calculations. Choose based on clarity and performance.
Category: Comparison
What is a correlated Subquery?
It references columns from the outer query, executing per row. Correlated subqueries can be costly if not optimized.
Category: Overview
Are Subqueries slow?
Not inherently; performance depends on indices, optimizer capabilities, and query structure. Excessive nesting can degrade performance.
Category: Performance
What are common Subquery mistakes?
Using IN with large sets, missing indexes, and writing correlated subqueries when joins or CTEs would be more efficient.
Category: Mistakes
Can Subqueries appear in INSERT or UPDATE?
Yes, many databases support subqueries across SELECT, INSERT, UPDATE, and DELETE statements.
Category: Requirements
How do CTEs compare with Subqueries?
CTEs improve readability and can be reused within a query; subqueries are inline. Optimizers may treat them similarly.
Category: Comparison
How to debug a complex Subquery?
Isolate inner queries, inspect execution plans, add indexes, and simplify predicates to validate logic and performance.
Category: Management
What’s a hot topic around Subqueries?
Optimizers increasingly flatten subqueries, pushing predicates and using semi-joins for improved performance.
Category: Topic