SQL Subquery

Summary: in this tutorial, you will learn about SQL subquery that is a regular query nested inside another query to form a complex query.

Introduction to SQL subquery

A subquery is a regular SELECT statement nested inside another query such as SELECT, UPDATE or DELETE statement. The following picture illustrates the subquery concept:

SQL Subquery

A subquery is also known as inner select or inner query, while the query that contains the subquery is called outer select or outer query.

In the picture above, the subquery returns a result set that consists of three rows.

reportsto data

This result set is fetched to the IN operator of the outer query. The query can be translated as:

managers

It returns all the managers in the employees table.

In this example, the result of the subquery is used by the outer query. The database engines executes the whole query twice, once for the subquery and once for the outer query.

A subquery can be also nested inside another subquery. The the number of nesting levels depends on the implementation of a specific database product. For example, Microsoft SQL Server supports up to 32 levels.

SQL subquery examples

In the following examples, we are going to use the following tables in the sample database:

  • customers – stores customer master data.
  • products – stores products master data.
  • orders – stores order header data including customer who made the purchases.
  • orderdetails – stores order line items data.

subquery tables

A subquery can return one or more rows. When the subquery returns a single row, you can use the comparison operator such as =, >, < , >=, <= and <> in the outer query to compare value with the value returned by the subquery.

For example, the following query selects all customers who locate the same city as the customer id BSBEV by using the not equal (<>) operator:

First, the subquery returns city where customer BSBEV locates, which is London. Then the London city is used to supply to the outer query to find all customers who locates in the London city.

customers locate the same city as BSBEV

SQL subquery with IN and NOT IN operators examples

In case the subquery returns a result set that contains multiple rows, you can use the IN or NOT IN operator in the outer query to check if value is in the set of values returned by the subquery.

For example, the following query selects all orders of customers in the USA. The subquery selects all customer IDs in the USA, and this set of IDs is used in the outer query to select orders:

orders of customers in USA

You can also use the  NOT IN operator to query all orders that are outside of the USA as the following query:

orders of customers outside USA

SQL subquery with the UPDATE statement example

A subquery can be nested inside other statements such as UPDATE and DELETE statements rather than the  SELECT statement.

For example, the following statement increases unit price of products supplied by the vendor 15 by 5%:

The subquery selects all IDs of products supplied by the vendor 15. The set of IDs is then fetched to the UDPATE statement to update the unit price.

SQL subquery as an expression example

A subquery can be used to substitute an expression in SQL statements. For example, the following query returns the prices of all beverage products, the product’s average price, and the difference between the unit price and the average price.

subquery as an expression example

SQL subquery with EXISTS and NOT EXISTS operators

A subquery can be used to test existence of rows when combining with the EXISTS and  NOT EXISTS operator.  Check it out the SQL EXISTS operator tutorial to learn how to use EXISTS opeartor with subqueries.

SQL correlated subquery

The subquery that we have seen so far can execute independently. There is another kind of subquery which cannot be executed independly called corralted subquery. We will cover the correlated subquery in the next tutorial.

In this tutorial, we have introduced you to the SQL subquery and shown you how to write queries that contains subqueries.