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:

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:

Types of sub-query:

Sub-query is of two types.

Comparison operator is of two types:

Rules for using sub-query:

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



  Tutorials

Popular Links

Contact Us

address Plot No-741,2ND Floor
Opp. Bhagabati Temple,Jayadev Vihar
      Bhubaneswar-751013
      Email: info@silantechnology.com
      Phone: 0674-2361252
address
39877, sundale dr, apt#101, Fremont, California 94538, USA
       Phone: +1(262)388-7619

facebook twitter google linked in

© 2018 Silan Technology. All Rights Reserved