SQL Subqueries are also called inner queries or inner select or a nested queries. The statement containing the subquery is also called an outer query or outer select. It is a query within another SQL query and embedded within the WHERE or HAVING clause. A subquery is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved. The inner query executes first before its parent query so that the results of an inner query can be passed to the outer query. There is no limit to the number of nested subqueries you can create. Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc for below mentioned activities
- Compare an expression to the result of the query.
- Determine if an expression is included in the results of the query.
- Check whether the query selects any rows.
Type of SQL subqueries
- Single row subquery: Returns zero or one row.
- Multiple row subquery: Returns one or more rows.
- Multiple column subqueries: Returns one or more columns.
- Correlated subqueries: Reference one or more columns in the outer SQL statement. The subquery is known as a correlated subquery because the subquery is related to the outer SQL statement.
- Nested subqueries: Subqueries are placed within another subquery.
Rules of SQL subqueries
- Subqueries must be enclosed within parentheses.
- A subquery can have only one column in the SELECT clause, unless multiple columns are in the main query for the subquery to compare its selected columns.
- An ORDER BY command cannot be used in a subquery, although the main query can use an ORDER BY. The GROUP BY command can be used to perform the same function as the ORDER BY in a subquery.
- Subqueries that return more than one row can only be used with multiple value operators such as the IN operator.
- The SELECT list cannot include any references to values that evaluate to a BLOB, ARRAY, CLOB, or NCLOB.
- A subquery cannot be immediately enclosed in a set function.
- The BETWEEN operator cannot be used with a subquery. However, the BETWEEN operator can be used within the subquery.
- 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.
Table: customers
| customer_id | customer_nm | address | city | Zip | country |
| 1 | Alex Angles | 230 Erwin Street | Woodland Hills | 91367 | USA |
| 2 | Frank Wilson | New Delhi Road | Delhi | 87653 | India |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
| 5 | Thomas Wilson | 1007 N Jackson street | Milwakee | 78881 | USA |
Table: orders
| order_id | customer_id | order_date | order_amt | Ship_id |
| 10001 | 1 | 2021-01-01 | 100.00 | 1 |
| 10002 | 5 | 2021-06-01 | 150.00 | 10 |
| 10003 | 3 | 2021-07-01 | 1000.00 | 3 |
| 10004 | 4 | 2021-07-15 | 250.50 | 2 |
| 10005 | 3 | 2021-08-01 | 225.50 | 5 |
SQL subqueries with the SELECT Statement
Subqueries are most frequently used with the SELECT statement. The basic syntax is as follows
Syntax SELECT column_name [, column_name ] FROM table1 [, table2 ] WHERE column_name OPERATOR (SELECT column_name [, column_name ] FROM table1 [, table2 ] [WHERE])
Query SELECT * FROM customers WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders WHERE order_amt > 200);
Explanation
Here the inner query will get the distinct customer ids that have an order amount of more than 200 dollars. So from the orders table, the customer id 3 & 4 have an order values of more than 200 dollars.
Result
| customer_id | customer_nm | address | city | Zip | country |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
| 4 | Mark Punk | Italiano blvd | Berlin | 10101 | Italy |
SQL subqueries with the INSERT Statement
The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date, or number functions.
Syntax INSERT INTO table_name [ (column1 [, column2 ]) ] SELECT [ *|column1 [, column2 ] FROM table1 [, table2 ] [ WHERE VALUE OPERATOR ]
INSERT INTO premium_customers SELECT * FROM customers WHERE customer_id IN (SELECT DISTINCT customer_id FROM orders WHERE order_amount > 1000);
Explanation
The above query will insert the records of premium customers into a table called premium_customers, by using the data returned from the subquery. Here the premium customers are the customers who had placed order worth more than 1000 dollars.
Result
premium_customers
| customer_id | customer_nm | address | city | Zip | country |
| 3 | Daniel Perker | Hilton street | Paris | 75016 | France |
SQL subqueries with the UPDATE Statement
The subquery can be used in conjunction with the UPDATE statement. Either single or multiple columns in a table can be updated when using a subquery with the UPDATE statement.
Syntax UPDATE table SET column_name = new_value [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
Query UPDATE orders SET order_amount = order_amount + 10 WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = ‘USA’);
The above statement will update the order value in the orders table for those customers who live in country USA, by increasing the current order value by 10 dollars.
Result
The customers who are from USA is
| order_id | customer_id | order_date | order_amt | Ship_id |
| 10001 | 1 | 2021-01-01 | 110.00 | 1 |
| 10002 | 5 | 2021-06-01 | 160.00 | 10 |
The final orders table will be
| order_id | customer_id | order_date | order_amt | Ship_id |
| 10001 | 1 | 2021-01-01 | 110.00 | 1 |
| 10002 | 5 | 2021-06-01 | 160.00 | 10 |
| 10003 | 3 | 2021-07-01 | 1000.00 | 3 |
| 10004 | 4 | 2021-07-15 | 250.50 | 2 |
| 10005 | 3 | 2021-08-01 | 225.50 | 5 |
SQL subqueries with the DELETE Statement
The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.
DELETE FROM TABLE_NAME [ WHERE OPERATOR [ VALUE ] (SELECT COLUMN_NAME FROM TABLE_NAME) [ WHERE) ]
DELETE FROM orders WHERE customer_id IN (SELECT customer_id FROM customers WHERE country = ‘France’);
Result
Below two rows will be deleted as the customer_id for country ‘France’ is 3.
| order_id | customer_id | order_date | order_amt | Ship_id |
| 10003 | 3 | 2021-07-01 | 1000.00 | 3 |
| 10005 | 3 | 2021-08-01 | 225.50 | 5 |
The final table is
| order_id | customer_id | order_date | order_amt | Ship_id |
| 10001 | 1 | 2021-01-01 | 100.00 | 1 |
| 10002 | 5 | 2021-06-01 | 150.00 | 10 |
| 10004 | 4 | 2021-07-15 | 250.50 | 2 |
SQL subquery Examples
Table: department
DEPT_ID DEPT_NAME DEPT_BUDGET
-------- --------------- -------------
50 IT 165000
60 Finance 10000
40 HR 300000
20 Management 35000
80 Legal 175000
Table: employee
EMP_ID EMP_FNAME EMP_LNAME EMP_DEPT
--------- --------------- --------------- ----------
10 Michale Stack 50
50 Ian Moya 60
80 Norman Lu 50
30 Jhon Hustol 60
40 Jose Sandes 40
60 Jack Jose 40
81 Karl Mayor 50
70 Henrey Fox 40
51 Alex Samuel 50
52 George Ford 20
71 Carl Samuel 60
61 Alan Walker 20
82 Maria Mendoza 50
Question: Write a query in SQL to find the first name and last name of employees working for departments which budget amount is the second-lowest.
Query
SELECT emp_fname, emp_lname
FROM employee
WHERE emp_dept IN (
SELECT dept_id
FROM emp_dept
WHERE dept_budget= (
SELECT MIN(dept_budget)
FROM department
WHERE dpt_budget >
(SELECT MIN(dpt_budget)
FROM department )));
The HR department has the highest budget & the lowest budget department is finance. The second-lowest budget department is Management.
Result emp_fname emp_lname Alan Walker George Ford
Question: Write a query to find the names of departments with more than two employees are working.
Query
SELECT dept_name FROM department
WHERE dept_id IN
(
SELECT emp_dept
FROM employee
GROUP BY emp_dept
HAVING COUNT(*) >2
);
The department ID which have more than 2 employees are 50,60 & 40.
Result dept_name IT HR Finance
Question: Write a query in SQL to find the departments which sanction amount is larger than the average sanction amount of all the departments.
Query
SELECT *
FROM department
WHERE dept_budget >
(
SELECT AVG(dept_budget)
FROM department
);
The average department budget is 137000. The department which has move budget then this average are given below.
Result
DEPT_ID DEPT_NAME DEPT_BUDGET
-------- --------------- -------------
50 IT 165000
40 HR 300000
80 Legal 175000