A sub-question or Inner inquiry or Nested inquiry is a question inside another SQL Server inquiry and installed inside the WHERE statement. A sub inquiry is utilized to restore information that will be utilized in the principle question as a condition to additionally limit the information to be recovered.
Sub inquiries can be utilized with the SELECT, INSERT, UPDATE, and DELETE proclamations alongside the administrators like =, <, >, >=, <=, IN, BETWEEN, and so forth
There are a couple of decides that sub questions should follow −
- You should encase a subquery in enclosure.
- A subquery should incorporate a SELECT proviso and a FROM statement.
- A subquery can incorporate discretionary WHERE, GROUP BY, and HAVING provisos.
- A subquery ca exclude COMPUTE or FOR BROWSE provisos.
- You can incorporate an ORDER BY proviso just when a TOP statement is incorporated.
- You can settle sub questions up to 32 levels.
Subqueries with SELECT Statement
Syntax
Subqueries are most often utilized with the SELECT assertion. Following is the essential linguistic structure.
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 accompanying 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
Allow us to apply the accompanying subquery with SELECT explanation.
SELECT *
FROM CUSTOMERS
WHERE ID IN (SELECT ID FROM CUSTOMERS WHERE SALARY > 4500)
The above order will deliver the accompanying yield.
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 questions likewise can be utilized with INSERT articulations. The INSERT proclamation utilizes the information got back from the subquery to embed into another table. The chose information in the subquery can be adjusted with any of the character, date, or number capacities.
Syntax
Following is the fundamental punctuation.
INSERT INTO table_name [ (column1 [, column2 ]) ]
SELECT [ *|column1 [, column2 ]
FROM table1 [, table2 ]
[ WHERE VALUE OPERATOR ]
Example
Consider a table CUSTOMERS_BKP with comparative design as CUSTOMERS table. Following is the punctuation to duplicate total 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 utilized related to the UPDATE explanation. Either single or numerous sections in a table can be refreshed when utilizing a subquery with the UPDATE articulation.
Syntax
Following is the fundamental sentence structure.
UPDATE table
SET column_name = new_value
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Allow us to accept we have CUSTOMERS_BKP table accessible which is reinforcement of CUSTOMERS table.
Following order model updates SALARY by 0.25 occasions in CUSTOMERS table for all the clients whose AGE is more noteworthy than or equivalent to 27.
UPDATE CUSTOMERS
SET SALARY = SALARY * 0.25
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >= 27 )
This will affect two lines lastly CUSTOMERS table will have the accompanying 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 utilized related to the DELETE articulation like with some other assertions referenced previously.
Syntax
Following is the essential linguistic structure.
DELETE FROM TABLE_NAME
[ WHERE OPERATOR [ VALUE ]
(SELECT COLUMN_NAME
FROM TABLE_NAME)
[ WHERE) ]
Example
Allow us to accept we have CUSTOMERS_BKP table accessible which is reinforcement of CUSTOMERS table.
Following order model erases records from CUSTOMERS table for all the clients whose AGE is more noteworthy than or equivalent to 27.
DELETE FROM CUSTOMERS
WHERE AGE IN (SELECT AGE FROM CUSTOMERS_BKP WHERE AGE >=27 )
This would affect two columns lastly CUSTOMERS table will have the accompanying 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