Nested SQL queries

A subquery is a select query that is used within a SELECT, INSERT, UPDATE, or DELETE statement, or within another subquery. The subquery can be used wherever expressions are allowed.

Example of a subquery structure

SELECT table_fields 
FROM table_list 
WHERE specific_field IN (
    SELECT table_field FROM table
)

Here, SELECT table_fields FROM table_list WHERE specific_field IN (...) - external request, and SELECT table_field FROM table - nested (internal) request.

Each subquery, in turn, can contain one or more subqueries. The number of nested requests in instructions is not limited.

The subquery can contain all the standard statements allowed for use in a regular SQL query: DISTINCT, GROUP BY, LIMIT, ORDER BY, table joins, queries, etc.

A subquery can return a scalar (one value), one row, one column, or a table (one or more rows from one or more columns). These are called scalar, columnar, row, and table subqueries.

Subquery as scalar operand

Scalar subquery is a query that returns a single scalar value (string, number, etc.).

The following simple query shows the output of a single value (company name). It doesn't make much sense as it is, but your queries can be much more complex.

SELECT (SELECT name FROM company LIMIT 1);

In the same way, you can use scalar subqueries to filter rows using WHERE with comparison operators.

SELECT * 
FROM FamilyMembers 
WHERE birthday = (SELECT MAX(birthday) FROM FamilyMembers);

With this request, it is possible to get the youngest member of the family. It uses a subquery to get the maximum date of birth, which is then used to filter the rows.

Subqueries с ANY, IN, ALL

ANY is a keyword that must follow the comparison operator (>, <, <>, =, etc.), returning TRUE if at least one of the values in the subquery column satisfies the indicated condition.

SELECT table_1_fields 
FROM table_1 
WHERE table_1_field <= ANY (SELECT table_2_field FROM table_2);

ALL is a keyword that must follow the comparison operation, returning TRUE if all the values in the subquery column satisfy the specified condition.

SELECT table_1_fields 
FROM table_1 
WHERE table_1_field > ALL (SELECT table_2_field FROM table_2);

IN is a keyword that is an alias for the keyword ANY with the comparison operator = (equivalence), or <> ALL for NOT IN. For example, the following queries are equivalent:

...
WHERE table_1_field = ANY (SELECT table_2_field FROM table_2);

...
WHERE table_1_field IN (SELECT table_2_field FROM table_2);

String subqueries

A string subquery is a subquery that returns a single row with more than one column. For example, the following query receives a single row in a subquery, and then, in order, pairwise compares the obtained values with the values in the outer query.

SELECT table_1_fields 
FROM table_1
WHERE (first_field_of_table_1, second_field_of_table_1) = 
    (
        SELECT first_field_of_table_2, second_field_of_table_2 
        FROM table_2 
        WHERE id = 10
    );

This construction is convenient to use for replacing logical operators. So, the following two queries are completely equivalent:

SELECT table_1_fields FROM table_1 WHERE (first_field_of_table_1, second_field_of_table_1) = (1, 1);
SELECT table_1_fields FROM table_1 WHERE first_field_of_table_1 = 1 AND second_field_of_table_1 = 1;

Related subqueries

A related subquery is a subquery that contains a reference to a table that was declared in the outer query. Here the subquery refers to the external table "table_1":

SELECT table_1_fields FROM table_1
WHERE table_1_field IN 
    (
        SELECT table_2_field FROM table_2
        WHERE table_2.table_2_field = table_1.table_1_field
    );

Subqueries as derived tables

A derived table is an expression that generates a temporary table in a FROM clause that works the same way as regular tables that you specify, separated by commas. This is the general syntax for a query using derived tables:

SELECT table_1_fields FROM (subquery) [AS] derived_table_alias

Note that a derived table always must be aliased to be able to refer to it in other parts of the query.

Processing nested requests

Nested subqueries are processed bottom-up. That is, the lowest level subquery is processed first. Further, the values obtained from the result of its execution are transmitted and used when implementing a higher-level subquery, etc.

Tasks for self-testing