SUBQUERY:
(i). A query embedded with another query.
(ii). It consists of two parts
Inner Query
Outer Query
(iii). Subqueries can be placed in various parts of SQL statements such select
clause, from clause, where clause
select salary from employees where first_name='Neena';
select * from employees where salary >17000;
select * from employees where salary > (select salary from employees where
first_name='Neena');
Types:
Single Row Subquery
Multi Row Subquery
Scalar Subquery
Inline Subquery
Nested Subquery
Correlated Subquery
Multi Column Subquery
Single Row Subquery:
Relational operators
=
!=
>
>=
<
<=
select * from employees where salary > (select salary from employees where
first_name='Neena');
select * from employees where salary = (select salary from employees where
first_name='Neena');
Multi Row Subquery:
Relational operators
IN
NOT IN
ALL ------ <all, >all, >=all, <=all
ANY ------ <any, >any, >=any, <=any
select * from employees where salary > (select salary from employees where
department_id=30);
select * from employees where salary IN (select salary from employees where
department_id=30);
select * from employees where salary >all (select salary from employees where
department_id=30);
select * from employees where salary >all (select salary from employees where
department_id=30);
select * from employees where salary < all (select salary from employees where
department_id=30);
select first_name,salary from employees where salary <any (select salary from
employees where first_name='Den');
Scalar Subquery:
The Inner query will be placed on select query.
select 1+2+(select 4+3 from dual) result from dual;
select department_name from departments d where e.department_id = d.department_id
select first_name from employees e;
select first_name,department_name from employees, departments where
employees.department_id = departments.department_id--Using Joins
select first_name,(select department_name from departments d where e.department_id
= d.department_id) dept_name from employees e; --Using Subquery
INLINE SUBQUERY:
The inner query will be placed on from class.
select * from employees where department_id=60;
select first_name,employee_id,salary from (select * from employees where
department_id=60) where salary>5000;
select first_name,employee_id,salary,department_id, ROW_NUMBER()OVER(partition by
department_id
order by salary desc) rnk from employees;
select * from(select first_name,employee_id,salary,department_id,
ROW_NUMBER()OVER(partition by department_id
order by salary desc) rnk from employees) where rnk between 3 and 5;
NESTED SUBQUERY:
Inner subquery will be placed on where clause
select * from employees where salary in (select salary from employees where
department_id=30);
finding duplicates using rowid
select e.*,ROWID from emp e;
select employee_id,first_name,max(ROWID) from emp group by employee_id,first_name;
select * from emp where ROWID IN (select max(ROWID) from emp group by
first_name,last_name);
CORRELATED SUBQUERY:
There is reference created between inner query and outer query.
we could not able to execute inner query without help of outer query.
select first_name,(select department_name from departments d where e.department_id
= d.department_id) dept_name from employees e; --Using Subquery
select department_name from departments d where department_id in (select
department_id from employees e where e.department_id=d.department_id);
MULTI COLUMN SUBQUERY:
The Inner Subquery that return more than one column.
select department_id,manager_id from departments where department_id=60;
select * from employees where (department_id,manager_id) IN (select
department_id,manager_id from departments where department_id=60);
