Relational Query Languages |NESTED QUERIES OR SUB-QUERY

NESTED QUERIES OR SUB-QUERY

DEFINITION

• A sub query or nested queries is a query that appears within another SQL command. SELECT, DELETE, and UPDATE all support sub queries, though the rules and reasons for using them vary. 
• A sub query is a complete query, but cannot specify a destination using TO or INTO. Sub queries are enclosed in parentheses in the containing query. Sub queries can appear in the WHERE clause of SELECT, UPDATE and DELETE.
• sub queries can also be used in the field list of SELECT, in the SET clause of UPDATE, and in the FROM clause of SELECT, UPDATE and DELETE.

TYPES OF NESTED QUERIES OR SUB QUERY

There are three types of sub-Queries or nested queries

1. Single row sub – query.

2. Multiple row sub- queries.

3. Multiple column sub- queries.

1. Single row sub – query-  Queries that return a single value or row from the inner select statement.
Syntax: select colname from tname where  colname=(another query);
Example: Select losal  from salgrade where grade=(select grade from salgrade where losal=700);



2. Multiple row sub-query- Queries that return more than one rows from the inner select statement.
Syntax: select colname1 , colname2 , colname3 from tname where colname in (another query);
Example: select grade , losal , hisal from salgrade where grade IN(select min (grade) from salgrade);



3. Multiple column sub query: Queries that return more than one columns from the inner select statement.