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.

Properties:-

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')