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
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 sub query.
Example demonstrating simple correlated sub queries
select * from Student s1 where sage > (select avg(sage) from Student)