SQL subquery

A subquery also called as 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 subqueries in WHERE Clause, and it is something like: WHERE expression NOT IN subquery, or IN subquery etc.

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

• 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 subqueires up to 32 levels. It may vary too

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

How to write a subquery in SQL 

In this simple example we will show you, How to write a SQL subquery.

SQL CODE

OUTPUT of the subquery

The inner query (subquery) will return all the records from Employee Table, whose sales amount is greater than 500

OUTPUT of Main Query

Main query will extract, or select the required columns from subquery (Inner query)

SQL subquery in Select Statement 

Microsoft SQL Server 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 single value for each record returned by the main query.

SQL CODE

OUTPUT

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 subquery in Where Clause.

SQL CODE

OUTPUT

First SQL Server will execute inner query (subquery), and within the subquery we are finding the Average of Sales amount. Next, 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 in From Clause 

A subquery in SQL Server can also be used in FROM clause, and this can help us to return multiple records from subquery. In this example we will show you, How to write a SQL subquery in FROM Clause.

SQL CODE

OUTPUT

First SQL Server will execute subquery, and the subquery 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 to refer SQL GROUP BY Clause article to understand the grouping concept.

SQL subquery in Case Statement 

In this example we will show you, How to write a SQL subquery in Case statement.

SQL CODE

OUTPUT

First SQL Server will execute subquery, and the subquery will find the Average of Sales amount. 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 return ‘He is Performing Good’ otherwise, ‘He is Under Performing’ as the output for Remarks Column. Now 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 SQL SPACE Function to return two empty spaces in between the First name and Last name.

SQL Correlated subquery 

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.

SQL CODE

OUTPUT

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 need Employee Id in Where Clause.

OK, let me run the subquery independently.

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

OUTPUT

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 SQL subquery in Update Statement to update the records present in the Employee Table.

SQL CODE

Above query 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 in Delete Statement 

We can also use the subquery in the DELETE Statement. In this example we will show you, How to write a SQL subquery in delete Statement to delete the rows from Employee Table.

SQL CODE

Above 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 in Insert Statement 

The SQL Server subquery can also be used in the INSERT INTO SELECT Statement. In this example we will show you, How to write a SQL subquery in Insert Statement to insert new records into the Employee Table.

SQL CODE

OUTPUT

Thank You for Visiting Our Blog