Oracle Sub Queries

Query within another query is called sub query or nested query. Sub queries are used to retrieve the data from single or multiple tables based on more than one step process.

Inner Query: A query which provides values to another query is called Inner query or child query. Oracle having maximum 255 inner queries.

Outer query: A query which received values from other query is called out query or parent query.

Sub queries are of two types:
1. NON CO-RELATED:
2. CO-RELATED:

NON CO-RELATED Queries:
In non co-related sub queries, sub query executed first, then only parent query is executed. These are divided into following categories.
a. Single row queries: In single row queries, child query always returns single value.

Example:

SELECT*FROM emp
WHERE sal<(SELECT AVG(sal) FROM emp);

SELECT ename, dname FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno=(SELECT deptno FROM emp
WHERE dname=’SALES’);

Query: Display lowest average salary job from employee table.

SELECT job, AVG(sal) FROM emp group by job
HAVING avg(sal)=(SELECT MIN(AVG(sal)) FROM emp group by job);

b.Multiple row query: In multiple row queries, child query always returns multiple values.
Query: Display employee detals who are getting maximum salary in each department from employee table.

SELECT * FROM emp
WHERE sal=(SELECT MAX(sal) FROM emp GROUP BY deptno);

c.Multiple Column Sub-query:
We can also compare multiple columns of the child query with multiple columns of the parent query.
Syntax:

SELECT * FROM table_name
WHERE (COLUMN1, COLUMN2, ….) IN(SELECT COLUMN1, COLUMN2, FROM table WHERE condition);

Example:
Display employees who are getting maximum salary in each department from employee table.

SELECT deptno, sal, ename FROM emp
WHERE (deptno,sal) IN(SELECT MAX(sal) FROM emp group by deptno);

Co-Related Sub Queries:

  • In these queries, parent query executes first then child query will be executed.
  • In Non-corelated queries, child query executed only once per query. Where as co-related queries child queries executed per each row for parent query table.
  • In co-related queries, we must create alias name for the parent query table in parent query and also pass that alias name into child query where clause.

Example:
Display the highest salary employee.

SELECT * FROM emp e1
WHERE 1=(SELECT COUNT(*) FROM emp e2
WHERE e2.sal>=e1.sal);

Display second highest salary employee details.

SELECT * FROM emp e1
WHERE 2=(SELECT COUNT(*) FROM emp e2
WHERE e2.sal>=e1.sal);

Powered by k2schools