Sometimes it is necessary to do a SQL query within another one. This concept is the so-called subquery and this chapter aims to discuss on how and why to deal with it.
1. Independent subquery
Let’s assume we’d like to get data about all employees whose salary is higher or equal to the average salary of all employees.
We get the following table.
2. Correlated subquery
Let’s assume we’d like to get, for each department, the data about the employee having started to work the first in (most senior).
We get the following table.
Recap
Subqueries are used when a piece of data needs to be obtained first, on which the filtering is based.
In the first example above, the average salary of all employees needs first to be obtained. This is done by the subquery as follows :
A subquery is said to be correlated when the subquery relates to the main query. In the second example above, for each employee, the subquery gives the oldest start date among employees part of the current employee’s department. Therefore, the subquery depends on the current employee’s department. That dependence is stated as follows :