Sub Query

Query with in another query
     
      1. Single Row sub query
      2. Multi row Sub query


select * from employees;

Want to fetch the person Who are all getting salary more than Neena's salary ?

Neena's Salary  :  17000


Select First_name, salary   
From   Employees
Where  salary > 17000;

--change in Neena's salary won't work for the above query.

Select salary From Employees
Where First_name = 'Neena';


Sub Query :

Select First_name, salary
From   Employees
Where  salary > (
                     Select salary From Employees
                     Where First_name = 'Neena'
                );



Select * from employees;

Select salary From Employees
Where First_name = 'Alexander';


Sub Query Returns one value  it is called as Single row sub query

Sub Query Returns more than one value  it is called as Multi row sub query


Single row sub query operators :

     >   <   >=  <=  =  <>

Multiple Row Sub query operators :

     IN   ALL  ANY

Who are all getting salary more than the average salary?

Select
       First_name,
       Salary
From  
       Employees
WHERE
       Salary >  (
                    SELECT Round(avg(salary),0)
                    FROM   Employees
                 );


Multiple Row Sub query  (In ANY ALL)

Select First_name,Department_id, salary
From Employees
Where salary in(
                 Select Salary From Employees
                 Where First_name = 'Alexander'
               );


Any : Minimum Value will be taken  (3100)
Select First_name,Department_id, salary
From Employees
Where salary > ANY(
                     Select Salary From Employees
                     Where First_name = 'Alexander'
                  );


All : Maximum Value will be taken (9000)
Select First_name,Department_id, salary
From Employees
Where salary > All(
                     Select Salary From Employees
                     Where First_name = 'Alexander'
                  );


We can use sub query in all the classes except Group by and order by class.

Select
From
Where
-----------Group by
having
-----------Order by


SELECT
    ( Select Count(*) from Employees Where Department_id = 90 )Executive,
    ( Select Count(*) from Employees Where Department_id = 60 ) IT,
    ( Select Count(*) from Employees Where Department_id = 10 ) Administration,
    ( Select Count(*) from Employees Where Department_id = 100) Finance
FROM
    Dual;

Select Min(salary) From Employees;

Select Min(salary) from
                        (
                          Select * from Employees
                          Where Department_id = 60
                        );

What is INLINE VIEW?
        If u use sub query in From it is call it is as INLINE VIEW.
 Intermediate result set


Correlated sub query

SELECT employee_number, name
FROM employees  Bob
WHERE salary >  (
       SELECT AVG(salary)
       FROM employees
       WHERE department = Bob.department
  );