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 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.
SELECT DISTINCT reportsto FROM employees
This result set is fetched to the IN operator of the outer query. The query can be translated as:
SELECT employeeid, firstname, lastname FROM employees WHERE employeeid IN (5 , 3, null);
It returns all the managers in the
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.
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:
SELECT customerid, companyname, city FROM customers WHERE customerid <> 'BSBEV' AND city = (SELECT city FROM customers WHERE customerid = 'BSBEV')
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.
SQL subquery with IN and NOT IN operators examples
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:
SELECT orderid, customerid, shipname FROM orders WHERE customerid IN ( SELECT customerid FROM customers WHERE country = 'USA');
You can also use the
NOT IN operator to query all orders that are outside of the USA as the following query:
SELECT orderid, customerid, shipname FROM orders WHERE customerid NOT IN ( SELECT customerid FROM customerS WHERE country = 'USA')
SQL subquery with the UPDATE statement example
For example, the following statement increases unit price of products supplied by the vendor 15 by 5%:
UPDATE products SET unitprice = unitprice * 1.05 WHERE productid IN ( SELECT productid FROM suppliers WHERE supplierid = 15);
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.
Code language: PHP (php)
SELECT productid, productname, (SELECT AVG(unitprice) FROM products) AS 'average price', (unitprice - ( SELECT AVG(unitprice) FROM products)) AS diff FROM products WHERE categoryid = 1
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 contain subqueries.