Tutorial Gateway

SQL Subquery

by

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

The following are some of the basic rules for writing the SQL subquery

  • Subqueries must be enclosed 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

In this article, we will show you, How to write a subquery in SQL Server with an example. For the demonstration purpose, We are going to use the Employee table present in our [SQL Tutorial] Database.

SQL Server CTE Example 0

SQL Subquery in Select Statement

In this simple example, we will show you, How to write a SQL subquery in Select statement with practical example.

-- 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) in this example will return all the records from Employee Table, whose sales amount is greater than 500

SQL subquery example 2

OUTPUT of Main Query

The main query of this example will extract, or select the required columns from subquery (Inner query)

SQL subquery example 3

SQL Subquery in Select Statement Example 2

Microsoft also allows us to add or use a subquery as a column expression in the SELECT statement. In this example, we will show you, How to write a SQL subquery in 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'

OUTPUT

SQL subquery example 4

SQL Subquery in Where Clause

We can also use a subquery as an expression in the WHERE Clause. In this example, we will show you, 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, and within the subquery, we are finding the Average of 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 example 5

SQL Subquery in From Clause

The Sql Server subquery can also be used in FROM clause, and this can help us to return multiple records from this query. In this example, we will show you, 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 be executed, 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 article to understand the grouping concept.

SQL subquery example 6

SQL Subquery in Case Statement

In this example, we will show you, 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 SQL Server will execute subquery, and the query will find the Average of Sales Amount in this table.

Next, we are using the CASE Statement to check whether the Sales Amount is greater than Average Sales (1970.9055), and if it is TRUE then it returns ‘He is Performing Good’ otherwise, ‘He is Under Performing’ as the output for Remarks Column.

Next, the main query will select all the records present in the Employee Table, along with the Remarks Column we got from Case Statement.

TIP: Here we used the SPACE Function to return two empty spaces between the First name and Last name.

SQL subquery example 7

Correlated subquery in SQL Server

All the above-specified queries are non-correlated subqueries because they run independently. In this example, we will show you, How to write a Correlated subquery in SQL server. These kind of Subqueries are interdependent. I mean, Subquery depends upon 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, subquery will run multiple times for multiple Employee Table Id. This is because subquery needs Employee Id in Where Clause.

WHERE sub.Id = emp.Id

OK, let me run the subquery independently.

SQL Correlated subquery 8

From the above screenshot, you can see that it is throwing an error. Now, let us run the whole query

SQL Correlated subquery 9

SQL subquery in Update Statement

A subquery in SQL Server can also be used in the UPDATE Statement. In this example, we will show you, How to write a subquery in 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

OUTPUT

SQL subquery example 10

Subquery in Delete Statement

We can also use the subquery in the DELETE Statement. In this example, we will show you, How to write a subquery in delete Statement to delete the rows from 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 employee table, whose sales amount is less than the average Sales. Here we are using the subquery to get the Average Sales Amount.

OUTPUT

SQL subquery example 11

SQL Server subquery in Insert Statement

The SQL Server subquery can also be used in Insert Into Select Statement. In this example, we will show you, 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 as shown below

SQL subquery example 12

Thank You for Visiting Our Blog

Placed Under: SQL