blog

Home / DeveloperSection / Blogs / Sub Query in Sqlserver.

Sub Query in Sqlserver.

James Smith6423 06-Jul-2011

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 query in any place where expression is allowed. Generally we will use sub query in where 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.

select productID,productName,'StudentID'=(select [sid] from Student where [sid]='S0001') from Product

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

insertinto Product values((selectsidfrom Student wheresid='S0005'),'FFFF',25)
Example demonstarting subquery in update command

update Product set productName=(select sname from Student wheresid='S0004')where productID='P0005'
Example demonstarting subquery in delete command

deletefrom 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 correlated 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 wheresage >(selectavg(sage) from Student)


Updated 18-Sep-2014

Leave Comment

Comments

Liked By