Correlated subquery in SQL Server

SQL Subquery

The SQL subquery is 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, 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 demo, we use the Employee table.

Data Source

The basic rules for writing the 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 it.
  • 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.
  • SQL Server allows you to nest subqueries up to 32 levels called nested queries. It may vary too.

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

Example 1

The primary query extract or select the required columns from subquery (Inner)

Execute Main

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 Example 4

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.

Where Clause

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 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.

in From Clause

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 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.

SQL Subquery in Case Statement

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 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

Correlated subquery in SQL Server

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 Bonus) to each person whose sales amount is greater than the average Sales.

in Update Statement

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.

in Delete Statement

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

in Insert Statement