The SQL subquery is also called an inner query, nested query, or inner select is a query nested within another query. The SQL Server subquery can nest inside SELECT, INSERT, DELETE, or UPDATE statements, or inside another.
In general, we mostly use the SQL Server subquery in WHERE Clause. That will be something like WHERE expression NOT IN, or IN subquery, etc. For the demo, we use the Employee table.
The basic rules for writing the SQL subquery or nested query
- It must enclose within the parenthesis.
- You cannot use ORDER BY Clause inside a sub-query unless you are using the TOP Clause
- A sub query must include a regular SELECT Statement and the FROM Clause
- You can also use the Optional WHERE, GROUP BY Clause, and HAVING Clause inside it.
- You must not include the COMPUTE or FOR BROWSE clause inside a sub query
- A sub query 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 called nested queries. It may vary too
SQL Subquery in Select Statement
In this simple example, we will write a SQL subquery in Select statement.
SELECT sq.FirstName + ' ' + sq.LastName AS [Full Name],
sq.[Occupation], sq.[YearlyIncome],sq.[Sales]
FROM (
SELECT [EmpID],[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
FROM [EmployeeTb]
WHERE [Sales] > 500
) AS [sq]
The inner query returns all the records from the Employee Table, whose sales amount is greater than 500
The main query extract, or select the required columns from sub query (Inner)
Subquery in an SQL Select Statement Example 2
Microsoft Server also allows us to add or use this as a column expression in the SELECT statement. Let us write it in the SELECT Statement.
TIP: Since we are using it as the Column expression, it must return a single value for each record returned by the main one.
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,
(
SELECT SUM([YearlyIncome]) FROM [EmployeeTb]
WHERE [Occupation] = 'Professional' OR [Occupation] = 'Management'
) AS [Total Income]
,[Sales]
,[HireDate]
FROM [EmployeeTb]
WHERE [Occupation] = 'Professional' OR [Occupation] = 'Management'
SQL Subquery in Where Clause
We can also use a SQL subquery as an expression in the WHERE Clause. In general, we write WHERE expression NOT IN or IN. In this example, we show how to write it in Where.
SELECT [EmpID]
,[FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [EmployeeTb]
WHERE [Sales] >= (
SELECT AVG([Sales]) FROM [EmployeeTb]
)
First, it will execute the inner query in WHERE Clause. Within the inner query, we are finding the Average of the Sales Amount.
Next, the main or outer query will select all the records present in the Employee Table, whose Sales Amount is greater than the average sale that was returned by the subquery.
SQL Server Subquery From Clause
It can also use in the FROM clause, which can help us to return multiple records. The following query shows how to write it in FROM Clause.
SELECT sq.Occupation,
SUM(sq.YearlyIncome) AS Income,
SUM(sq.Sales) AS TotalSale
FROM (
SELECT [EmpID],[FirstName],[LastName]
,[Education],[Occupation]
,[YearlyIncome]
,[Sales]
FROM [EmployeeTb]
WHERE [Sales] > 500
) AS sq
GROUP BY sq.Occupation
First, SQL subqueries will execute, and it will select all the records present in the Employee Table, whose Sales Amount is greater than 500.
Within the outer query, we are performing grouping on Occupation, and aggregating the Yearly Income, Sales amounts returned by the sub query. I suggest you refer GROUP BY Clause to understand the aggregations and the grouping in structured query language.
SQL Subquery in Case Statement
Let us see how to write a SQL Server subquery in a Case statement.
SELECT [EmpID]
,[FirstName] + SPACE(2) + [LastName] AS FullName
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,CASE WHEN
(
SELECT AVG([Sales]) FROM [EmployeeTb]
) <= emp.[Sales] THEN 'He is Performing Good'
ELSE 'He is Under Performing'
END AS Remarks
,[HireDate]
FROM [EmployeeTb] AS emp
First, it execute subquery, and it 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 the expression returns TRUE, it returns ‘He is Performing Good’ otherwise, ‘He is Under Performing’ as the output for Remarks Column.
Next, the main code 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 Sub queries are interdependent. I mean, inner one depends upon the main and vice versa.
SELECT [EmpID]
,[FirstName] + SPACE(2) + [LastName] AS FullName
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,CASE WHEN
(
SELECT AVG([Sales]) FROM [EmployeeTb] AS sb
WHERE sb.EmpID = emp.EmpID
) <= emp.[Sales] THEN 'He is Performing Good'
ELSE 'He is Under Performing'
END AS Remarks
,[HireDate]
FROM [EmployeeTb] AS emp
Although it gives the same result as the above Case statement example, it runs multiple times for multiple Employee Table Id. It is because the subquery needs Employee Id in Where Clause.
WHERE sb.Id = emp.Id
OK, let me run the Nested query independently.
Messages
-------
Msg 4104, Level 16, State 1, Line 13
The multi-part identifier "emp.EmpID" could not be bound.As you see, it is throwing an error. Now, let us run the whole code
Subquery in Update Statement
It can also used in the UPDATE Statement. It shows how to write a subqueries in the Update Statement to update the records in the Employee Table.
UPDATE [EmployeeTb]
SET [YearlyIncome] = [YearlyIncome] + 25000
WHERE [Sales] > (
SELECT AVG([Sales]) FROM [EmployeeTb]
)
Above nested example 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 sub query in the DELETE Statement. Write a SQL subquery in delete Statements to delete the rows from the Employee Table.
DELETE FROM [EmployeeTb]
WHERE [Sales] < (
SELECT AVG([Sales]) FROM [EmployeeTb]
)
Above DELETE Statement will Delete the records or rows from the employee table, whose sales amount is less than the average sales. Here we are using the nested query to get the Average Sales Amount.
Subquery in Insert Statement
The nested query also applied in Insert Into Select Statement. How to write a subquery in SQL Insert Statement to insert new records into the Employee Table.
INSERT INTO [EmployeeTb] ([FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate])
SELECT [FirstName]
,[LastName]
,[Education]
,[Occupation]
,[YearlyIncome]
,[Sales]
,[HireDate]
FROM [EmployeeTbOrginal]
The output of the Insert Into Select Statement is
