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
A subquery introduced with IN or NOT IN operator returns a result set then the outer query uses the result set for further processing.
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
A subquery can be nested in data manipulation language statements such as 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.