---
title: "Sub Query in Sqlserver."  
description: "A subquery defined as a select query that returns a single value and is nested inside a select, insert, delete or update command. We will use sub quer"  
author: "James Smith"  
published: 2011-07-06  
updated: 2014-09-18  
canonical: https://www.mindstick.com/blog/202/sub-query-in-sqlserver  
category: "database"  
tags: ["database"]  
reading_time: 2 minutes  

---

# Sub Query in Sqlserver.

A [subquery](https://www.mindstick.com/forum/1279/using-alias-in-subquery) defined as a [select query](https://www.mindstick.com/articles/1858/sqlite-select-query) that returns a single value and is nested inside a select, insert, delete or update [command](https://www.mindstick.com/blog/178/synchronous-and-asynchronous-command-execution-in-c-sharp-dot-net). We will use sub query in any place where [expression](https://www.mindstick.com/articles/1861/sqlite-expressions) is allowed. Generally we will use sub query in [where clause](https://www.mindstick.com/interview/1909/when-do-you-use-where-clause-and-when-do-you-use-having-clause).\

Here I will give you an example in which a sub query is used as a column expression named StudentID in a [select statement](https://www.mindstick.com/forum/160076/explain-the-sql-select-statement-and-how-it-s-used-to-search-for-data-in-a-database).

select productID,productName,'StudentID'=(select [sid] from [Student](https://www.mindstick.com/articles/157138/student-loan-default-wage-garnishment-and-student-loan) where [sid]='S0001') from [Product](https://www.mindstick.com/articles/75385/full-product-keys)

A subquery is also called inner query or inner select, while the statement containing a subquery is also called an outer query or outer select.

##### Example demonstarting subquery in insert command

\

```
insert into Product values((select sid from Student where sid='S0005'),'FFFF',25)
```

##### Example demonstarting subquery in update command

\

```
update Product set productName=(select sname from Student where sid='S0004') where productID='P0005'
```

##### Example demonstarting subquery in delete command

\

```
delete from Product where productID in (select productID from Product where productPrice > 23)
```

##### Correlated Sub Queries

\

Such type of sub query in which is executed rapidly, once for each row that might be selected by the outer query is [known as](https://answers.mindstick.com/qa/35703/ricky-ponting-is-also-known-as-what) [correlated](https://www.mindstick.com/interview/624/what-are-correlated-subqueries) sub query. In other words we can define, when Sql may need to re-evaluated as it examines each new row or group of rows in the outer level select. This is called a correlated sub query.

##### Example demonstrating simple correlated sub queries

\

```
select * from Student s1 where sage > (select avg(sage) from Student)
```

---

Original Source: https://www.mindstick.com/blog/202/sub-query-in-sqlserver

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.