Tutorial Gateway

  • C
  • C#
  • Java
  • Python
  • SQL
  • MySQL
  • Js
  • BI Tools
    • Informatica
    • Talend
    • Tableau
    • Power BI
    • SSIS
    • SSRS
    • SSAS
    • MDX
    • R Tutorial
    • Alteryx
    • QlikView
  • More
    • C Programs
    • C++ Programs
    • Go Programs
    • Python Programs
    • Java Programs

SQL Subquery

by suresh

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.

SQL Server CTE Example 0

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

SQL subquery example 2

OUTPUT of Main Query

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

SQL subquery example 3

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

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

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

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

SQL subquery example 7

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.

SQL Correlated subquery 8

As you see, it is throwing an error. Now, let us run the whole query

SQL Correlated subquery 9

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.

SQL subquery example 10

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.

SQL subquery example 11

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

SQL subquery example 12

Placed Under: SQL

  • Install SQL Server
  • Install SQL Management Studio
  • Uninstall Management Studio
  • Install AdventureWorks Database
  • SQL Management Studio Intro
  • Connect SQL with sqlcmd utility
  • SQL Attach Database
  • SQL Detach Database
  • SQL Restore Database
  • Restore Database using BAK
  • SQL Rename Database with Files
  • Get SQL Database Names
  • SQL Create Table
  • SQL Rename Table
  • SQL Alter Table
  • SQL Add Column
  • SQL Rename Column
  • Get SQL Table Names in a DB
  • Find SQL Table Dependencies
  • Rename SQL Table & Column
  • SQL Global & Local Temp Table
  • SQL Table Variable
  • SQL Derived Table
  • SQL DATALENGTH
  • SQL Data Types
  • DML, DDL, DCL & TCL Cmds
  • SQL Query Builder
  • SQL ALIAS
  • SQL SELECT Statement
  • SQL SELECT DISTINCT
  • SQL SELECT INTO Statement
  • SQL INSERT Statement
  • SQL INSERT INTO SELECT
  • SQL BULK INSERT or BCP
  • SQL UPDATE Statement
  • SQL UPDATE from SELECT
  • SQL DELETE Statement
  • SQL TRUNCATE Table
  • SQL CASE Statement
  • SQL MERGE Statement
  • SQL Subquery
  • SQL CTE
  • SQL PIVOT
  • SQL UNPIVOT
  • SQL Clauses Examples
  • SQL TOP Clause
  • SQL WHERE Clause
  • SQL ORDER BY Clause
  • SQL GROUP BY Clause
  • SQL HAVING Clause
  • SQL Primary Key
  • SQL Foreign Key
  • SQL Referential Integrity
  • SQL Check Constraint
  • SQL Unique Constraint
  • SQL Default Constraint
  • SQL Clustered Index
  • SQL Non Clustered Index
  • SQL Filtered Indexes
  • SQL COALESCE Function
  • SQL IS NOT NULL
  • SQL IS NULL Function
  • SQL ISNULL
  • SQL JOINS
  • SQL CROSS JOIN
  • SQL FULL JOIN
  • SQL SELF JOIN
  • SQL Outer Joins
  • SQL Cross Join Vs Inner Join
  • SQL LEFT JOIN
  • SQL RIGHT JOIN
  • SQL AND & OR Operators
  • SQL Arithmetic Operators
  • SQL BETWEEN Operator
  • SQL Comparison Operators
  • SQL LIKE
  • SQL EXCEPT
  • SQL EXISTS Operator
  • SQL NOT EXISTS Operator
  • SQL INTERSECT
  • SQL IN Operator
  • SQL NOT IN Operator
  • SQL UNION
  • SQL UNION ALL
  • SQL IF ELSE
  • SQL ELSE IF
  • SQL WHILE LOOP
  • SQL Nested While Loop
  • SQL BREAK Statement
  • SQL CONTINUE Statement
  • SQL GOTO Statement
  • SQL IIF Function
  • SQL CHOOSE Function
  • SQL Change Data Capture
  • SQL Table Partitioning
  • SQL Table Partition using SSMS
  • SQL TRY CATCH
  • SQL VIEWS
  • SQL User Defined Functions
  • SQL Alter User Defined Functions
  • SQL Stored Procedure Intro
  • Useful System Stored Procedures
  • SQL SELECT Stored Procedure
  • SQL INSERT Stored Procedure
  • SQL UPDATE Stored Procedure
  • Stored Procedure Return Values
  • Stored Procedure Output Params
  • Stored Procedure Input Params
  • Insert SP result into Temp Table
  • SQL Triggers Introduction
  • SQL AFTER INSERT Triggers
  • SQL AFTER UPDATE Triggers
  • SQL AFTER DELETE Triggers
  • SQL INSTEAD OF INSERT
  • SQL INSTEAD OF UPDATE
  • SQL INSTEAD OF DELETE
  • SQL STATIC CURSOR
  • SQL DYNAMIC CURSOR
  • SQL FORWARD_ONLY Cursor
  • SQL FAST_FORWARD CURSOR
  • SQL KEYSET CURSOR
  • SQL TRANSACTIONS
  • SQL Nested Transactions
  • SQL ACID Properties
  • Create SQL Windows Login
  • Create SQL Server Login
  • SQL Server Login Error
  • Create SQL Server Roles
  • SQL Maintenance Plan
  • Backup SQL Database
  • SQL Ranking Functions Intro
  • SQL RANK Function
  • SQL PERCENT_RANK Function
  • SQL DENSE_RANK Function
  • SQL NTILE Function
  • SQL ROW_NUMBER
  • SQL Aggregate Functions
  • SQL Date Functions
  • SQL Mathematical Functions
  • SQL String Functions
  • SQL CAST Function
  • SQL TRY CAST
  • SQL CONVERT
  • SQL TRY CONVERT
  • SQL PARSE Function
  • SQL TRY_PARSE Function
  • SQL Calculate Running Total
  • SQL Find Nth Highest Salary
  • SQL Reverse String
  • SQL FOR XML PATH
  • SQL FOR XML AUTO
  • SQL FOR XML RAW

Copyright © 2021 · All Rights Reserved by Suresh

About Us | Contact Us | Privacy Policy