A sub-query or Inner query or Nested query is a query within another SQL Server query and embedded within the WHERE clause. A sub query is used to return data that will be used in the main query as a condition to further restrict the data to be retrieved.

Sub queries 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 sub queries 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 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

Sub queries 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 similar structure as CUSTOMERS table. Following is the syntax to copy 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 CUSTOMERS_BKP table available which is backup of 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 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 CUSTOMERS_BKP table available which is backup of CUSTOMERS table.

Following command example deletes records from 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 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