Subquery in SQL

A SUBQUERY is a query within another SELECT query, SUBQUERY or other SQL statements such as INSERT, UPDATE o DELETE. In this section we will focus on the SUBQUERY within the SELECT statement and we will learn how to apply them with examples from our Customers and Orders tables.

A SELECT may internally contain another SELECT in one of its clauses. This inner SELECT enclosed in parentheses is the so-called SUBQUERY.

SUBQUERY help in data filtering and attend to the following characteristics and recommendations:

• They must always be in parentheses.

• They generally cannot use ORDER BY unless they are preceded by the TOP clause.

• They are usually preceded by ALL, IN, NOT IN, EXISTS, NOT EXISTS clauses.

• It is not advisable to use calculated fields as they can slow down the query.

We will see examples below according to the location of the expression in a SELECT statement:

SELECT

A SUBQUERY can be placed in the column list of the SELECT expression to return the result as one more field of the result set:

SELECT [name],
       [lastName],
       (SELECT SUM (totalPrice)
       FROM Orders
       WHERE customerId = 4) AS totalCustomerSpending
FROM Customers
WHERE customerId = 4

As we can see the execution returns the name and last name from the Customers table and the result of all of the totalCustomerSpending sum from the Orders table.

FROM

The FROM clause admits instead of a table definition, a SUBQUERY that returns filtered results:

SELECT C.*
FROM Customers C
INNER JOIN
    (SELECT *
    FROM Orders
    WHERE orderDate >= '2022-09-01') AS S
ON C.customerId = S.customerId
WHERE S.customerId IS NOT NULL

WHERE

The WHERE clause is certainly where more SUBQUERY are used to perform comparisons and filter results:

SELECT *
FROM Customers
WHERE customerId IN
        ( SELECT customerId
        FROM  Orders
        WHERE product = 'Play Station 5');

In this execution, without the need to establish a JOIN between the Customers and Orders tables, we can return the information of a customer according to the name of a product of an order that has been made. Next, we will see another example:

SELECT customerId,
       orderId,
       product,
       price
FROM Orders
WHERE price > ALL
    ( SELECT price
    FROM Orders
     WHERE customerId = 2);

This execution is returning the records from the Orders table wich prices are greater than all the prices of customerId = 2.