SQL Sub Queries
A sub query in SQL, also known as an inner query or nested query, is a query within another query, that is often found embedded in the ‘where’ clause, although they can also be used within the ‘select’ and ‘from’ clauses. They allow a query to be based on the results of another query.
SELECT column_name1, column_name2, column_name3 FROM table_name WHERE column_name operator ( SELECT column_name FROM table_name WHERE condition);
It should be noted that the sub query can only contain one column of data, although this could be made up of more than one column concatenated, or joined, together. The sub query should also only return one row of data, unless the operator being used in the outer query is a multiple value operator, such as IN.
Continuing with the ‘person’ table, used in the previous examples, a sub query could be used to retrieve details of the person, or people, with a date of birth equal to the minimum date of birth.
SELECT id, firstname, lastname, title, dob FROM person WHERE dob = ( SELECT MIN(dob) FROM person);
Only one record from the original four is returned in this case, however, if two or more records had the same date of birth as the minimum, then they would all be returned.
id | firstname | lastname | title | dob |
2 | Simon | Smith | Mr | 1960-04-01 |
The above example incorporates one sub query, however, if necessary, more than one can be used as in the example below, where only those records with a date of birth equal to the minimum or maximum date of birth are returned.
SELECT id, firstname, lastname, title, dob FROM person WHERE dob = ( SELECT MIN(dob) FROM person) OR dob = ( SELECT MAX(dob) FROM person);
The data returned includes just two records from the original four, however, as with the previous example, if more than one record has a date of birth equal to the minimum or maximum, then they will all be returned.
id | firstname | lastname | title | dob |
2 | Simon | Smith | Mr | 1960-04-01 |
4 | Fiona | Jones | Miss | 1985-05-19 |