SQL Subqueries

In this tutorial you will learn how to embed a query within another query in SQL.

What Is a Subquery?

A subquery, also known as a nested query or subselect, is a SELECT query embedded within the WHERE or HAVING clause of another SQL query. The data returned by the subquery is used by the outer statement in the same way a literal value would be used.

Subqueries provide an easy and efficient way to handle the queries that depend on the results from another query. They are almost identical to the normal SELECT statements, but there are few restrictions. The most important ones are listed below:

• A subquery must always appear within parentheses.
• A subquery must return only one column. This means you cannot use SELECT * in a subquery unless the table you are referring has only one column. You may use a subquery that returns multiple columns, if the purpose is row comparison.
• You can only use subqueries that return more than one row with multiple value operators, such as the IN or NOT IN operator.
• A subquery cannot be a UNION. Only a single SELECT statement is allowed.

Subqueries are most frequently used with the SELECT statement, however you can use them within a INSERT, UPDATE, or DELETE statement as well, or inside another subquery.

Subqueries with the SELECT Statement

The following statement will return the details of only those customers whose order value in the orders table is more than 5000 dollar. Also note that we've used the keyword DISTINCT in our subquery to eliminate the duplicate cust_id values from the result set.

SELECT * FROM customers
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders 
                      WHERE order_value > 5000);

Subqueries with the INSERT Statement

Subqueries can also be used with INSERT statements. Here's an example:

INSERT INTO premium_customers 
SELECT * FROM customers 
WHERE cust_id IN (SELECT DISTINCT cust_id FROM orders 
                      WHERE order_value > 5000);

The above statement will insert the records of premium customers into a table called premium_customers, by using the data returned from subquery. Here the premium customers are the customers who had placed order worth more than 5000 dollar.

Subqueries with the UPDATE Statement

You can also use the subqueries in conjunction with the UPDATE statement to update the single or multiple columns in a table, as follow:

UPDATE orders
SET order_value = order_value + 10
WHERE cust_id IN (SELECT cust_id FROM customers 
                      WHERE postal_code = 75016);

The above statement will update the order value in the orders table for those customers who live in the area whose postal code is 75016, by increasing the current order value by 10 dollar.

Subqueries with the DELETE Statement

Similarly, you can use the subqueries in conjunction with the DELETE statement to delete the single or multiple rows in a table, as follow:

DELETE FROM orders
WHERE order_id IN (SELECT order_id FROM order_details 
                   WHERE product_id = 5);

The SQL statement in the example above will delete those orders from the orders table that contains the product whose product_id is 5.