SQL Sub Queries

A sub query is a select statement , which is embedded in the clause of another select statement. In that case the sub-query is called the inner query and the SQL statement in which the inner query is attached with is called the parent query or main query.

Where a sub query can be used ?

Suppose you want to find out those employees names who have joined after Ramesh? then what will you do ? the solution is:

• • Find out what is what is Ramesh's joining date ?
• • Then find out who has joined after the date on which Ramesh has joined ?
• • For the above two steps you need two queries. If you combine the two queries placing one query inside the other query the problem will be solved.

Syntax of a sub-query:

Select select_list
From table
Where expr operator
    ( select select_list From table ) ;

In the above syntax:

Operator includes a comparison operator such as > , = or IN.

The sub-query executes once before the main query. The result of the sub-query is used by the main query.

Use of sub-query:

• • To provide value for conditions in the where , having, from clause of the select statement.
• • To define the set of rows to be included in the target table of an insert or create table statement
• • To define a table to be operated on by a containing query. This can be done using the insert , update, delete statement.

Types of sub-query:

Sub-query is of two types.

• • Single-row sub queries- that returns only one row from the inner select statement.
• • Multiple-row sub queries- that returns more than one row from the inner select statement.
• • Multiple-column sub query that returns multiple-row and multiple-column from the inner select statement.

Comparison operator is of two types:

• • Single row operator:- which includes >, =, >=, <, <=, >>
• • Multiple-row operator :- which includes IN, ANY, ALL

Rules for using sub-query:

• • A sub-query must be enclosed in parenthesis.
• • Place the sub-query on the right side of the comparison condition.
• • The order by clause can be used in the sub-query to perform Top and analysis.
• • Use single-row operator with single-row sub queries and multiple-row operator with

Single-row sub queries :

Example:

Select emp_name
    From emp
    Where hire_date >  ( select hire_date from emp
    Where emp_name =" Ramesh" ) ;

In the above example first the inner query will get execute, which will return the hire_date of Ramesh. Then the hire_date of Ramesh will be used by the where clause of the main query to display the name of the employees whose hire_date is after the hire_date of Ramesh.

The having clause with sub-queries:

Example:

Suppose you want to display the department id and minimum salary grouped by department id where the minimum salary of each group should be more than the minimum salary of the department 50.

Select dept_id, min (emp_salary )
    From emp
    Group by dept_id
    Having min (emp_salary ) > ( select min ( emp_salary)
    From emp
    Where dept_id =50 ) ;

In the above example you saw how to use group function in a sub-query and using the sub-query with the having clause.

Note:- use single-row operator with single row sub-queries and use multiple row operator with multiple row sub-queries.

Multiple row sub-queries:

Using IN operator in multiple row sub-queries:

The IN operator equal to any value returned by the sub-query.

Suppose you want to display all the employees name who gets the salary as the minimum salary of each department.

Select emp_id, emp_name
    From emp
    Where emp_salary IN ( select min (emp_salary) 
    From emp
    Group by dept_id ) ;

Using ANY operator in multiple-row sub-query :

The ANY operator compares a value two each value returned by a sub-query:

Example:

Select emp_id, emp_name
    From emp
    Where emp_salary IN ( select min (emp_salary) 
    From emp
    Group by dept_id ) ;

In the above example it will display employees id, employees name, employees salary of those employees who are not IT programmers and whose salary is < ANY IT programmer.

Using ALL operator in multiple row sub-query:

The ALL operator compares a value two every value returned by a sub-query.

Example:

Select emp_id, emp_name, emp_salary
        From emp
        Where emp_salary < ALL ( select emp_salary from emp
        Where job_id =IT_PROG) 
        AND
        Job_Id >> IT_PROG ;

Inj the above example it will display employees id, employees name , employees salary of those employees who are not IT programmers and whose salary is < ALL IT programmer