The SQL subquery also called an inner query, nested query, or inner select is a query nested within another query. For example, the SQL Server subquery can nest inside SELECT, INSERT, DELETE, and UPDATE statements, or inside another.
We mainly use the SQL Server subquery in the WHERE Clause. That will be something like WHERE expression NOT IN, or IN subquery, etc.
‘For the SQL subquery or nested query 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 the ORDER BY Clause inside a sub-query unless you use the TOP Clause.
- It must include a regular SELECT Statement and the FROM Clause
- You can also use the Optional WHERE, GROUP BY Clause, and HAVING Clause inside SQL Subquery or nested query.
- You must not include the COMPUTE or FOR BROWSE clause inside it.
- It can be nested inside the WHERE or HAVING clauses of an outer SELECT, INSERT, DELETE, or UPDATE statements.
- It allows you to nest subqueries up to 32 levels called nested queries. It may vary too.
SQL Subquery in Select Statement
This simple example will write a nested query in the 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 primary query extract or select the required columns from the SQL subquery (Inner or nested query).
SQL subquery Select Statement Example 2
Microsoft Server allows us to add or use this as a column expression in the SELECT statement. So, 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 Server Subquery in Where Clause
We can also use a nested query 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. Then, within the inner query, we find the Average of the Sales Amount.
Next, the primary or outer query will select all the records in the Employee Table whose Sales Amount exceeds the average sale.
SQL Server Subquery From Clause
It can also use in the FROM clause, which can help us to return multiple records. For example, 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 subquery or nested queries will execute, selecting all the records in the Employee Table whose Sales Amount exceeds 500.
Within the outer query, we are grouping on Occupation and aggregating the Yearly Income, and Sales amounts returned. I suggest you refer GROUP BY Clause to understand the aggregations and the grouping in a structured query language.
SQL Nested Query 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 executes a subquery, and it will find the Average of the Sales Amount in this table. Next, we use the CASE statement to check whether the Sales Amount is higher than the Average Sales (1970.9055). If the expression returns TRUE, it returns ‘He is Performing Good’; otherwise, ‘He is Under Performing’ as the output for the Remarks Column.
Next, the main code selects all the records present in the Employee Table and the Remarks Column we got from Case Statement. Here, we used the SPACE Function to return two empty spaces between the first and last names.
Correlated subquery in SQL Server
All the above-specified queries are non-correlated because they run independently. Let us see how to write a Correlated subquery in SQL server. These kinds are interdependent. I mean, the 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 the 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
SQL Subquery in Update Statement
It can also use in the UPDATE Statement. It shows how to write it 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]
)
The above example in the UPDATE Statement will update the Yearly Income by adding 25000 (something like a Bonus) to each person whose sales amount is greater than the average Sales.
SQL Subquery in Delete Statement
We can also use it in the DELETE Statement. Write a 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. For example, we are using the nested query to get the Average Sales Amount.
SQL Subquery in Insert Statement
We can also use it in Insert Into Select Statement. For example, write a subquery in 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
