Summary: in this tutorial, we will discuss about SQL subquery and show you how to use write complex SQL subqueries.
Introduction to SQL subquery
A subquery is a regular query that is nested inside another query such as SELECT, INSERT, UPDATE or DELETE statement. A subquery returns a result set if it executes independently. Then, this result set is used by the outer query. The following picture illustrates subquery concept:
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 or parent statement.
A subquery can be used anywhere in SELECT statement where an expression is accepted.
A subquery can be also nested inside another subquery. The nesting level of subquery depends on the implementation of specific database management system. For example, Microsoft SQL Server supports up to 32 levels.
We will give you some examples that illustrate the basic ideas of SQL subquery. We are going to use the following database schema in the sample database. The schema contains four tables:
- customers – contains customer master data.
- products – contains products master data.
- orders – contains order header data including customer who has orders.
- order_details – contains order line item data including products sold.
SQL subquery examples
SQL subquery with table alias
We must use table alias for the subquery that refers to the same table as outer query. For example, we can find customers who locate in the same city as customer with ID value BSBEV by using the following query:
SELECT customerid, companyname, city FROM customers WHERE customerid <> 'BSBEV' AND city = (SELECT c2.city FROM customers AS c2 WHERE c2.customerid = 'BSBEV')
First, the subquery returns city where customer BSBEV locates, which is London. Then London city is used to supply for the outer query to find all customers that locates in the London city.
SQL subquery with IN and NOT IN operators
The following query finds all orders that ordered by customers in USA. The subquery finds all customer IDs in USA and this result set is used for the outer query to retrieve orders:
SELECT orderid, customerid, shipname FROM orders WHERE customerid IN (SELECT customerid FROM customers WHERE country = 'USA')
We can also use NOT IN operator to query all orders outside USA as follows:
SELECT orderid, customerid, shipname FROM orders WHERE customerid NOT IN (SELECT customerid FROM customerS WHERE country = 'USA')
SQL subquery with UPDATE, DELETE and INSERT statements
Here is a query to increase unit price by 5% of all products supplied by vendor number 15.
UPDATE products SET unitprice = unitprice * 0.5 WHERE productid IN (SELECT productid FROM suppliers WHERE supplierid = 15)
SQL subquery used as expression
A subquery can be used to substitute an expression in SQL statements.
The following query displays the prices of all products in beverage category (with category id value 1), the products’ average price, and the difference between the unit price and the average price of products.
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
A subquery can be used to test existence of records. A subquery in this case return TRUE or FALSE and its result is used by WHERE clause of the outer query. Check it out the EXISTS operator tutorial for using subquery with EXISTS and NOT EXISTS operators.
In this tutorial, we have introduced you the SQL subquery concept with several examples of using subqueries. Understanding and using subquery appropriately so crucial that helps you to avoid using temporary table and cursor for querying data in complicated situations.