Correlated Subqueries

This post will review a correlated subquery in SQL programming. To understand a correlated subquery, lets first discuss a non-correlated subquery. A non-correlated subquery is a sub-select where the inner select makes no reference to the outer select that contains it. Here is an example:

SELECT column1, column2...
FROM table1
WHERE column1 NOT IN
(SELECT column1
FROM table2);

In this query, the inner select can be run first, and the result set of the inner select used in the outer select. A correlated subquery works fundementally different as the inner select refers to values produced by the outer select. The syntax is shown below:

SELECT Out.column1, Out.column2,...
FROM table1 "Out"
WHERE Out.column2 operator (SELECT In.column2...
FROM table 2 "In"
WHERE In.column1=Out.column1);

The correlated subquery references a column of the main query to establish a link to the main query. This means that for every row of the outer query, the sub(inner) query is evaluated. Note the alias assigned to the tables in the outer and inner queries and the use of them in the WHERE clause in the inner query.

Also note, a correlated subquery could be performed where the outer and inner tables are the same. A scenario would be where you want to select all employees whose salary exceeds the average salary of all employees. Furthermore, a correlated subquery can be used as a column expression as a filter expression in the WHERE clause or as a data source expression in the FROM clause.

However, correlated subqueries are far less efficient than non-correlated subqueries because the inner query must be invoked for each row found by the outer query. With a non-correlated subquery, the inner query is run only once.