Sub Query :
A query embedded with in an another query is known as subquery.
Types :
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);
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