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:

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:

WHERE

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

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:

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