Sub Query in Sql Server
A sub query 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
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 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
Example demonstrating simple correlated sub queries
select * from Student s1 where sage > (select avg(sage) from