A sub-query or Inner query or Nested query is a query within another SQL Server query and embedded within the WHERE 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.
Subqueries can be used with the SELECT, INSERT, UPDATE, and DELETE statements along with the operators like =, <, >, >=, <=, IN, BETWEEN, etc.
There are a few rules that subqueries must follow −
- You must enclose a subquery in parenthesis.
- A subquery must include a SELECT clause and a FROM clause.
- A subquery can include optional WHERE, GROUP BY, and HAVING clauses.
- A subquery cannot include COMPUTE or FOR BROWSE clauses.
- You can include an ORDER BY clause only when a TOP clause is included.
- You can nest sub queries up to 32 levels.
Subqueries with SELECT Statement
Syntax
Subqueries are most frequently used with the SELECT statement. Following is the basic syntax.
SELECT column_name [, column_name ]
FROM table1 [, table2 ]
WHERE column_name OPERATOR
(SELECT column_name [, column_name ]
FROM table1 [, table2 ]
[WHERE])
Example
Consider the CUSTOMERS table having the following records.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
Let us apply the following subquery with the SELECT statement.
SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)
The above command will produce the following output.
ID NAME AGE ADDRESS SALARY
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00
Subqueries with INSERT Statement
Subqueries also can be used with INSERT statements. 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
Following is the basic syntax.
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
Example
Consider a table CUSTOMERS_BKP with a similar structure as the CUSTOMERS table. Following is the syntax to copy the complete CUSTOMERS table into CUSTOMERS_BKP.
INSERT INTO CUSTOMERS_BKP
SELECT * FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS)
Subqueries with 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
Following is the basic syntax.
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Let us assume we have the CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table.
Following command, example updates SALARY by 0.25 times in CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.
UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )
This will impact two rows and finally, the CUSTOMERS table will have the following records.
ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 500.00
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 2125.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
Subqueries with DELETE Statement
The subquery can be used in conjunction with the DELETE statement like with any other statements mentioned above.
Syntax
Following is the basic syntax.
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Let us assume we have the CUSTOMERS_BKP table available which is a backup of the CUSTOMERS table.
Following command, for example deletes records from the CUSTOMERS table for all the customers whose AGE is greater than or equal to 27.
DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )
This would impact two rows and finally, the CUSTOMERS table will have the following records.
ID NAME AGE ADDRESS SALARY
2 Khilan 25 Delhi 1500.00
3 kaushik 23 Kota 2000.00
4 Chaitali 25 Mumbai 6500.00
6 Komal 22 MP 4500.00
7 Muffy 24 Indore 10000.00
