# SQL Subqueries ## SQL Subquery Subquery is a query is written inside a query. The result of inner query is used in execution of outer query. You can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause. We will use **STUDENT, COURSE, STUDENT\_COURSE** tables for understanding nested queries. **STUDENT** | **S\_ID** | **S\_NAME** | **S\_ADDRESS** | **S\_PHONE** | **S\_AGE** | | --------- | ----------- | -------------- | ------------ | ---------- | | S1 | RAM | DELHI | 9455123451 | 18 | | S2 | RAMESH | GURGAON | 9652431543 | 18 | | S3 | SUJIT | ROHTAK | 9156253131 | 20 | | S4 | SURESH | DELHI | 9156768971 | 18 | **COURSE** | **C\_ID** | **C\_NAME** | | --------- | ----------- | | C1 | DSA | | C2 | Programming | | C3 | DBMS | **STUDENT\_COURSE** | **S\_ID** | **C\_ID** | | --------- | --------- | | S1 | C1 | | S1 | C3 | | S2 | C1 | | S3 | C2 | | S4 | C2 | | S4 | C3 | There are mainly two types of nested queries: ## **Nested Sub Queries** In independent nested queries, query execution starts from innermost query to outermost queries. The execution of inner query is independent of outer query, but the result of inner query is used in execution of outer query. Various operators like IN, NOT IN, ANY, ALL etc are used in writing independent nested queries. **IN:** If we want to find out **S\_ID** who are enrolled in **C\_NAME** ‘DSA’ or ‘DBMS’, we can write it with the help of independent nested query and IN operator. From **COURSE** table, we can find out **C\_ID** for **C\_NAME** ‘DSA’ or DBMS’ and we can use these **C\_ID**s for finding **S\_ID**s from **STUDENT\_COURSE** TABLE. **STEP 1:** Finding **C\_ID** for **C\_NAME** =’DSA’ or ‘DBMS’ ```sql Select C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME = ‘DBMS’; ``` **STEP 2:** Using **C\_ID** of step 1 for finding **S\_ID** ```sql Select S_ID from STUDENT_COURSE where C_ID IN (SELECT C_ID from COURSE where C_NAME = ‘DSA’ or C_NAME=’DBMS’); ``` The inner query will return a set with members C1 and C3 and outer query will return those **S\_ID**s for which **C\_ID** is equal to any member of set (C1 and C3 in this case). So, it will return S1, S2 and S4. **Note:** If we want to find out names of **STUDENT**s who have either enrolled in ‘DSA’ or ‘DBMS’, it can be done as: ```sql Select S_NAME from STUDENT where S_ID IN (Select S_ID from STUDENT_COURSE where C_ID IN (SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’)); ``` **NOT IN:** If we want to find out **S\_ID**s of **STUDENT**s who have neither enrolled in ‘DSA’ nor in ‘DBMS’, it can be done as: ```sql Select S_ID from STUDENT where S_ID NOT IN (Select S_ID from STUDENT_COURSE where C_ID IN (SELECT C_ID from COURSE where C_NAME=’DSA’ or C_NAME=’DBMS’)); ``` The innermost query will return a set with members C1 and C3. Second inner query will return those **S\_ID**s for which **C\_ID** is equal to any member of set (C1 and C3 in this case) which are S1, S2 and S4. The outermost query will return those **S\_ID**s where **S\_ID** is not a member of set (S1, S2 and S4). So it will return S3. ## **Co-related Sub Queries** In co-related nested queries, the output of inner query depends on the row which is being currently executed in outer query. e.g.; If we want to find out **S\_NAME** of **STUDENT**s who are enrolled in **C\_ID** ‘C1’, it can be done with the help of co-related nested query as: ```sql Select S_NAME from STUDENT S where EXISTS ( select * from STUDENT_COURSE SC where S.S_ID=SC.S_ID and SC.C_ID=’C1’); ``` For each row of **STUDENT** S, it will find the rows from **STUDENT\_COURSE** where S.**S\_ID** = SC.**S\_ID**and SC.**C\_ID**=’C1’. If for a **S\_ID** from **STUDENT** S, atleast a row exists in **STUDENT\_COURSE** SC with **C\_ID**=’C1’, then inner query will return true and corresponding **S\_ID** will be returned as output. This SQL Server tutorial explains how to use **subqueries** in SQL Server (Transact-SQL) with syntax and examples. ### WHERE clause Most often, the subquery will be found in the WHERE clause. These subqueries are also called **nested subqueries**. For example: ```sql SELECT p.product_id, p.product_name FROM products p WHERE p.product_id IN (SELECT inv.product_id FROM inventory inv WHERE inv.quantity > 10); ``` The subquery portion of the SELECT statement above is: ```sql (SELECT inv.product_id FROM inventory inv WHERE inv.quantity > 10); ``` This subquery allows you to find all *product\_id* values from the inventory table that have a quantity greater than 10. The subquery is then used to filter the results from the main query using the [IN condition](https://www.techonthenet.com/sql_server/in.php). This subquery could have alternatively been written as an INNER join as follows: ```sql SELECT p.product_id, p.product_name FROM products p INNER JOIN inventory inv ON p.product_id = inv.product_id WHERE inv.quantity > 10; ``` This INNER JOIN would run more efficiently than the original subquery. It is important to note, though, that not all subqueries can be rewritten using [joins](https://www.techonthenet.com/sql_server/joins.php). ### FROM clause A subquery can also be found in the FROM clause. These are called **inline views**. For example: ```sql SELECT suppliers.supplier_name, subquery1.total_amt FROM suppliers, (SELECT supplier_id, SUM(orders.amount) AS total_amt FROM orders GROUP BY supplier_id) subquery1 WHERE subquery1.supplier_id = suppliers.supplier_id; ``` In this example, we've created a subquery in the FROM clause as follows: ```sql (SELECT supplier_id, SUM(orders.amount) AS total_amt FROM orders GROUP BY supplier_id) subquery1 ``` This subquery has been aliased with the name *subquery1*. This will be the name used to reference this subquery or any of its fields. ### SELECT clause A subquery can also be found in the SELECT clause. These are generally used when you wish to retrieve a calculation using an aggregate function such as the [SUM](https://www.techonthenet.com/sql_server/functions/sum.php), [COUNT](https://www.techonthenet.com/sql_server/functions/count.php), [MIN](https://www.techonthenet.com/sql_server/functions/min.php), or [MAX](https://www.techonthenet.com/sql_server/functions/max.php) function, but you do not want the aggregate function to apply to the main query. For example: ```sql SELECT e1.last_name, e1.first_name, (SELECT MAX(salary) FROM employees e2 WHERE e1.employee_id = e2.employee_id) subquery2 FROM employees e1; ``` In this example, we've created a subquery in the SELECT clause as follows: ```sql (SELECT MAX(salary) FROM employees e2 WHERE e1.employee_id = e2.employee_id) subquery2 ``` The subquery has been aliased with the name *subquery2*. This will be the name used to reference this subquery or any of its fields. The trick to placing a subquery in the select clause is that the subquery must return a single value. This is why an aggregate function such as the [SUM](https://www.techonthenet.com/sql_server/functions/sum.php), [COUNT](https://www.techonthenet.com/sql_server/functions/count.php), [MIN](https://www.techonthenet.com/sql_server/functions/min.php), or [MAX](https://www.techonthenet.com/sql_server/functions/max.php) function is commonly used in the subquery. --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://gyansetu-sql.gitbook.io/sql-programming/sql-select/sql-subqueries.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.