A subquery in SQL Server is also called an inner query, nested query, or inner select is a query nested within another query. The SQL subquery can nest inside a SELECT, INSERT, DELETE, or UPDATE statements, or inside another subquery. In general, we mostly use the SQL Server subquery in WHERE Clause. That will be something like WHERE expression NOT IN subquery, or IN subquery, etc. For the SQL Server Subquery demo, we use the Employee table.
The basic rules for writing the SQL subquery
- Subqueries must enclose within the parenthesis.
- You cannot use ORDER BY Clause inside a subquery unless you are using the TOP Clause
- A subquery must include a regular SELECT Statement and the FROM Clause
- You can also use the Optional WHERE Clause, GROUP BY Clause, and HAVING Clause inside a subquery.
- You must not include the COMPUTE or FOR BROWSE clause inside a subquery
- A subquery can be nested inside the WHERE or HAVING clauses of an outer SELECT, INSERT, DELETE, or UPDATE statements.
- SQL Server allows you to nest subqueries up to 32 levels. It may vary too
SQL Subquery in Select Statement
In this simple example, we will write a SQL subquery in Select statement.
-- SQL NESTED QUERY EXAMPLE
USE [SQL Tutorial]
GO
SELECT subquery.FirstName + ' ' + subquery.LastName AS [Full Name],
subquery.[Occupation], subquery.[YearlyIncome],
subquery.[Sales]
FROM (
SELECT [Id]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Employee Table]
WHERE [Sales] > 500
) AS [subquery]
OUTPUT of the subquery
The inner query (subquery) returns all the records from the Employee Table, whose sales amount is greater than 500
OUTPUT of Main Query
The main query extract, or select the required columns from subquery (Inner query)
SQL Subquery in Select Statement Example 2
Microsoft SQL Server also allows us to add or use a subquery as a column expression in the SELECT statement. Let us write a SQL subquery in the SELECT Statement.
TIP: Since we are using the subquery as the Column expression, the subquery must return a single value for each record returned by the main query.
-- SQL NESTED QUERY EXAMPLE
USE [SQL Tutorial]
GO
SELECT [Id]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,
(
SELECT SUM([YearlyIncome]) FROM [Employee Table]
WHERE [Occupation] = 'Professional' OR [Occupation] = 'Management'
) AS [Total Income]
,[Sales]
,[HireDate]
FROM [Employee Table]
WHERE [Occupation] = 'Professional' OR [Occupation] = 'Management'
SQL Subquery in Where Clause
We can also use a subquery as an expression in the WHERE Clause.In general, we write WHERE expression NOT IN subquery, or IN subquery. In this example, we show how to write a SQL Server subquery in Where Clause.
-- SQL NESTED QUERY EXAMPLE
USE [SQL Tutorial]
GO
SELECT [Id]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [Employee Table]
WHERE [Sales] >= (
SELECT AVG([Sales]) FROM [Employee Table]
)
First, it will execute the inner query (subquery) in WHERE Clause. Within the subquery, we are finding the Average of the Sales Amount. Next, the main query will select all the records present in the Employee Table, whose Sales Amount is greater than the average sale (returned by subquery).
SQL Subquery in From Clause
The SQL Server subquery can also use in the FROM clause, and this can help us to return multiple records from this query. It shows how to write a SQL subquery in FROM Clause.
-- SQL NESTED QUERY EXAMPLE
USE [SQL Tutorial]
GO
SELECT sub.Occupation,
SUM(sub.YearlyIncome) AS Income,
SUM(sub.Sales) AS TotalSale
FROM (
SELECT [Id]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [Employee Table]
WHERE [Sales] > 500
) AS sub
GROUP BY sub.Occupation
First, Subquery will execute, and it will select all the records present in the Employee Table, whose Sales Amount is greater than 500.
Within the main query, we are performing grouping on Occupation, and aggregating the Yearly Income, Sales amounts returned by the subquery. I suggest you refer GROUP BY Clause.
SQL Subquery in Case Statement
Let us see how to write a SQL Server subquery in a Case statement.
-- SQL Server Subquery - SQL NESTED QUERY EXAMPLE
USE [SQL Tutorial]
GO
SELECT [Id]
,[FirstName] + SPACE(2) + [LastName] AS FullName
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,CASE WHEN
(
SELECT AVG([Sales]) FROM [Employee Table]
) <= emp.[Sales] THEN 'He is Performing Good'
ELSE 'He is Under Performing'
END AS Remarks
,[HireDate]
FROM [Employee Table] AS emp
First, it execute subquery, and the query will find the Average of the Sales Amount in this table. Next, we are using the CASE statement to check whether the Sales Amount is higher than Average Sales (1970.9055). If it is TRUE, it returns ‘He is Performing Good’ otherwise, ‘He is Under Performing’ as the output for Remarks Column.
Next, the main query selects all the records present in the Employee Table, along with the Remarks Column we got from Case Statement. Here, we used the SPACE Function to return two empty spaces between the First name and Last name
Correlated subquery in SQL Server
All the above-specified queries are non-correlated subqueries because they run independently. Let us see how to write a Correlated subquery in SQL server. These kinds of Subqueries are interdependent. I mean, Subquery depends upon the main query and vice versa.
-- SQL Server CORRELATED SUBQUERY EXAMPLE
USE [SQL Tutorial]
GO
SELECT [Id]
,[FirstName] + SPACE(2) + [LastName] AS FullName
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,CASE WHEN
(
SELECT AVG([Sales]) FROM [Employee Table] AS sub
WHERE sub.Id = emp.Id
) <= emp.[Sales] THEN 'He is Performing Good'
ELSE 'He is Under Performing'
END AS Remarks
,[HireDate]
FROM [Employee Table] AS emp
Although it gives the same result as the above Case statement example, the subquery runs multiple times for multiple Employee Table Id. It is because subquery needs Employee Id in Where Clause.
WHERE sub.Id = emp.Id
OK, let me run the subquery independently.
Messages
-------
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "emp.Id" could not be bound.As you see, it is throwing an error. Now, let us run the whole query
Subquery in Update Statement
A subquery in SQL Server also used in the UPDATE Statement. It shows how to write a subquery in the Update Statement to update the records in the Employee Table.
-- SQL Server subquery EXAMPLE
USE [SQL Tutorial]
GO
UPDATE [Employee Table]
SET [YearlyIncome] = [YearlyIncome] + 25000
WHERE [Sales] > (
SELECT AVG([Sales]) FROM [Employee Table]
)
Above subquery in UPDATE Statement will update the Yearly Income by adding 25000 (something like Bonus) to each person, whose sales amount is greater than the average Sales.
Subquery in Delete Statement
We can also use the subquery in the DELETE Statement. Write a subquery in delete Statements to delete the rows from the Employee Table.
-- SQL Server subquery EXAMPLE
USE [SQL Tutorial]
GO
DELETE FROM [Employee Table]
WHERE [Sales] < (
SELECT AVG([Sales]) FROM [Employee Table]
)
Above DELETE Statement query will Delete the records or rows from the employee table, whose sales amount is less than the average sales. Here we are using the subquery to get the Average Sales Amount.
Subquery in Insert Statement
The subquery also applied in Insert Into Select Statement. How to write a subquery in SQL Insert Statement to insert new records into the Employee Table.
-- SQL server subquery EXAMPLE
USE [SQL Tutorial]
GO
INSERT INTO [Employee Table] ([FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate])
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [SQL Tutorial].[dbo].[Employee Table2]
WHERE [Sales] < 1950
The output of the subquery in Insert Into Select Statement is
