SQL Subqueries
Subquery is a query within a query.
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_IDs for finding S_IDs from STUDENT_COURSE TABLE.
STEP 1: Finding C_ID for C_NAME =’DSA’ or ‘DBMS’
STEP 2: Using C_ID of step 1 for finding S_ID
The inner query will return a set with members C1 and C3 and outer query will return those S_IDs 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 STUDENTs who have either enrolled in ‘DSA’ or ‘DBMS’, it can be done as:
NOT IN: If we want to find out S_IDs of STUDENTs who have neither enrolled in ‘DSA’ nor in ‘DBMS’, it can be done as:
The innermost query will return a set with members C1 and C3. Second inner query will return those S_IDs 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_IDs 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 STUDENTs who are enrolled in C_ID ‘C1’, it can be done with the help of co-related nested query as:
For each row of STUDENT S, it will find the rows from STUDENT_COURSE where S.S_ID = SC.S_IDand 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:
The subquery portion of the SELECT statement above is:
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.
This subquery could have alternatively been written as an INNER join as follows:
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.
FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
For example:
In this example, we've created a subquery in the FROM clause as follows:
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, COUNT, MIN, or MAX function, but you do not want the aggregate function to apply to the main query.
For example:
In this example, we've created a subquery in the SELECT clause as follows:
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, COUNT, MIN, or MAX function is commonly used in the subquery.
Last updated