Oracle SQL – Noncorrelated Subquery

Last Updated on :September 29, 2019

In Noncorrelated subquery, inner query does not depend on outer query.

Noncorrelated subquery can be categorized as follows

• Single row subquery / Scalar Subquery
• Multiple row subquery
• Multiple column subquery

1. Single row subquery / Scalar Subquery

A single-row subquery returns a single row with single column. This is also called scalar subquery.
Operator (=, <, >, !=, <=, >=, or <>) can be used for comparison with the single-row subquery.

Example

The following query illustrates how to show employee details earning lowest salary using single row subquery. The subquery is added in the WHERE clause of the outer query.

The inner query will execute first and will determine minimum salary from employee table, the result is fed to outer query which retrieve all employees those earning lowest salary.

SELECT *
FROM emp
WHERE sal = (SELECT MIN (sal) FROM emp);

Using comparison operators in Single Row subqueries

Below given query will show all employees details earning more average salary. In this case you have to use ‘>’ operator for comparison against the subquery.

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

Output

EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7839,"KING","PRESIDENT",,11/17/1981,5000,,10
7698,"BLAKE","MANAGER",7839,5/1/1981,2850,,30
7782,"CLARK","MANAGER",7839,6/9/1981,2450,,10
7566,"JONES","MANAGER",7839,4/2/1981,2975,,20
7788,"SCOTT","ANALYST",7566,12/9/1982,3000,,20
7902,"FORD","ANALYST",7566,12/3/1981,3000,,20

2. Multiple row subquery

When subquery returns multiple row , it will not allow you to use comparison Operator (=, <, >, !=, <=, >=, or <>) , oracle will return error message since a single value cannot be directly compared with a set of values. To accomplish this, IN,ALL,ANY or SOME operator must be used.

Example – Using IN operator with Subquery

The IN operator compares the columns value with set of values returned by the subquery. If match is found in the set of values returned by the subquery then the record is included in the result set.

The following query illustrates how to show customer details who has placed order after 01-Apr-2015. The subquery returns the list of customer_id from orders table those were placed after 01-Apr-2015.

The IN operator compares list of customer_id returned by the subquery and retrieves customer name from customers table if matching found.

SELECT customer_id,cust_first_name,cust_last_name
FROM customers
WHERE customer_id IN (SELECT customer_id
FROM orders
WHERE order_timestamp > DATE '2015-04-01');

You can also use NOT IN operator and include record in result set those are not in subquery result set.
The NOT IN operator fetches customer name those are not in the list returned by the subquery.

SELECT customer_id, cust_first_name, cust_last_name
FROM customers
WHERE customer_id NOT IN (SELECT customer_id
FROM orders
WHERE order_timestamp > DATE '2015-04-01');

Example – Using ANY and ALL Operator with Subquery

The ANY and ALL operators are used with comparison operator.
The ANY operator returns the rows when any value returned by the subquery makes the condition true.
The ALL operator returns the rows when all values returned by the subquery makes the condition true.

In following query the subquery returns set of salaries from EMP table for DEPTNO 10.The outer query check and retrieves employee details when his/her salary greater than or equal to any salary returned by the subquery.

SELECT EMPNO, ENAME
FROM EMP
WHERE SAL >= ANY (SELECT SAL
FROM EMP
WHERE DEPTNO = 10);

You can use ALL operators when you want to compare with all values returned by the subquery. In following example the outer query check and retrieves employee details when his / her salary greater than or equal to all salary returned by the subquery

SELECT EMPNO, ENAME
FROM EMP
WHERE SAL >= ALL (SELECT SAL
FROM EMP
WHERE DEPTNO = 10);

3. Multiple-column subquery

You can write subquery that return multiple columns. In following example the subquery returns sal and deptno for empno 7900 from emp table. The result set is fed to outer query which retrieve employee no and employee name when match is found.

SELECT empno, ename
FROM emp
WHERE (sal, deptno) = (SELECT sal, deptno
FROM emp
WHERE empno = 7900);