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:
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.
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.
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 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.
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.
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.