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
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
- A subquery cannot be a
UNION. Only a single
SELECTstatement is allowed.
Subqueries are most frequently used with the
SELECT statement, however you can use them within a
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);
Tip: A subquery can return a single value, a single row, a single column, or a table containing one or more rows of one or more columns.
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.
Tip: Checkout the tutorial on SQL cloning tables to learn how to quickly insert many rows into a table from another table using
INSERT ... SELECT statement.
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.