Friday, 13 October 2017

SUB QUERY

Sub Query :

A query embedded with in an another query is known as subquery.

Types :

  • Single Row Sub Query 
  • Multiple Row Sub Query 
  • Scalar Sub Query 
  • Inline View 
  • Correlated Sub Query 
  • Nested Sub Query 
  • Multiple Column Sub Query.

SELECT First_name, Salary FROM Employees WHERE Salary = ( SELECT MAX(Salary) From Employees); 

In the Query Sub Query(Inner Query ) is executed first. 

SELECT First_name, Salary FROM Employees WHERE Salary = ( SELECT MAX(Salary) FROM Employees) or Salary = (SELECT MIN(Salary) FROM Employees);

More than one query is possible. 
Different table is also possible.

>ALL :  Greater than ALL 

Greater than the greatest Value 
Salary>All (17000,9000)
It return 17000 greater value.

>ANY : Greater than any
  
Greater than the lowest Value.
Salary>Any (17000,9000)
It return 9000 greater value. 

SINGLE ROW SUB QUERY:

Sub Query returns only one row means it is single row sub query.
Operators used in the single row sub query. <=,<,=>,>,<>

SELECT First_name, Salary FROM Employees WHERE Salary <=( SELECT salary  FROM Employees WHERE First_name= 'Neena');

SELECT First_name, Salary FROM Employees WHERE Salary <( SELECT salary  FROM Employees WHERE First_name= 'Neena');

SELECT First_name, Salary FROM Employees WHERE Salary >=( SELECT salary  FROM Employees WHERE First_name= 'Neena');

SELECT First_name, Salary FROM Employees WHERE Salary >( SELECT salary  FROM Employees WHERE First_name= 'Neena');

SELECT First_name, Salary FROM Employees WHERE Salary <>( SELECT salary  FROM Employees WHERE First_name= 'Neena');

MULTIPLE ROW SUB QUERY:

Sub query return more than one row is multiple row sub query.
Operators used IN ALL ANY

SELECT First_name, Salary FROM Employees WHERE Salary IN  ( SELECT salary  FROM Employees WHERE First_name= 'Alexander');

SELECT First_name, Salary FROM Employees WHERE Salary <ALL( SELECT salary  FROM Employees WHERE First_name= 'Alexander');

SELECT First_name, Salary FROM Employees WHERE Salary <ANY( SELECT salary  FROM Employees WHERE First_name= 'Alexander');

SELECT First_name, Salary FROM Employees WHERE Salary >ALL( SELECT salary  FROM Employees WHERE First_name= 'Alexander');

SELECT First_name, Salary FROM Employees WHERE Salary >ANY( SELECT salary  FROM Employees WHERE First_name= 'Alexander');

SCALAR SUB QUERY

To write a sub query in SELECT Clause

SELECT 
(SELECT COUNT(*) FROM Employees WHERE Department_id = 10)"Admin",
(SELECT COUNT(*) FROM Employees WHERE Department_id = 20)"Marketing",
(SELECT COUNT(*) FROM Employees WHERE Department_id = 30)"Purchase",
(SELECT COUNT(*) FROM Employees WHERE Department_id = 40)"Networking",
(SELECT COUNT(*) FROM Employees WHERE Department_id = 50)"Support",
(SELECT COUNT(*) FROM Employees WHERE Department_id IN (10,20,30,40,50))"Total"
FROM Dual; 

SELECT 1+2+(SELECT 1+3 FROM Dual) FROM Dual;

INLINE VIEW

To write the sub query in the FROM Clause is inline view 

SELECT * FROM (SELECT * FROM Employees) Departments;

SELECT * FROM (SELECT * FROM Employees)locations;

SELECT MIN(Salary) FROM (SELECT First_name,Salary FROM Employees WHERE Department_id=50);

NESTED SUB QUERY :

Sub Query is created first
Outer Query is depend from Sub Query
Sub Query value is used in Outer Query.

SELECT First_name,Salary from employees where department_id in (SELECT department_id FROM Employees);

MULTIPLE COLUMN SUB QUERY

Relational operator used IN,ALL, ANY
Multiple Column and Multiple rows.

SELECT First_name,Salary FROM Employees where (department_id,Salary) in (SELECT Department_id,MAX(SALARY) From Employees Group By department_id);

Here Where clause contains 2 columns, subquery contains 2 columns.

CORRELATED SUB QUERY:

Correlated Sub Query means both inner query and outer Query executed in mutually dependent.

EX: Who are all getting salary more than their department average salary

SELECT   e.employee_id
       , e.first_name
   , e.Department_id
   , e.salary 
FROM employees e 
WHERE salary> (Select ROUND(AVG(salary)) FROM employees WHERE department_id=e.department_id); 

EX: Who are all getting salary more than their  manager salary 

SELECT   e.employee_id
       , e.first_name
   , e.manager_id
   , e.salary 
FROM employees e 
WHERE salary> (Select salary FROM employees WHERE employee_id=e.manager_id); 

No comments:

Post a Comment