A Subquery is a query within another query. The outer query is called as main query and inner query is called as subquery.
A subquery (inner query) executes first then main query (outer query) executes and main query (outer query) use the subquery result .
There are some guidelines to consider when using subqueries as given below.
- A Subquery can be used with a Select , Insert , Update, or Delete statement along with comparison or logical operator such as > = ,<= , NOT , IN etc..
- A subquery is usually added within the Where Clause of another SQL select statement.
Order by Clause cannot be used in a subquery. - Where, Group by, Having clause can be used in a subquery.
- Subqueries must enclose within the parenthesis.
Lets look at an example of Subquery in SQL Server.
First we prepare a table first then insert some records into table using below scripts.
CREATE TABLE dbo.SalesDetails ( SalesId INT NOT NULL, SalesPerson VARCHAR(50), Product VARCHAR(150), Quantity INT, Price NUMERIC(9,2), [Date] DATE ) INSERT INTO dbo.SalesDetails VALUES (1,'Raj Kishor', 'Samsung Tablet', 5, 75000, '12-09-2019'), (2,'Raj Kishor', 'Samsung Tablet', 3, 41000, '08-12-2019'), (3,'Raj Kishor', 'Iphone 5S', 2, 36000, '05-01-2020'), (4,'Suraj Singh', 'Samsung Tablet', 2, 20000, '07-01-2020'), (5,'Suraj Singh', 'Iphone 7', 2, 55000, '12-24-2019'), (6,'Suraj Singh', 'Samsung NoteBook', 2, 35000, '01-25-2020'), (7,'Raj Kishor', 'Samsung Tablet', 6, 85000, '02-11-2020'), (8,'Mahesh Kumar', 'Iphone 6', 2, 49000, '02-21-2020'), (9,'Mahesh Kumar', 'Iphone 5S', 3, 45000, '03-03-2020'), (10,'Mahesh Kumar', 'Iphone 6', 2, 49000, '03-21-2020'), (11,'Raj Kishor', 'Iphone 6', 3, 65000, '03-25-2020'), (12,'Raj Kishor', 'Iphone 5S', 2, 36000, '03-27-2020'), (13,'Mohit Singh', 'Samsung Tablet', 3, 48000, '02-21-2020')
Now we have a table named as SalesDetails as shown below.
SELECT * FROM dbo.SalesDetails
Subquery in Select Statement
Lets see the example of using subquery in select statement,
In following statement you can see, two select statement, a subquery that is inner query and main query (outer query).
Subquery (inner query) fetches all the sales which are done in year 2020, then main Query (Outer query) uses output returned by subquery and returns Product wise Sales done by individual Sales in year 2020.
SELECT subqry.SalesPerson,
subqry.Product,
SUM(subqry.Quantity) AS TotalQty,
SUM(subqry.Price) AS TotalPrice
FROM (
SELECT SalesId,
SalesPerson,
Product,
Quantity,
Price,
[Date]
FROM dbo.SalesDetails WHERE YEAR(DATE)=2020
) AS subqry
GROUP BY subqry.SalesPerson, subqry.Product
Lets understand the statement by breaking it into two parts, firstly we will execute the subquery (inner query) and see the output return by subquery, and secondly we will execute the complete query to see the output.
Output of Subquery (inner query)
As you can see, it returns all the details of sales which are done in Year 2020.
Now, we will execute main query(Outer Query) that is completer query.
Output of Outer query
You can see, it returns Product Wise sales done by individual sales person, also this sales will be for year 2020 as Outer query uses the output returned by Subquery that is sales done for year 2020.
Using Subquery in WHERE Clause
You can also use subquery as an expression in Where clause, lets modify the above query and make it return the same result using subquery as an expression in WHERE clause.
SELECT SalesPerson, Product, SUM(Quantity) AS TotalQty, SUM(Price) AS TotalPrice FROM dbo.SalesDetails WHERE SalesId IN ( SELECT SalesId FROM dbo.SalesDetails WHERE YEAR(DATE)=2020 ) GROUP BY SalesPerson, Product
Lets understand the above query by breaking it in parts, first we will check the output of subquery (inner query). Once you execute the Subquery, it returns all SalesIds which belongs to Year 2020.
Output of Subquery
Now, we will execute the main query (Outer query), as Subquery is being used as an expression in Where clause of Outer Query. So the outer query returns the output for only those sales whose salesId are returned by Subquery.
Output of Outer Query
You can see the output, Product wise sales done by individual SalesPerson in year 2020.
Using Subquery in INSERT statement
You can also use Subquery with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table.
Lets prepare a sample table named as SalesDetails_Log that will be used to insert the data.
CREATE TABLE dbo.SalesDetails_Log ( SalesId INT NOT NULL, SalesPerson VARCHAR(50), Product VARCHAR(150), Quantity INT, Price NUMERIC(9,2), [Date] DATE )
Now we will insert the data of all sales done in year 2020 into this table using subquery.
Lets see the following statement, which uses insert into select statement which uses the data returned from the subquery to insert the data into SalesDetails_Log table.
INSERT INTO dbo.SalesDetails_Log ( SalesId, SalesPerson, Product, Price, [Date] ) SELECT SalesId, SalesPerson, Product, Price, [Date] FROM dbo.SalesDetails WHERE SalesId IN ( SELECT SalesId FROM dbo.SalesDetails WHERE YEAR(DATE)=2020 )
Lets execute the subquery part only in above statement, and see the output returned by Subquery.
You can see, it returns salesids belongs to sales done in year 2020.
Lets execute the Main Statement (Outer query), and you can see it executed successfully that means sales data for year 2020 are inserted into SalesDetails_Log table.
Lets check the SalesDetails_Log to verify whether data is inserted or not into table.
SELECT * FROM dbo.SalesDetails_LOG
You can see, data is inserted into SalesDetails_log table.
Using Subquery in Update Statement
You can also update records in table using subquery.
Lets update the sales price by 2000 for all sales done in year 2019.
Before doing that, Lets see the sales done in year 2019.
SELECT * FROM dbo.SalesDetails WHERE YEAR(DATE)=2019
You can see, there are three sales which are done in year 2019.
Following statement uses subquery(inner query) as an expression in Where Clause, which returns all salesid belongs to year 2019 to outer query and Outer query updates the price based on those SalesIds.
Update dbo.SalesDetails SET Price = Price + 2000 WHERE SalesId IN ( SELECT SalesId FROM dbo.SalesDetails WHERE YEAR(DATE)=2019 )
Lets break the above statement in two parts, first we will see the output returned by subquery(inner query).
Lets, execute only subquery part from Statement, and you can see it returns all Salesid belongs to sales done in year 2019.
Now, we will execute main query (Outer query), and it will updates the Sales’s price based on those SalesIds which are retuned by Subquery.
You can see, query executed successfully.
Lets select the sales for year 2019 to see whether the sales price are updated by 2000 or not.
SELECT * FROM dbo.SalesDetails WHERE YEAR(DATE)=2019
You can see, Sales price are updated by 2000 for 2019 sales.
Using Subquery in DELETE Statement
You can also use Subquery in Delete Statement, Lets See the below statement which delete the sales records for year 2019 and product
In following statement, Subquery (inner query) returns SalesId belongs to Sales done in year 2019 for Product ‘IPhone 7’.
The Main Query (Outer Query) that is delete statement, deletes the records from Sales tables based on those SalesIds which are returned by Subquery.
DELETE FROM dbo.SalesDetails WHERE SalesId IN ( SELECT SalesId FROM dbo.SalesDetails WHERE YEAR(DATE)=2019 AND PRODUCT ='Iphone 7' )
You can see, the output of Subquery, it returns one SalesId for year 2019 for product ‘Iphone 7’.
So, this record will be deleted only from sales table by outer query.
Lets execute the complete query, and see the table records.
You can see, query executed successfully. Lets verify whether the records for sales Id =5 deleted from sales table or not.
You will see, SalesId 5 does not exists in sales table now.
Also Read..
