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:


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],
       (SELECT SUM (totalPrice)
       FROM Orders
       WHERE customerId = 4) AS totalCustomerSpending
FROM Customers
WHERE customerId = 4
name lastName totalCustomerSpending
Helen Ward 4999

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.


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

FROM Customers C
    (SELECT *
    FROM Orders
    WHERE orderDate >= '2022-09-01') AS S
ON C.customerId = S.customerId
customerId name lastName address bankAccount
4 Helen Ward 711 Hershell Hollow Road 447824556
4 Helen Ward 711 Hershell Hollow Road 447824556
5 Kimberly Lee 4298 Drummond Street 778345112


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

FROM Customers
WHERE customerId IN
        ( SELECT customerId
        FROM  Orders
        WHERE product = 'Play Station 5');
customerId name lastName address bankAccount
2 Mavin Pettitt 2336 Cottonwood Lane 123456789

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,
FROM Orders
WHERE price > ALL
    ( SELECT price
    FROM Orders
     WHERE customerId = 2);
customerId orderId product price
1 4 MacBook Pro M1 2449,5
NULL 8 Balay Oven 699,55

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