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)
Anonymous User
18-Mar-2019Thank You for the informative post.
Kenny Tangnde
08-Sep-2011hi James Smith
thank for you partake.