A sub-query is a query embedded in another query. The subquery can be embedded in another SELECT, INSERT, UPDATE, or DELETE statement,or inside another sub query.
1.The SELECT query of a subquery is alwaysenclosed in parentheses
2.View created by using a subquery cannot beupdated.
3.The ntext, text, and image data types cannot beused in the select list of sub queries
4.If a table appears only in a subquery and not inthe outer query, then columns from that table cannot be included in the output
Example related to Subqueries
·Subquerieswith IN and NOT IN:
Example 1:SELECT city FROMtblCity WHERE ID NOTIN(SELECT CityID FROMtblState WHERE StateId ='25')
Example 2:SELECT city FROMtblCity WHERE ID IN(SELECT CityID FROM tblState WHEREStateId ='25')
·Subquerieswith comparison operators.
Comparison operators can be used (like <, >, =,!> etc). Sub queries used with comparison operators must return a singlevalue rather than a list to avoid error. Hence the nature of the database mustbe knows before executing such sub queries.
Example :SELECT sName FROMtblEmployee WHERE nSalary >'5000'
·Subquerieswith Exists and NOT Exists.
A subquery with Exist does not really return any data; itreturns TRUE or FALSE.
Example 1:SELECT sName FROMtblEmployee WHERENOTEXISTS(SELECT*FROM tblCompany WHERE nID ='emp0004'ANDName='E Vergis')
Example 2:SELECT sName FROMtblEmployee WHEREEXISTS(SELECT*FROM tblCompany WHEREnID ='emp0004'ANDName='E Vergis')