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.

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.