Correlated and nested subqueries in database management provide powerful tools for efficient data retrieval. Correlated subqueries use values from external queries to retrieve complex data, while nested subqueries insert one query into another, operating independently. In this tutorial, we will examine their syntax, execution, and practical application by example on various types.
Correlated Subquery: An Overview
A Correlated subquery defines the correlation between the inner query and the outer query. It is a subquery that uses values from the outer query to complete its task. This query cannot be executed independently because it is dependent on the outer query(main query). You can think of it as a query within a query, where the inner query depends on the data from the execution of the outer query.
A correlated subquery is executed individually for every row which is processed by the parent statement in the form of a SELECT, UPDATE, OR DELETE statement.
Syntax
SELECT column1, column2, ...
FROM table1
WHERE column1 OPERATOR (
SELECT column1
FROM table2
WHERE table2.correlated_column = table1.correlated_column
);Code language: SQL (Structured Query Language) (sql)Example
Let’s say we want to fetch a list of the employer whose product sales performance is good within their departments from the SALE table.
To find out this, we need a list of employees whose sales are more than the department’s average from the SALE table.
SELECT e.NAMES, e.SALES_DONE
FROM SALE e
WHERE e. SALES_DONE > (SELECT AVG(e1.SALES_DONE)
FROM SALE e1
WHERE e1.DEPARTMENT_ID = e.DEPARTMENT_ID)
ORDER BY e.SALES_DONE DESC;Code language: SQL (Structured Query Language) (sql)Output
Nested Subquery: An Overview
Nested Subquery in SQL are those queries that are embedded within another query in the form of an SQL statement. Nested subqueries are self-contained queries and can be executed independently within another query. This query allows the embedding of one query within another.
Syntax
SELECT column1, column2, ...
FROM table1
WHERE column1 OPERATOR (SELECT column1
FROM table2
WHERE condition);Code language: SQL (Structured Query Language) (sql)Example
Let’s say we want to fetch a list of employees whose sales performance exceeds the average sales in their respective departments from the SALE table.
SELECT e.NAMES, e.SALES_DONE
FROM SALE e
WHERE e.SALES_DONE > (
SELECT AVG(e1.SALES_DONE)
FROM SALE e1
WHERE e1.DEPARTMENT_ID = e.DEPARTMENT_ID
)
ORDER BY e.SALES_DONE DESC;Code language: SQL (Structured Query Language) (sql)Output
Correlated Subqueries Vs Nested Subqueries
The correlated subqueries execute the outer query first and after that, it uses the values obtained from it to execute the inner query.
Whereas Nested subqueries are the opposite of that, they first execute the inner query and then the values obtained from it are used to execute the outer query.
Example
For Correlated Subquery:
Let’s say we want to find all the employees having average salary in the department from the SALE table.
SELECT e.NAMES, e.SALES_DONE
FROM SALE e
WHERE e. SALES_DONE = (
SELECT MAX(e1.SALES_DONE)
FROM SALE e1
WHERE e1.DEPARTMENT_ID = e.DEPARTMENT_ID
);Code language: SQL (Structured Query Language) (sql)
So, in the above query, the outer query selects a name, and sales amounts whereas the inner query calculates the maximum sales for each department.
For Nested Subquery
Let’s say we want to find all the employees having salaries less than the average salary in the department from the SALE table.
SELECT NAMES, SALES_DONE
FROM SALE
WHERE SALES_DONE < (
SELECT AVG(SALES_DONE)
FROM SALE AS sub
WHERE sub.DEPARTMENT_ID = SALE.DEPARTMENT_ID
)
ORDER BY SALES_DONE DESC;Code language: SQL (Structured Query Language) (sql)
So, in the above query, the inner query calculates the average sales amount for each department whereas the outer query selects the name and sales done by employees whose average salary is lower than the average sales amount in the department.
Comparison and Use Cases
Correlated Subqueries
- This Subquery is ideal for dynamic data retrieval or filtering based on the outer query’s contact.
- This Subquery is used to calculate row-specific aggregates or filters based on related data.
Nested Subqueries
- This Subquery is ideal for standalone operations or when independent data retrieval or filtering is required.
- This Subquery is used for filtering based on general aggregates or subsetting data prior to joining.
Conclusion
In this tutorial, we have learned about the Correlated Subquery and Nested Subquery using multiple examples, also we saw how they both differ from each other. To put it simply, use correlated subqueries for comparisons that rely on the unique data in each row and nested subqueries for efficiency and pre-calculated results. We hope you enjoyed this tutorial.