SQL Subquery

The SQL subquery also called an inner query, nested query, or inner select is a query nested within another query. For example, the subquery can nest inside SELECT, INSERT, DELETE, and UPDATE statements, or inside another.

We mainly use the subquery in the WHERE Clause. That will be something like WHERE expression NOT IN, or IN subquery, etc. For this SQL Server subquery or nested query demo, we use the Employee table.

SQL Server CTE Example 0

The basic rules for writing the subquery or nested query.

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

SQL subquery Example 1

The primary query extracts or selects the required columns from the subquery (Inner or nested query).