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 WHERE NOTEXISTS(SELECT
* FROM tblCompany WHERE nID = 'emp0004'AND Name = 'E Vergis')
Example 2: SELECT sName FROMtblEmployee WHERE EXISTS(SELECT *
FROM tblCompany WHEREnID = 'emp0004'AND Name = 'E Vergis')