Sub Queries

Sub Queries Or Nested Queries

A query within another query is called a sub query. We can define any number of sub queries with in a query. But the system executes the inner most query first. If we are using relational operators between the queries then the sub query must return a single value.

The Following Points should be kept in Mind While using SubQueries

1. The inner Query must be enclosed in parentheses
2. The Inner query must be on the right hand side of the condition
3. The sub query may not have an order by clause
4. The Order By clause appears at the end of the main select statement
5. Sub queries are always executed from the most deeply nested to the least deeply nested

Note : If we use operators “in” and “not in” then the sub query followed by the operators can return more than one value.

Example Queries

1.List the employee’s Details who belong to the department of “Ward”

In general, first we have to know; what the department of “Ward” and then we can display all the employee’s details belonging to “Ward”

select * from emp where ename='WARD';

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- --------- --------- ------------ --------- --------- ---------

7521 WARD salesman 7698 22-FEB-81 1250 500 30

• select * from emp where deptno=30;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- -------------- ----------- ------------- --------- --------- ---------

7499 ALLEN salesman 7698 20-FEB-81 1600 300 30

7521 WARD salesman 7698 22-FEB-81 1250 500 30

7654 MARTIN salesman 7698 28-SEP-81 1250 1400 30

7698 BLAKE manager 7839 01-MAY-81 2850 30

7844 TURNER salesman 7698 08-SEP-81 1500 0 30

Using Sub Queries,

• select * from emp where deptno=(select deptno from emp where ename='WARD');

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- -------------- ----------- ------------- --------- --------- ---------

7499 ALLEN salesman 7698 20-FEB-81 1600 300 30

7521 WARD salesman 7698 22-FEB-81 1250 500 30

7654 MARTIN salesman 7698 28-SEP-81 1250 1400 30

7698 BLAKE manager 7839 01-MAY-81 2850 30

7844 TURNER salesman 7698 08-SEP-81 1500 0 30

2.List the employee’s details whose salary is greater than the average salary of all the employees

In general, first we have to know; average sal of all employee’s and then we can display all the employee’s details whose salary is > average salary of all employees

• select avg(sal) from emp;

AVG(SAL)

---------

2161.3636

• select * from emp where sal>=2161;

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- ------------- --------- ------------- --------- --------- -----------

7566 JONES Manager 7839 02-APR-81 2975 20

7698 BLAKE Manager 7839 01-MAY-81 2850 30

7782 CLARK Manager 7839 09-JUN-81 2450 10

7788 SCOTT Analyst 7566 19-APR-87 3000 20

7839 KING President 17-NOV-81 5000 10

Using Sub Queries,

• select * from emp where sal>(select avg(sal) from emp);

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

--------- ---------- ------------- --------- ------------- --------- --------- -----------

7566 JONES Manager 7839 02-APR-81 2975 20

7698 BLAKE Manager 7839 01-MAY-81 2850 30

7782 CLARK Manager 7839 09-JUN-81 2450 10

7788 Mahesh Analyst 7566 14-FEB-78 3000 20

7839 KING President 17-NOV-81 5000 10

3.Write a Query to find the details of the department whose manager’s empcode ‘7698’

In general, first we have to know; department number whose manager empcode is “7698” and then we can display all the department details of that employee

• select * from dept;

DEPTNO DNAME LOC

--------- -------------- -----------------

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

• select deptno from emp where mgr='7698'

DEPTNO

---------

30

30

30

30

Using sub Query,

select * from dept where deptno=(select deptno from emp where mgr='7698')

ERROR at line 1:

ORA-01427: single-row subquery returns more than one row

• select * from dept where deptno in (select deptno from emp where mgr='7698')

DEPTNO DNAME LOC

--------- -------------- -------------

30 SALES CHICAGO

or you can give, like this

• select * from dept where deptno=(select distinct deptno from emp where mgr='7698');

DEPTNO DNAME LOC

--------- -------------- -------------

30 SALES CHICAGO

4.List the details of all the employee’s who earn lowest salary in each department

• select * from emp where sal in(select min(sal) from emp group by deptno)

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO

-------- ---------- --------- --------- ------------- --------- --------- ---------

7369 SMITH Clerk 7902 17-DEC-80 800 20

7521 WARD Salesman 7698 22-FEB-81 1250 500 30

7654 MARTIN Salesman 7698 28-SEP-81 1250 1400 30

7782 CLARK Manager 7839 09-JUN-81 2450 10

Co related Sub query:

A sub query is executed once for the parent statement whereas the correlated sub query is executed once for each row of the parent query.

Example:

Find all employees who earn more than the average salary in their department.

SELECT last-named, salary, department_id FROM employees A WHERE salary > (SELECT AVG (salary) FROM employees B WHERE B.department_id =A.department_id Group by B.department_id)