subquery in oracle sql.

A subquery is a SELECT statement nested in various clauses of a SQL statement. It allows you to use the output from one query as
the input of another SQL statement.
Subqueries make it easy to break down problems intoical and manageable pieces.
In most database systems, subqueries are typically part of the WHERE clause, as follows:
=>WHERE column IN (subquery
=> WHERE column <comparison> (subquery)
=>WHERE EXISTS (subquery)
Write Subqueries in the WHERE and HAVING Clauses
WHERE clause
Most often, the subquery will be found in the WHERE clause. These subqueries are also called nested subqueries.
Limitations
Oracle allows up to 255 levels of subqueries in the WHERE clause.
Write Subqueries That Return Multiple Rows
Write Subqueries That Return Multiple Columns
Subqueries are sometimes also referred to as sub-SELECTs or nested SELECTs
Subqueries are not used just in SELECT statements but also in other SQL statements that allow subqueries (for example, the WHERE
clause of DELETE statements,
SET and WHEREauses of UPDATE state., or part of the SELECT clause of INSERT statements). You use these SQL statement the
Insert, Update, and Delete.”
a subquery is referred to as an inner query, and the surrounding statement is known as the outer query
In a simple subquery, the inner query is executed once before the execution of the outer query. (This is in contrast to the
correlatedubquery, where the inner query executes repeatedly)
Scalar Subqueries or single-row subquery
A scalar subquery, also called a single-row subquery, returns a single column with one row.
Instead of performing equality conditions, you might need to construct >, <, >=, <=, or <> comparisons against a result. Just like the
aforementioned statement, these comparisons will work only if the subquery returns a single row.
Scalar Subqueries
scalar subqueries allow you to treat the output of a subquery as a column or even an expression within a SELECT statement. It is a
query that only selects one column or expression and returns just one row. If the scalar subquery fails to return select any rows, Oracle
will use a NULL value for the output of the scalar subquery.
SELECT d.deptno, d.dname, (SELECT count(*) FROM emp e
WHERE e.deptno = d.deptno) AS "Num Dept"
FROM dept d;
DEPTNO DNAME Num Dept
---------- -------------- ----------
10 ACCOUNTING 3
20 RESEARCH 5
30 SALES 6
40 OPERATIONS 0
Scalar Subquery Expressions—

You have already learned about the scalar subquery, which is a query that returns a single-column, single-row value. You can use a
scalar subquery expression in most syntax
that calls for an expression. The next examples show you how to use this functionality in the SELECT list, in the WHERE clause, in the
ORDER BY clause of a query, in a
CASE expression, or as part of a function cal
Scalar Subquery Expression in the SELECT Clause
Scalar Subquery Expression in the WHERE Clause
Scalar Subquery Expression in the ORDER BY Clause
Scalar Subquery Expression and the CASE Expression
Errors in Scalar Subquery Expressions
the scalar subquery expression must always return one row and one column. Otherwise, Oracle returns error ORA-01427: single-row
subquery
returns more than one row. If you list multiple columns, you receive error ORA-00913: “too many values.” If your subquery does not
return any row, a null value is returned
Example 1 of Scalar subquery
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Sal > (SELECT Sal
4 FROM Emp
5 WHERE Empno = 7566);
ENAME SAL JOB
---------- ----- ---------
KING 5000 PRESIDENT
FORD 3000 ANALYST
SCOTT 3000 ANALYST
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT Sal
2 FROM Emp
3 WHERE Empno = 7566;
SAL
-----
2975
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1 Card=1 Bytes=8)
1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 Car
d=1 Bytes=8)
2 1 INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQUE)
) (Cost=0 Card=1)
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Sal > 2975;
ENAME SAL JOB
---------- ----- ---------
KING 5000 PRESIDENT
FORD 3000 ANALYST
SCOTT 3000 ANALYST
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=7 Bytes=126
)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=7 Bytes=
126)
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Sal > (SELECT Sal
4 FROM Emp
5 WHERE Empno = 7566);
ENAME SAL JOB
---------- ----- ---------
KING 5000 PRESIDENT
FORD 3000 ANALYST
SCOTT 3000 ANALYST
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=18)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
18)
2 1 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=1 C
ard=1 Bytes=8)
3 2 INDEX (UNIQUE SCAN) OF 'EMP_PRIMARY_KEY' (INDEX (UNIQU
E)) (Cost=0 Card=1)
SQL> cl scr
SQL> SELECT Job
2 FROM Emp
3 WHERE Ename = 'SMITH';
JOB
---------
CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=14)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
14)
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 W HERE Job = 'CLERK';
ENAME SAL JOB
---------- ----- ---------
JAMES 950 CLERK
SMITH 800 CLERK
ADAMS 1100 CLERK
MILLER 1300 CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=3 Bytes=54)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Bytes=
54)
SQL> SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Job = (SELECT Job
4 FROM Emp
5 WHERE Ename = 'SMITH')
6 ORDER BY Sal;
ENAME SAL JOB
---------- ----- ---------
SMITH 800 CLERK
JAMES 950 CLERK
ADAMS 1100 CLERK
MILLER 1300 CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=54)
1 0 SORT (ORDER BY) (Cost=7 Card=3 Bytes=54)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Byte
s=54)
3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 By
tes=14)
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
)
2 FROM Emp
3 WHERE Job = (SELECT Job
4 FROM Emp
5 WHERE Ename = 'SMITH' AND
6
Ename <> 'SMITH'
7
8* ORDER BY Sal
SQL> /
no rows selected
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=54)
1 0 SORT (ORDER BY) (Cost=7 Card=3 Bytes=54)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Byte
s=54)
3 2 FILTER
4 3 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1
Bytes=14)
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Job
2 FROM Emp
3 WHERE Job = (SELECT Job
4 FROM Emp
5 WHERE Ename = 'SMITH'
6
7 Ename <> 'SMITH'
8* ORDER BY Sal
SQL> /
ENAME SAL JOB
---------- ----- ---------
JAMES 950 CLERK
ADAMS 1100 CLERK
MILLER 1300 CLERK
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=3 Bytes=54)
1 0 SORT (ORDER BY) (Cost=7 Card=3 Bytes=54)
2 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=3 Byte
s=54)
3 2 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 By
tes=14)
SQL> SPOOL OFF
SQL> cl scr
) AND
SQL> SELECT Hiredate
2 FROM Emp
3 WHERE Ename = 'TURNER';
HIREDATE
---------
08-SEP-81
SQL> SELECT Empno, Ename, Hiredate, Sal
2 FROM
Emp
3 WHERE Hiredate > (SELECT Hiredate
4 FROM Emp
5 WHERE Ename = 'TURNER')
6 ORDER BY Sal;
EMPNO ENAME HIREDATE SAL
---------- ---------- --------- ----------
7900 JAMES 03-DEC-81 950
7876 ADAMS 12-JAN-83 1100
7654 MARTIN 28-SEP-81 1250
7934 MILLER 23-JAN-82 1300
7902 FORD 03-DEC-81 3000
7788 SCOTT 09-DEC-82 3000
7839 KING 17-NOV-81 5000
7 rows selected.
SQL
>
ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Hiredate, Sal
2 FROM
Emp
3 WHERE Hiredate < (SELECT Hiredate
4 FROM Emp
5 WHERE Ename = 'TURNER')
6* ORDER BY Sal
S
QL
>
/
EMPNO ENAME HIREDATE SAL
---------- ---------- --------- ----------
7369 SMITH 17-DEC-80 800
7521 WARD 22-FEB-81 1250
7499 ALLEN 20-FEB-81 1600
7782 CLARK 09-JUN-81 2450
7698 BLAKE 01-MAY-81 2850
7566 JONES 02-APR-81 2975
6 rows selected.
SQL> cl
scr
S
QL
>
R
1 SELECT Empno, Ename, Hiredate, Sal
2 FROM Emp
3 WHERE Hiredate < (SELECT Hiredate
4 FROM Emp
5
WHERE Ename = 'TURNER')
6* ORDER BY Sal
EMPNO ENAME HIREDATE SAL
---------- ---------- --------- ----------
7369 SMITH 17-DEC-80 800
7521 WARD 22-FEB-81 1250
7499 ALLEN 20-FEB-81 1600
7782 CLARK 09-JUN-81 2450
7698 BLAKE 01-MAY-81 2850
7566 JONES 02-APR-81 2975
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Hiredate, Sal
2 FROM Emp
3 WHERE Hiredate = (SELECT Hiredate
4 FROM Emp
5 WHERE Ename = 'FORD')
6* ORDER BY Sal
SQL> /
EMPNO ENAME HIREDATE SAL
---------- ---------- --------- ----------
7900 JAMES 03-DEC-81 950
7902 FORD 03-DEC-81 3000
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Hiredate, Sal
2 FROM Emp
3 WHERE Hiredate = (SELECT Hiredate
4 FROM Emp
5 WHERE Ename = 'FORD') AND
6 Ename <> 'FORD'
7* ORDER BY Sal
SQL> /
EMPNO ENAME HIREDATE SAL
---------- ---------- --------- ----------
7900 JAMES 03-DEC-81 950
SQL> cl scr
SQL> SELECT Empno, Ename, Sal, Job
2 FROM Emp
3 WHERE Deptno = (SELECT Deptno
4 FROM Dept
5 WHERE Dname = 'SALES');
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7698 BLAKE 2850 MANAGER
7654 MARTIN 1250 SALESMAN
7499 ALLEN 1600 SALESMAN
7844 TURNER 1500 SALESMAN
7900 JAMES 950 CLERK
7521 WARD 1250 SALESMAN
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Sal, Job
2 FROM Emp, Dept
3 WHERE Emp.Deptno = Dept.Deptno AND
4* Dept.Dname = 'SALES'
SQL> /
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7698 BLAKE 2850 MANAGER
7654 MARTIN 1250 SALESMAN
7499 ALLEN 1600 SALESMAN
7844 TURNER 1500 SALESMAN
7900 JAMES 950 CLERK
7521 WARD 1250 SALESMAN
6 rows selected.
SQL> SET AUTOTRACE ON EXPLAIN
SQL> SELECT Empno, Ename, Sal, Job
2 FROM Emp, Dept
3 WHERE Emp.Deptno = Dept.Deptno AND
4 Dept.Dname = 'SALES';
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7698 BLAKE 2850 MANAGER
7654 MARTIN 1250 SALESMAN
7499 ALLEN 1600 SALESMAN
7844 TURNER 1500 SALESMAN
7900 JAMES 950 CLERK
7521 WARD 1250 SALESMAN
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=7 Card=5 Bytes=370
)
1 0 HASH JOIN (Cost=7 Card=5 Bytes=370)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt
es=22)
3 1 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=14 Byt
es=728)
SQL> SELECT Empno, Ename, Sal, Job
2 FROM Emp
3 WHERE Deptno = (SELECT Deptno
4 FROM Dept
5 WHERE Dname = 'SALES');
EMPNO ENAME SAL JOB
---------- ---------- ---------- ---------
7698 BLAKE 2850 MANAGER
7654 MARTIN 1250 SALESMAN
7499 ALLEN 1600 SALESMAN
7844 TURNER 1500 SALESMAN
7900 JAMES 950 CLERK
7521 WARD 1250 SALESMAN
6 rows selected.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=52)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
52)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt
es=22)
SQL> SELECT Empno, Ename, Sal, Comm, Sal + NVL( Comm, 0 ) TotSal
2 FROM Emp
3 WHERE Deptno = (SELECT Deptno
4 FROM Dept
5 WHERE Loc = 'DALLAS');
EMPNO ENAME SAL COMM TOTSAL
---------- ---------- ---------- ---------- ----------
7566 JONES 2975 2975
7902 FORD 3000 3000
7369 SMITH 800 800
7788 SCOTT 3000 3000
7876 ADAMS 1100 1100
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=6 Card=1 Bytes=59)
1 0 TABLE ACCESS (FULL) OF 'EMP' (TABLE) (Cost=3 Card=1 Bytes=
59)
2 1 TABLE ACCESS (FULL) OF 'DEPT' (TABLE) (Cost=3 Card=1 Byt
es=21)
SQL> cl scr
SQL> SET AUTOTRACE OFF EXPLAIN
SQL> cl scr
SQL> SELECT MAX(Sal) FROM Emp;
MAX(SAL)
----------
5000

1 SELECT
2 Ename,
3 MAX(Sal)
4 FROM Emp
5* GROUP BY Ename
SQL> /
ENAME MAX(SAL)
---------- ----------
ADAMS 1100
ALLEN 1600
BLAKE 2850
CLARK 2450
FORD 3000
JAMES 950
JONES 2975
KING 5000
MARTIN 1250
MILLER 1300
SCOTT 3000
ENAME MAX(SAL)
---------- ----------
SMITH 800
TURNER 1500
WARD 1250
14 rows selected.
SQL> SELECT MAX(Sal) FROM Emp;
MAX(SAL)
----------
5000
SQL> SELECT Ename, Sal, Deptno, Job
2 FROM Emp
3 WHERE Sal = 5000;
ENAME SAL DEPTNO JOB
---------- ---------- ---------- ---------
KING 5000 10 PRESIDENT
SQL> SELECT Ename, Job , Sal
2 FROM Emp
3 WHERE Sal = (SELECT MAX(Sal)
4 FROM Emp);
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
SQL> cl scr
SQL> SELECT Ename, Job, Sal
2 FROM Emp
3 WHERE Sal = (SELECT MIN(Sal)
4 FROM Emp);
ENAME JOB SAL
---------- --------- ----------
SMITH CLERK 800
SQL> SELECT Ename, Job, Sal
2 FROM Emp
3 WHERE Sal > (SELECT AVG(Sal)
4 FROM Emp);
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
FORD ANALYST 3000
SCOTT ANALYST 3000
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Sal
2 FROM Emp
3 WHERE Sal < (SELECT AVG(Sal)
4* FROM Emp)
SQL> /
ENAME JOB SAL
---------- --------- ----------
MARTIN SALESMAN 1250
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
JAMES CLERK 950
WARD SALESMAN 1250
SMITH CLERK 800
ADAMS CLERK 1100
MILLER CLERK 1300
8 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Sal
2 FROM Emp
3 WHERE Sal = (SELECT AVG(Sal)
4* FROM Emp)
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Job, Sal
2 FROM Emp
3 WHERE Sal <> (SELECT AVG(Sal)
4* FROM Emp)
SQL> /
ENAME JOB SAL
---------- --------- ----------
KING PRESIDENT 5000
BLAKE MANAGER 2850
CLARK MANAGER 2450
JONES MANAGER 2975
MARTIN SALESMAN 1250
ALLEN SALESMAN 1600
TURNER SALESMAN 1500
JAMES CLERK 950
WARD SALESMAN 1250
FORD ANALYST 3000
SMITH CLERK 800
ENAME JOB SAL
---------- --------- ----------
SCOTT ANALYST 3000
ADAMS CLERK 1100
MILLER CLERK 1300
14 rows selected.
SQL> cl scr
SQL> SELECT Ename, Deptno, Sal, Job
2 FROM Emp
3 WHERE Sal = (
4 SELECT MAX(Sal)
5 FROM Emp
6 WHERE Deptno = 10
7
ENAME DEPTNO SAL JOB
---------- ---------- ---------- ---------
KING 10 5000 PRESIDENT
SQL> cl scr
SQL> SELECT
2 MIN(Sal)
3 FROM Emp
4 WHERE Deptno = 20;
MIN(SAL)
----------
800
SQL> SELECT Deptno, MIN(Sal)
2 FROM Emp
3 GROUP BY Deptno
4 HAVING MIN(Sal) > (SELECT
5 MIN(Sal)
6 FROM Emp
7 WHERE Deptno = 20);
DEPTNO MIN(SAL)
---------- ----------
10 1300
30 950
SQL> cl scr
SQL> SELECT AVG(Sal)
2 FROM Emp
3 GROUP BY Job;
AVG(SAL)
----------
3000
1037.5
2758.33333
5000
1400
SQL> ED
Wrote file afiedt.buf
1 SELECT MAX(AVG(Sal))
);
2 FROM Emp
3* GROUP BY Job
SQL> /
MAX(AVG(SAL))
-------------
5000

SQL> SELECT Job, AVG(Sal)
2 FROM Emp
3 GROUP BY Job
4 HAVING AVG(Sal) = (SELECT
5 MIN(AVG(Sal))
6 FROM Emp
7 GROUP BY Job);
JOB AVG(SAL)
--------- ----------
CLERK 1037.5
SQL> ED
Wrote file afiedt.buf
1 SELECT Job, AVG(Sal)
2 FROM Emp
3 GROUP BY Job
4 HAVING AVG(Sal) = (SELECT
5 MAX(AVG(Sal))
6 FROM Emp
7* GROUP BY Job)
SQL> /
JOB AVG(SAL)
--------- ----------
PRESIDENT 5000
SQL> cl scr
SQL> COLUMN Empno FORMAT 9999
SQL> COLUMN MGR FORMAT 9999
SQL> COLUMN Sal FORMAT 9999
SQL> SELECT *
2 FROM Emp
3 WHERE
4 Sal BETWEEN
5
6 SELECT Sal
7 FROM Emp
8 WHERE Ename = 'MILLER'
9
10
11 SELECT Sal
12 FROM Emp
13 WHERE Ename = 'JONES'
14
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE
4 Sal BETWEEN
5
6 SELECT Sal
7 FROM Emp
8 WHERE Ename = 'MILLER'
9
10
11 SELECT Sal
12 FROM Emp
13 WHERE Ename = 'JONES'
14
15* Ename NOT IN('MILLER', 'JONES')
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE
4 Sal >= (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'MILLER'
8 ) AND
9 Sal <= (
10 SELECT Sal
11 FROM Emp
12 WHERE Ename = 'JONES'
13 ) AND
14* Ename NOT IN('MILLER', 'JONES')
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE
(
) AND
(
) AND
4 Sal >= (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'MILLER'
8 ) AND
9 Sal <= (
10 SELECT Sal
11 FROM Emp
12 WHERE Ename = 'JONES'
13 ) AND
14 Ename NOT IN('MILLER', 'JONES') AND
15 Deptno = (
16 SELECT Deptno
17 FROM Dept
18 WHERE Dname = 'SALES'
19* )
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE
4 Sal >= (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'MILLER'
8 ) AND
9 Sal <= (
10 SELECT Sal
11 FROM Emp
12 WHERE Ename = 'JONES'
13 ) AND
14 Ename NOT IN('MILLER', 'JONES') AND
15 Deptno = (
16 SELECT Deptno
17 FROM Dept
18 WHERE Dname = 'SALES'
19 ) AND
20 Job = (
21 SELECT Job
22 FROM Emp
23 WHERE Ename = 'ALLEN'
24* )
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE
4 Sal >= (
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'MILLER'
8 ) AND
9 Sal <= (
10 SELECT Sal
11 FROM Emp
12 WHERE Ename = 'JONES'
13 ) AND
14 Ename NOT IN('MILLER', 'JONES') AND
15 Deptno = (
16 SELECT Deptno
17 FROM Dept
18 WHERE Dname = 'SALES'
19 ) AND
20 Job = (
21 SELECT Job
22 FROM Emp
23 WHERE Ename = 'ALLEN'
24 ) AND
25* Ename <> 'ALLEN'
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
Where to use Scalar Subqueries
Scalar Subqueries are mainly used for the following tasks:
 Remove an Outer Join
 Aggregate from Multiple Tables
 Inserting into Tables, based on Values from other Tables
Remove an Outer Join
When you remove an outer join, not only is the resulting query usually easier to read, but many times, the performance can be
improved as well.
Consider this Outer Join Query:
select a.username, count(*) Cnt, avg(b.object_id) Avg
from all_users a, all_objects b
where a.username = b.owner(+)
group by a.username
order by a.username;
USERNAME CNT AVG
------------------------------ ---------- ----------
ANONYMOUS 1
CTXSYS 338 42790.6657
DBSNMP 46 9673.65217
DIP 1
DMSYS 189 42439.6878
EXFSYS 279 42935.1792
F1 1
G1 1
MDDATA 1
MDSYS 863 49667.7068
MGMT_VIEW 1
NAVSEARCH 36 51610.0278
OLAPSYS 718 49881.61
ORDPLUGINS 10 44487.7
ORDSYS 1720 45540.4012
OUTLN 7 1179.57143
SCOTT 43 53183.1163
SI_INFORMTN_SCHEMA 8 44475.5
SYS 22957 17383.9356
SYSMAN 1291 52369.1534
SYSTEM 426 6553.87324
TSMSYS 2 8687.5
WMSYS 232 10102.1422
XDB 346 43841.8006
Replaced by the following Query using two Scalar Subqueries.
select a.username UserName, (select count(*)
from all_objects b
where b.owner = a.username) Cnt,
(select avg(object_id )
from all_objects b
where b.owner = a.username) Avg
from all_users a
order by a.username;
USERNAME CNT AVG
------------------------------ ---------- ----------
ANONYMOUS 0
CTXSYS 338 42790.6657
DBSNMP 46 9673.65217
DIP 0
DMSYS 189 42439.6878
EXFSYS 279 42935.1792
F1 0
G1 0
MDDATA 0
MDSYS 863 49667.7068
MGMT_VIEW 0
NAVSEARCH 36 51610.0278
OLAPSYS 718 49881.61
ORDPLUGINS 10 44487.7
ORDSYS 1720 45540.4012
OUTLN 7 1179.57143
SCOTT 43 53183.1163
SI_INFORMTN_SCHEMA 8 44475.5
SYS 22957 17383.9356
SYSMAN 1291 52369.1534
SYSTEM 426 6553.87324
TSMSYS 2 8687.5
WMSYS 232 10102.1422
XDB 346 43841.8006
The two Scalar Subqueries can be even replaced with an Object Type
create or replace type ConvertType as object
(
cnt number,
average number
)
/
That maps to the two numbers we would like to return: the count and the average. Now, we can get the result using this query:
select username UserName, a.data.cnt Cnt, a.data.average Avg
from (select username, (select ConvertType(count(*),avg(object_id))
from all_objects b
where b.owner = a.username) data
from all_users a) A
order by username;
USERNAME CNT AVG
------------------------------ ---------- ----------
ANONYMOUS 0
CTXSYS 338 42790.6657
DBSNMP 46 9673.65217
DIP 0
DMSYS 189 42439.6878
EXFSYS 279 42935.1792
F1 0
G1 0
MDDATA 0
MDSYS 863 49667.7068
MGMT_VIEW 0
NAVSEARCH 36 51610.0278
OLAPSYS 718 49881.61
ORDPLUGINS 10 44487.7
ORDSYS 1720 45540.4012
OUTLN 7 1179.57143
SCOTT 43 53183.1163
SI_INFORMTN_SCHEMA 8 44475.5
SYS 22957 17383.9356
SYSMAN 1291 52369.1534
SYSTEM 426 6553.87324
TSMSYS 2 8687.5
WMSYS 232 10102.1422
XDB 346 43841.8006
Aggregate from Multiple Tables
If you want to calculate Aggregates from different Tables, then Scalar Subqueries can be used.
Suppose you are trying to generate a report that shows by username, the username, created date, number of tables they own, and
the number of constraints they own for all users.
Solution with Outer Join and Inline View:
select a.username,
a.created,
nvl(b.cons_cnt,0) cons,
nvl(c.tables_cnt,0) tables
from all_users a,
(select all_constraints.owner, count(*) cons_cnt
from all_constraints, all_users
where all_users.username = all_constraints.owner
group by owner) b,
(select all_tables.owner, count(*) tables_cnt
from all_tables, all_users
where all_users.username = all_tables.owner
group by owner) c
where a.username = b.owner(+)
and a.username = c.owner(+)
order by a.username
/
USERNAME CREATED CONS TABLES
------------------------------ --------- ---------- ----------
ANONYMOUS 23-AUG-06 0 0
CTXSYS 23-AUG-06 92 37
DBSNMP 23-AUG-06 88 21
DIP 23-AUG-06 0 0
DMSYS 23-AUG-06 7 2
EXFSYS 23-AUG-06 89 44
F1 23-OCT-06 0 0
G1 23-OCT-06 0 0
MDDATA 23-AUG-06 0 0
MDSYS 23-AUG-06 170 49
MGMT_VIEW 24-OCT-06 0 0
NAVSEARCH 23-OCT-06 12 7
OLAPSYS 23-AUG-06 568 126
ORDPLUGINS 23-AUG-06 0 0
ORDSYS 23-AUG-06 13 4
OUTLN 23-AUG-06 0 3
SCOTT 23-AUG-06 57 7
SI_INFORMTN_SCHEMA 23-AUG-06 0 0
SYS 23-AUG-06 2317 709
SYSMAN 24-OCT-06 1161 337
SYSTEM 23-AUG-06 418 142
TSMSYS 23-AUG-06 2 1
WMSYS 23-AUG-06 105 40
XDB 23-AUG-06 33 11
Elapsed: 00:00:00.51
Solution with two Scalar Subqueries:
select username,
created,
(select count(*)
from all_constraints
where owner = username) cons,
(select count(*)
from all_tables
where owner = username) tables
from all_users
order by username
/
USERNAME CREATED CONS TABLES
------------------------------ --------- ---------- ----------
ANONYMOUS 23-AUG-06 0 0
CTXSYS 23-AUG-06 92 37
DBSNMP 23-AUG-06 88 21
DIP 23-AUG-06 0 0
DMSYS 23-AUG-06 7 2
EXFSYS 23-AUG-06 89 44
F1 23-OCT-06 0 0
G1 23-OCT-06 0 0
MDDATA 23-AUG-06 0 0
MDSYS 23-AUG-06 170 49
MGMT_VIEW 24-OCT-06 0 0
NAVSEARCH 23-OCT-06 12 7
OLAPSYS 23-AUG-06 568 126
ORDPLUGINS 23-AUG-06 0 0
ORDSYS 23-AUG-06 13 4
OUTLN 23-AUG-06 0 3
SCOTT 23-AUG-06 57 7
SI_INFORMTN_SCHEMA 23-AUG-06 0 0
SYS 23-AUG-06 2317 709
SYSMAN 24-OCT-06 1161 337
SYSTEM 23-AUG-06 418 142
TSMSYS 23-AUG-06 2 1
WMSYS 23-AUG-06 105 40
XDB 23-AUG-06 33 11
Elapsed: 00:00:02.32
As you can see, in this case, the Outer Join / Inline View solution is much faster.
Inserting into Tables, based on Values from other Tables
Scalar subqueries are also handy for inserting into tables, based on values from other tables.
create table summary (
sum_sal number,
max_sal number,
min_sal number,
avg_sal number
);
insert into summary (
sum_sal,
max_sal,
min_sal,
avg_sal)
values (
(select sum(sal) from emp),
(select max(sal) from emp),
(select min(sal) from emp),
(select avg(sal) from emp)
);
1 row created.
Conclusion
Scalar subqueries are restricted to returning a single value because they select a finite value. Scalar subqueries can be used in almost
any place where an expression can be used, including:
 CASE expressions
 SELECT statements
 VALUES clauses of INSERT statements
 WHERE clauses
 ORDER BY clauses
 Parameters of a function
There are also important restrictions on scalar subqueries. Scalar subqueries can’t be used for:

 Default values for columns
 RETURNING clauses
 Hash expressions for clusters
 Functional index expressions
 CHECK constraints on columns
 WHEN condition of triggers
 GROUP BY and HAVING clauses
 START WITH and CONNECT BY clauses
Subqueries That Return Multiple Rows
Or
MULTIPLE ROW OPERATORS (IN, ANY, ALL and w/NOT) – the subquery can return MORE THAN ONE value
If the subquery has a GROUP BY clause, it implies that the subquery will return more than one value so a Multiple Row Operator must
be used!!
If the subquery returns a NULL value no data will be returned because Null can not be compared – WHERE job_id = NULL and if there
was a job_id that was NULL then no rows would be returned because the comparison of two null values yields a NULL
If the WHERE clause has an IN,ANY,ALL then NULL values can be processed
The inner and outer query can get data from different tables.
SELECT e.last_name, e.department_id, d.department_name *employees who work in the same
FROM employees e JOIN departments d department as 'Grant'
ON e.department_id = d.department_id
WHERE e.department_id IN
(SELECT e.department_id
FROM employees e
WHERE last_name = 'Grant');
SQL> SELECT * FROM Emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
7934 MILLER CLERK 7782 23-JAN-82 1300 10
14 rows selected.
SQL> SELECT *
2 FROM Emp
3 WHERE Sal IN
4
5 SELECT Sal
6 FROM Emp
7 WHERE Ename = 'WARD'
8
9 SELECT Sal
10 FROM Emp
11 WHERE Ename = 'FORD'
12
ERROR at line 8:
ORA-00907: missing right parenthesis
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE Sal IN
4
5
6 SELECT Sal
7 FROM Emp
8 WHERE Ename = 'WARD'
9
10
11 SELECT Sal
12 FROM Emp
13 WHERE Ename = 'FORD'
14
15*
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
SQL> cl scr
SQL> Select MIN(Sal)
2 FROM Emp
3 GROUP BY Deptno;
(
,
);
,
*
(
(
),
(
)
)
MIN(SAL)
----------
1300
800
950
SQL> cl scr
SQL> SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Sal IN(Select MIN(Sal)
4 FROM Emp
5 GROUP BY Deptno);
ENAME SAL DEPTNO
---------- ----- ----------
MILLER 1300 10
SMITH 800 20
JAMES 950 30
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1234, 'SAMPLE01', 20, 1300);
1 row created.
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1235, 'SAMPLE02', 20, 950);
1 row created.
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1236, 'SAMPLE03', 30, 1300);
1 row created.
SQL> SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Sal IN(Select MIN(Sal)
4 FROM Emp
5 GROUP BY Deptno);
ENAME SAL DEPTNO
---------- ----- ----------
SAMPLE03 1300 30
SAMPLE01 1300 20
MILLER 1300 10
SMITH 800 20
SAMPLE02 950 20
JAMES 950 30
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Sal IN(Select MAX(Sal)
4 FROM Emp
5* GROUP BY Deptno)
SQL> /
ENAME SAL DEPTNO
---------- ----- ----------
KING 5000 10
SCOTT 3000 20
FORD 3000 20
BLAKE 2850 30
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Sal IN(Select MAX(Sal)
4 FROM Emp
5* GROUP BY Job)
SQL> /
ENAME SAL DEPTNO
---------- ----- ----------
SCOTT 3000 20
FORD 3000 20
SAMPLE03 1300 30
SAMPLE01 1300 20
MILLER 1300 10
JONES 2975 20
KING 5000 10
ALLEN 1600 30
8 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Sal IN(Select MIN(Sal)
4 FROM Emp
5* GROUP BY Job)
SQL> /
ENAME SAL DEPTNO
---------- ----- ----------
SCOTT 3000 20
FORD 3000 20
SMITH 800 20
CLARK 2450 10
KING 5000 10
WARD 1250 30
MARTIN 1250 30
SAMPLE02 950 20
JAMES 950 30
9 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Deptno
2 FROM Emp
3 WHERE Sal IN(Select AVG(Sal)
4 FROM Emp
5* GROUP BY Job)
SQL> /
ENAME SAL DEPTNO
---------- ----- ----------
SCOTT 3000 20
FORD 3000 20
KING 5000 10
SQL> SPOOL OFF
SQL> cl scr
SQL> SELECT HireDate
2 FROM Emp
3 WHERE Deptno = 20;
HIREDATE
---------
02-APR-81
03-DEC-81
17-DEC-80
09-DEC-82
12-JAN-83
SQL> SELECT Sal
2 FROM Emp
3 WHERE HireDate IN(SELECT HireDate
4 FROM Emp
5 WHERE Deptno = 20);
SAL
----------
2975
3000
950
800
3000
1100
6 rows selected.
SQL> SELECT Empno, Ename, Job, Sal, Deptno, HireDate
2 FROM Emp
3 WHERE Sal IN(SELECT Sal
4 FROM Emp
5 WHERE HireDate IN(SELECT HireDate
6 FROM Emp
7 WHERE Deptno = 20)
8
EMPNO ENAME JOB SAL DEPTNO HIREDATE
---------- ---------- --------- ---------- ---------- ---------
7566 JONES MANAGER 2975 20 02-APR-81
7900 JAMES CLERK 950 30 03-DEC-81
7902 FORD ANALYST 3000 20 03-DEC-81
7369 SMITH CLERK 800 20 17-DEC-80
7788 SCOTT ANALYST 3000 20 09-DEC-82
7876 ADAMS CLERK 1100 20 12-JAN-83
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Job, Sal, Deptno, HireDate
2 FROM Emp
3 WHERE Sal IN(
4 SELECT Sal
5 FROM Emp
6 WHERE HireDate IN(
7 SELECT HireDate
8 FROM Emp
9 WHERE Deptno = (
10 SELECT Deptno
11 FROM Dept
12 WHERE Dname = 'RESEARCH'
13
14
15* )
SQL> /
EMPNO ENAME JOB SAL DEPTNO HIREDATE
---------- ---------- --------- ---------- ---------- ---------
7566 JONES MANAGER 2975 20 02-APR-81
7900 JAMES CLERK 950 30 03-DEC-81
7902 FORD ANALYST 3000 20 03-DEC-81
7369 SMITH CLERK 800 20 17-DEC-80
7788 SCOTT ANALYST 3000 20 09-DEC-82
7876 ADAMS CLERK 1100 20 12-JAN-83
6 rows selected.
SQL> cl scr
SQL> COLUMN Empno FORMAT 9999
SQL> COLUMN Sal FORMAT 9999
SQL> COLUMN Comm FORMAT 9999
SQL> COLUMN MGR FORMAT 9999
SQL> cl scr
);
)
)
SQL> SELECT *
2 FROM Emp
3 WHERE Sal >ANY(1100, 2750, 950);
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3* WHERE Sal > 1100 OR Sal > 2750 OR Sal > 950
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> cl scr
SQL> SELECT *
2 FROM Emp
3 WHERE Sal >ANY(
4 SELECT Sal
5 FROM Emp
6 WHERE Deptno = 30
7
SQL> SELECT Sal
2 FROM Emp
3 WHERE Deptno = 30;
SAL
-----
2850
1250
1600
1500
950
1250
6 rows selected.
SQL> SELECT *
2 FROM Emp
3 WHERE Sal >ANY(
4 SELECT Sal
5 FROM Emp
6 WHERE Deptno = 30
7 );
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
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
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
12 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE Sal >(
4 SELECT MIN(Sal)
)
8
5 FROM Emp
6 WHERE Deptno = 30
7* )
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10
12 rows selected.
SQL> cl scr
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM Emp
3 WHERE Sal >SOME(
4 SELECT Sal
5 FROM Emp
6 WHERE Deptno = 30
7* )
SQL> /
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7788 SCOTT ANALYST 7566 09-DEC-82 3000 20
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
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- --------- ----- ----- ----------
7876 ADAMS CLERK 7788 12-JAN-83 1100 20
12 rows selected.
SQL> cl scr
SQL> SELECT Sal
2 FROM Emp
3 WHERE Job = 'CLERK';
SAL
-----
950
800
1100
1300
SQL> SELECT Empno, Ename, Job
2 FROM Emp
3 WHERE Sal < ANY(SELECT Sal
4 FROM Emp
5 WHERE Job = 'CLERK');
EMPNO ENAME JOB
----- ---------- ---------
7369 SMITH CLERK
7900 JAMES CLERK
7876 ADAMS CLERK
7654 MARTIN SALESMAN
7521 WARD SALESMAN
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Job, Sal
2 FROM Emp
3 WHERE Sal < ANY(SELECT Sal
4 FROM Emp
5* WHERE Job = 'CLERK')
SQL> /
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7369 SMITH CLERK 800
7900 JAMES CLERK 950
7876 ADAMS CLERK 1100
7654 MARTIN SALESMAN 1250
7521 WARD SALESMAN 1250
SQL> SELECT Empno, Ename, Job, Sal
2 FROM Emp
3 WHERE Sal < ANY(Select Sal
4 FROM Emp
5 WHERE Deptno = 20)
6 AND Job <> 'CLERK';
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7654 MARTIN SALESMAN 1250
7521 WARD SALESMAN 1250
7844 TURNER SALESMAN 1500
7499 ALLEN SALESMAN 1600
7782 CLARK MANAGER 2450
7698 BLAKE MANAGER 2850
7566 JONES MANAGER 2975
7 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Job, Sal
2 FROM Emp
3 WHERE Sal < ANY(SELECT Sal
4 FROM Emp
5 WHERE Job = 'CLERK') AND
6* Job <> 'CLERK'
SQL> /
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7654 MARTIN SALESMAN 1250
7521 WARD SALESMAN 1250
SQL> cl scr
SQL> SELECT DISTINCT MGR
2 FROM Emp;
MGR
-----
7566
7698
7782
7788
7839
7902
7 rows selected.
SQL> SELECT Sal
2 FROM Emp
3 WHERE MGR IN(SELECT DISTINCT MGR
4 FROM Emp);
SAL
-----
2975
2450
2850
1250
950
1500
1600
1250
3000
3000
800
SAL
-----
1100
1300
13 rows selected.
SQL> SELECT Empno, Ename, Job, Sal
2 FROM Emp
3 WHERE Sal < ANY(SELECT Sal
4 FROM Emp
5 WHERE MGR IN(SELECT DISTINCT MGR
6 FROM Emp)
7
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7900 JAMES CLERK 950
7521 WARD SALESMAN 1250
7369 SMITH CLERK 800
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
11 rows selected.
SQL> SELECT Sal
2 FROM Emp
3 WHERE Empno IN(SELECT DISTINCT MGR
4 FROM Emp);
SAL
-----
2975
2850
2450
3000
5000
3000
6 rows selected.
SQL> SELECT Empno, Ename, Job, Sal
2 FROM Emp
);
3 WHERE Sal < ANY(SELECT Sal
4 FROM Emp
5 WHERE Empno IN(SELECT DISTINCT MGR
6 FROM Emp)
7
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7698 BLAKE MANAGER 2850
7782 CLARK MANAGER 2450
7566 JONES MANAGER 2975
7654 MARTIN SALESMAN 1250
7499 ALLEN SALESMAN 1600
7844 TURNER SALESMAN 1500
7900 JAMES CLERK 950
7521 WARD SALESMAN 1250
7902 FORD ANALYST 3000
7369 SMITH CLERK 800
7788 SCOTT ANALYST 3000
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7876 ADAMS CLERK 1100
7934 MILLER CLERK 1300
13 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Sal <ANY (SELECT Sal
4 FROM Emp
5 WHERE HireDate IN(SELECT HireDate
6 FROM Emp
7 WHERE Deptno = 20);
WHERE Deptno = 20)
*
ERROR at line 7:
ORA-00907: missing right parenthesis
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Job, Sal, Deptno
2 FROM Emp
3 WHERE Sal <ANY (SELECT Sal
4 FROM Emp
5 WHERE HireDate IN(SELECT HireDate
6 FROM Emp
7 WHERE Deptno = 20)
8* )
SQL> /
EMPNO ENAME JOB SAL DEPTNO
);
----- ---------- --------- ----- ----------
7698 BLAKE MANAGER 2850 30
7782 CLARK MANAGER 2450 10
7566 JONES MANAGER 2975 20
7654 MARTIN SALESMAN 1250 30
7499 ALLEN SALESMAN 1600 30
7844 TURNER SALESMAN 1500 30
7900 JAMES CLERK 950 30
7521 WARD SALESMAN 1250 30
7369 SMITH CLERK 800 20
7876 ADAMS CLERK 1100 20
7934 MILLER CLERK 1300 10
11 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, Job
2 FROM Emp
3 WHERE Sal >ANY(SELECT Sal
4 FROM Emp
5 WHERE Job = 'CLERK');
EMPNO ENAME JOB
----- ---------- ---------
7839 KING PRESIDENT
7902 FORD ANALYST
7788 SCOTT ANALYST
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7499 ALLEN SALESMAN
7844 TURNER SALESMAN
7934 MILLER CLERK
7654 MARTIN SALESMAN
7521 WARD SALESMAN
EMPNO ENAME JOB
----- ---------- ---------
7876 ADAMS CLERK
7900 JAMES CLERK
13 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Job
2 FROM Emp
3 WHERE Sal >(SELECT MIN(Sal)
4 FROM Emp
5* WHERE Job = 'CLERK')
SQL> /
EMPNO ENAME JOB
----- ---------- ---------
7839 KING PRESIDENT
7698 BLAKE MANAGER
7782 CLARK MANAGER
7566 JONES MANAGER
7654 MARTIN SALESMAN
7499 ALLEN SALESMAN
7844 TURNER SALESMAN
7900 JAMES CLERK
7521 WARD SALESMAN
7902 FORD ANALYST
7788 SCOTT ANALYST
EMPNO ENAME JOB
----- ---------- ---------
7876 ADAMS CLERK
7934 MILLER CLERK
13 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, Job, Sal
2 FROM Emp
3 WHERE Sal >ALL(SELECT AVG(Sal)
4 FROM Emp
5 GROUP BY Deptno);
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7839 KING PRESIDENT 5000
7566 JONES MANAGER 2975
7902 FORD ANALYST 3000
7788 SCOTT ANALYST 3000
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename, Job, Sal
2 FROM Emp
3 WHERE Sal >(SELECT MAX(AVG(Sal))
4 FROM Emp
5* GROUP BY Deptno)
SQL> /
EMPNO ENAME JOB SAL
----- ---------- --------- -----
7839 KING PRESIDENT 5000
7566 JONES MANAGER 2975
7902 FORD ANALYST 3000
7788 SCOTT ANALYST 3000
SQL> SELECT Empno, Ename Job, Sal
2 FROM Emp
3 WHERE Sal <ALL(SELECT AVG(Sal)
4 FROM Emp
5 GROUP BY Deptno);
EMPNO JOB SAL
----- ---------- -----
7654 MARTIN 1250
7844 TURNER 1500
7900 JAMES 950
7521 WARD 1250
7369 SMITH 800
7876 ADAMS 1100
7934 MILLER 1300
7 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Empno, Ename Job, Sal
2 FROM Emp
3 WHERE Sal <(SELECT MIN(AVG(Sal))
4 FROM Emp
5* GROUP BY Deptno)
SQL> /
EMPNO JOB SAL
----- ---------- -----
7654 MARTIN 1250
7844 TURNER 1500
7900 JAMES 950
7521 WARD 1250
7369 SMITH 800
7876 ADAMS 1100
7934 MILLER 1300
7 rows selected.
SQL> cl scr
SQL> SELECT Ename, Sal, Deptno, HireDate
2 FROM Emp
3 WHERE Sal > (
4 SELECT MAX(Sal)
5 FROM Emp
6 WHERE Deptno = 30
7 ) AND
8 Deptno = (
9 SELECT Deptno
10 FROM Dept
11 WHERE Dname = 'RESEARCH'
12
ENAME SAL DEPTNO HIREDATE
---------- ----- ---------- ---------
JONES 2975 20 02-APR-81
FORD 3000 20 03-DEC-81
SCOTT 3000 20 09-DEC-82
);
SQL> WHERE (Sal, Deptno)SPOOL OFF
SP2-0734: unknown command beginning "WHERE (Sal..." - rest of line ignored.
SQL> SPOOL OFF
SQL> cl scr
SQL> ED
SP2-0107: Nothing to save.
SQL> cl scr
SQL> SELECT ProdId, Qty
2 FROM Item
3 WHERE OrdID = 605;
PRODID QTY
---------- ----------
100861 100
100870 500
100890 5
101860 50
101863 100
102130 10
6 rows selected.
SQL> SELECT OrdID, ProdID, Qty
2 FROM Item;
ORDID PRODID QTY
---------- ---------- ----------
610 100890 1
611 100861 1
612 100860 100
601 200376 1
602 100870 20
604 100890 3
604 100861 2
604 100860 10
603 100860 4
610 100860 1
610 100870 3
ORDID PRODID QTY
---------- ---------- ----------
613 200376 200
614 100860 444
614 100870 1000
612 100861 20
612 101863 150
620 100860 10
620 200376 1000
620 102130 500
613 100871 100
613 101860 200
613 200380 150
ORDID PRODID QTY
---------- ---------- ----------
619 102130 100
617 100860 50
617 100861 100
614 100871 1000
616 100861 10
616 100870 50
616 100890 2
616 102130 10
616 200376 10
619 200380 100
619 200376 100
ORDID PRODID QTY
---------- ---------- ----------
615 100861 4
607 100871 1
615 100870 100
617 100870 500
617 100871 500
617 100890 500
617 101860 100
617 101863 200
617 102130 100
617 200376 200
617 200380 300
ORDID PRODID QTY
---------- ---------- ----------
609 100870 5
609 100890 1
618 100860 23
618 100861 50
618 100870 10
621 100861 10
621 100870 100
615 100871 50
608 101860 1
608 100871 2
609 100861 1
ORDID PRODID QTY
---------- ---------- ----------
606 102130 1
605 100861 100
605 100870 500
605 100890 5
605 101860 50
605 101863 100
605 102130 10
612 100871 100
619 100871 50
64 rows selected.
SQL> SELECT DISTINCT OrdID
2 FROM Item;
ORDID
----------
601
602
603
604
605
606
607
608
609
610
611
ORDID
----------
612
613
614
615
616
617
618
619
620
621
21 rows selected.
SQL> SELECT OrdID, COUNT(*) ItemCnt
2 FROM Item
3 GROUP BY OrdID;
ORDID ITEMCNT
---------- ----------
601 1
602 1
603 1
604 3
605 6
606 1
607 1
608 2
609 3
610 3
611 1
ORDID ITEMCNT
---------- ----------
612 4
613 4
614 3
615 3
616 5
617 10
618 3
619 4
620 3
621 2
21 rows selected.
SQL> cl scr
SQL> SELECT ProdId, Qty
2 FROM Item
3 WHERE OrdID = 605;
PRODID QTY
---------- ----------
100861 100
100870 500
100890 5
101860 50
101863 100
102130 10
6 rows selected.
SQL> SELECT OrdID, ProdID, Qty
2 FROM Item
3 WHERE ProdID IN(100861, 100870, 100890, 101860, 101863, 102130) AND
4 OrdID <> 605;
ORDID PRODID QTY
---------- ---------- ----------
610 100890 1
611 100861 1
602 100870 20
604 100890 3
604 100861 2
610 100870 3
614 100870 1000
612 100861 20
612 101863 150
620 102130 500
613 101860 200
ORDID PRODID QTY
---------- ---------- ----------
619 102130 100
617 100861 100
616 100861 10
616 100870 50
616 100890 2
616 102130 10
615 100861 4
615 100870 100
617 100870 500
617 100890 500
617 101860 100
ORDID PRODID QTY
---------- ---------- ----------
617 101863 200
617 102130 100
609 100870 5
609 100890 1
618 100861 50
618 100870 10
621 100861 10
621 100870 100
608 101860 1
609 100861 1
606 102130 1
33 rows selected.
SQL> SELECT OrdID, ProdID, Qty
2 FROM Item
3 WHERE (ProdID, Qty) IN(SELECT ProdId, Qty
4 FROM Item
5 WHERE OrdID = 605) AND
6 OrdID <> 605;
ORDID PRODID QTY
---------- ---------- ----------
617 100861 100
617 100870 500
616 102130 10
SQL> SELECT OrdID, ProdID, Qty
2 FROM Item
3 WHERE ProdID IN(SELECT ProdID
4 FROM Item
5 WHERE OrdID = 605)
6 AND Qty IN (SELECT Qty
7 FROM Item
8 WHERE OrdID = 605)
9 AND OrdID <> 605
10 /
ORDID PRODID QTY
---------- ---------- ----------
616 100861 10
621 100861 10
618 100861 50
617 100861 100
618 100870 10
616 100870 50
609 100870 5
617 100870 500
615 100870 100
621 100870 100
617 100890 500
ORDID PRODID QTY
---------- ---------- ----------
617 101860 100
616 102130 10
620 102130 500
619 102130 100
617 102130 100
16 rows selected.
SQL> cl scr
SQL> SELECT OrdID, ProdID, Qty
2 FROM Item
3 WHERE (ProdID, Qty) IN(100861, 100);
WHERE (ProdID, Qty) IN(100861, 100)
*
ERROR at line 3:
ORA-00920: invalid relational operator
SQL> cl scr
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE (Deptno, Sal) IN (SELECT Deptno, MAX(Sal)
4 FROM Emp
5 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE Deptno IN (SELECT Deptno
4 FROM Emp
5 GROUP BY Deptno) AND
6 Sal IN (SELECT MAX(Sal)
7 FROM Emp
8 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1234, 'SAMPLE01', 10, 3000);
1 row created.
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1235, 'SAMPLE02', 10, 2850);
1 row created.
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1236, 'SAMPLE03', 20, 2850);
1 row created.
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE (Deptno, Sal) IN (SELECT Deptno, MAX(Sal)
4 FROM Emp
5 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE Deptno IN (SELECT Deptno
4 FROM Emp
5 GROUP BY Deptno) AND
6 Sal IN (SELECT MAX(Sal)
7 FROM Emp
8 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
SAMPLE01 10 3000
BLAKE 30 2850
SAMPLE03 20 2850
SAMPLE02 10 2850
7 rows selected.
SQL> ROLLBACK;
Rollback complete.
SQL> cl scr
SQL> ROLLBACK;
Rollback complete.
SQL> cl scr
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 /
ENAME SAL COMM
---------- ---------- ----------
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250 1400
ALLEN 1600 300
TURNER 1500 0
JAMES 950
WARD 1250 500
FORD 3000
SMITH 800
ENAME SAL COMM
---------- ---------- ----------
SCOTT 3000
ADAMS 1100
MILLER 1300
14 rows selected.
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm IN(1400, 300, NULL);
ENAME SAL COMM
---------- ---------- ----------
MARTIN 1250 1400
ALLEN 1600 300
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm NOT IN(1400, 300, NULL);
no rows selected
SQL> cl scr
SQL> SELECT Ename, Empno, MGR
2 FROM Emp;
ENAME EMPNO MGR
---------- ---------- ----------
KING 7839
BLAKE 7698 7839
CLARK 7782 7839
JONES 7566 7839
MARTIN 7654 7698
ALLEN 7499 7698
TURNER 7844 7698
JAMES 7900 7698
WARD 7521 7698
FORD 7902 7566
SMITH 7369 7902
ENAME EMPNO MGR
---------- ---------- ----------
SCOTT 7788 7566
ADAMS 7876 7788
MILLER 7934 7782
14 rows selected.
SQL> SELECT E.Ename
2 FROM Emp E
3 WHERE E.Empno IN(SELECT M.Mgr
4 FROM Emp M);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT E.Ename
2 FROM Emp E
3 WHERE E.Empno NOT IN(SELECT M.Mgr
4* FROM Emp M)
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT E.Ename
2 FROM Emp E
3 WHERE E.Empno NOT IN(SELECT NVL(M.Mgr, 0)
4* FROM Emp M)
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
8 rows selected.
SQL> cl scr
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm IN(SELECT Comm
4 FROM Emp);
ENAME SAL COMM
---------- ---------- ----------
MARTIN 1250 1400
ALLEN 1600 300
TURNER 1500 0
WARD 1250 500
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm NOT IN(SELECT Comm
4* FROM Emp)
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm NOT IN(SELECT NVL(Comm, 0)
4* FROM Emp)
SQL> /
no rows selected
SQL> cl scr
Some tricky comparison examples:
Salary > ANY means the salary can be greater than the minimum value returned by the subquery
Salary < ANY means the salary must be less than the maximum value
Salary = ANY means the salary can be equal to any of the values returned from the subquery (same as IN)
Salary > ALL means the salary must be greater than the maximum value
Salary < ALL means the salary must be less than the mimimum
Minimum Maximum
ALL
The ALL comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a
list or subquery.
When the ALL condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together
with AND operators, as shown below.
SELECT empno, sal
FROM emp
WHERE sal > ALL (2000, 3000, 4000);
EMPNO SAL
---------- ----------
7839 5000
SQL>
-- Transformed to equivalent statement without ALL.
SELECT empno, sal
FROM emp
WHERE sal > 2000 AND sal > 3000 AND sal > 4000;
EMPNO SAL
---------- ----------
7839 5000
SQL>
When the ALL condition is followed by a subquery, the optimizer performs a two-step transformation as shown below.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ALL (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 20);
EMPNO SAL
---------- ----------
7839 5000
SQL>
-- Transformed to equivalent statement using ANY.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE NOT (e1.sal <= ANY (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 20));
EMPNO SAL
---------- ----------
7839 5000
SQL>
-- Transformed to equivalent statement without ANY.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE NOT EXISTS (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 20
AND e1.sal <= e2.sal);
EMPNO SAL
---------- ----------
7839 5000
SQL>
If a subquery returns zero rows, the condition evaluates to TRUE.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ALL (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);
EMPNO SAL
---------- ----------
7369 800
7900 950
7876 1100
7521 1250
7654 1250
7934 1300
7844 1500
7499 1600
7782 2450
7698 2850
7566 2975
7788 3000
7902 3000
7839 5000
SQL>
ANY
The ANY comparison condition is used to compare a value to a list or subquery. It must be preceded by =, !=, >, <, <=, >= and followed by a
list or subquery.
When the ANY condition is followed by a list, the optimizer expands the initial condition to all elements of the list and strings them together
with OR operators, as shown below
SELECT empno, sal
FROM emp
WHERE sal > ANY (2000, 3000, 4000);
EMPNO SAL
---------- ----------
7566 2975
7698 2850
7782 2450
7788 3000
7839 5000
7902 3000
SQL>
-- Transformed to equivalent statement without ANY.
SELECT empno, sal
FROM emp
WHERE sal > 2000 OR sal > 3000 OR sal > 4000;
EMPNO SAL
---------- ----------
7566 2975
7698 2850
7782 2450
7788 3000
7839 5000
7902 3000
When the ANY condition is followed by a subquery, the optimizer performs a single transformation as shown below.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ANY (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 10);
EMPNO SAL
---------- ----------
7839 5000
7902 3000
7788 3000
7566 2975
7698 2850
7782 2450
7499 1600
7844 1500
SQL>
-- Transformed to equivalent statement without ANY.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE EXISTS (SELECT e2.sal
FROM emp e2
WHERE e2.deptno = 10
AND e1.sal > e2.sal);
EMPNO SAL
---------- ----------
7839 5000
7902 3000
7788 3000
7566 2975
7698 2850
7782 2450
7499 1600
7844 1500
SQL>
Assuming subqueries don't return zero rows, the following statements can be made for both list and subquery versions:
 "x = ANY (...)": The value must match one or more values in the list to evaluate to TRUE.
 "x != ANY (...)": The value must not match one or more values in the list to evaluate to TRUE.
 "x > ANY (...)": The value must be greater than the smallest value in the list to evaluate to TRUE.
 "x < ANY (...)": The value must be smaller than the biggest value in the list to evaluate to TRUE.
 "x >= ANY (...)": The value must be greater than or equal to the smallest value in the list to evaluate to TRUE.
 "x <= ANY (...)": The value must be smaller than or equal to the biggest value in the list to evaluate to TRUE.
If a subquery returns zero rows, the condition evaluates to FALSE.
SELECT e1.empno, e1.sal
FROM emp e1
WHERE e1.sal > ANY (SELECT e2.sal FROM emp e2 WHERE e2.deptno = 100);
no rows selected
SQL>
SOME
The SOME and ANY comparison conditions do exactly the same thing and are completely interchangeable.
Multiple Row Subquery in the WHERE clause:
SELECT job_id, last_name, department_id, salary
FROM employees
WHERE job_id IN
(SELECT job_id
FROM employees
WHERE hire_date BETWEEN TO_DATE('01-JAN-90') AND TO_DATE('01-JAN-95')
AND salary > 4000);
SELECT e.last_name, e.manager_id, e.employee_id
FROM employees e JOIN employees m ON
e.manager_id = m.employee_id
WHERE e.employee_id IN
(SELECT m.manager_id
FROM employees m);
 Subqueries can return one or multiple rows. If a subquery returns a single row, the =, <, >, <=, >=, or <> operator can be used for
comparison with the subquery. If multiple records are returned, the IN, ANY, ALL, or SOME operator must be used;
 Aggregate Functions, GROUP BY, and HAVING Clauses,” aggregate functions, when used alone, without the presence of any
nonaggregate expressions in the SELECT list, always return one row
 If the subquery returns multiple rows and you want to perform a comparison other than equality or inequality, use the ALL, ANY,
and SOME operators
Comparison Operators for Subqueries
Comparison Operator Subquery Returns One Row Subquery Returns Multiple Rows
Equality = IN.
Inequality <> NOT IN.
Greater than > Use the ANY, ALL, and SOME operators (see Lab
Less than <
Greater than or equal to >=
Less than or equal to <=
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 /
ENAME SAL COMM
---------- ---------- ----------
KING 5000
BLAKE 2850
CLARK 2450
JONES 2975
MARTIN 1250 1400
ALLEN 1600 300
TURNER 1500 0
JAMES 950
WARD 1250 500
FORD 3000
SMITH 800
ENAME SAL COMM
---------- ---------- ----------
SCOTT 3000
ADAMS 1100
MILLER 1300
14 rows selected.
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm IN(1400, 300, NULL);
ENAME SAL COMM
---------- ---------- ----------
MARTIN 1250 1400
ALLEN 1600 300
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm NOT IN(1400, 300, NULL);
no rows selected
SQL> cl scr
SQL> SELECT Ename, Empno, MGR
2 FROM Emp;
ENAME EMPNO MGR
---------- ---------- ----------
KING 7839
BLAKE 7698 7839
CLARK 7782 7839
JONES 7566 7839
MARTIN 7654 7698
ALLEN 7499 7698
TURNER 7844 7698
JAMES 7900 7698
WARD 7521 7698
FORD 7902 7566
SMITH 7369 7902
ENAME EMPNO MGR
---------- ---------- ----------
SCOTT 7788 7566
ADAMS 7876 7788
MILLER 7934 7782
14 rows selected.
SQL> SELECT E.Ename
2 FROM Emp E
3 WHERE E.Empno IN(SELECT M.Mgr
4 FROM Emp M);
ENAME
----------
JONES
BLAKE
CLARK
SCOTT
KING
FORD
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT E.Ename
2 FROM Emp E
3 WHERE E.Empno NOT IN(SELECT M.Mgr
4* FROM Emp M)
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT E.Ename
2 FROM Emp E
3 WHERE E.Empno NOT IN(SELECT NVL(M.Mgr, 0)
4* FROM Emp M)
SQL> /
ENAME
----------
SMITH
ALLEN
WARD
MARTIN
TURNER
ADAMS
JAMES
MILLER
8 rows selected.
SQL> cl scr
SQL> SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm IN(SELECT Comm
4 FROM Emp);
ENAME SAL COMM
---------- ---------- ----------
MARTIN 1250 1400
ALLEN 1600 300
TURNER 1500 0
WARD 1250 500
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm NOT IN(SELECT Comm
4* FROM Emp)
SQL> /
no rows selected
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Comm
2 FROM Emp
3 WHERE Comm NOT IN(SELECT NVL(Comm, 0)
4* FROM Emp)
SQL> /
no rows selected
SQL> cl scr
a multi-column subquery

Subqueries That Return Multiple Columns-
SQL allows you to compare multiple columns in the WHERE clause to multiple columns of a subquery. The values in the columns must
match both sides of the equation in the WHERE clause for the condition to be true. This means the data type must be compatible and the
number and order of columns must match
Question: What is a multi-column subquery?
Answer: A multi-Column Subquery is simply a subquery that returns more than one column. Here is a working example of a multi-column
subquery
Multiple Column Subqueries
Used when you want to compare two or more columns.
a. Pairwise subqueries - the rows selected must have the PAIR values from the subquery to be displayed
**Find the employee id, manager id and department id for all of the employees who have the same manager_id and
department id as the two employees with id 174 or 178. In other words, find those employees who work in the same
department and have the same manager as does employee id 174 or employee id 178. Exclude employees with id’s
of 174 and 178 from your results.
SELECT employee_id, manager_id, department_id
FROM employees
WHERE(manager_id, department_id) IN
(SELECT manager_id, department_id
FROM employees
WHERE employee_id IN (178, 174)
AND employee_id NOT IN (178,174);
b. NonPairwise subqueries - the rows selected must have ANY COMBINATION of the values from the subquery to be
displayed.
SELECT employee_id, manager_id, department_id
FROM employees
WHERE(manager_id IN
(SELECT manager_id
FROM employees
WHERE employee_id IN (141, 174))
AND (department_id) IN
(SELECT department_id
FROM employees
WHERE employee_id IN (141,174))
AND employee_id NOT IN (141,174);
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE (Deptno, Sal) IN (SELECT Deptno, MAX(Sal)
4 FROM Emp
5 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE Deptno IN (SELECT Deptno
4 FROM Emp
5 GROUP BY Deptno) AND
6 Sal IN (SELECT MAX(Sal)
7 FROM Emp
8 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1234, 'SAMPLE01', 10, 3000);
1 row created.
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1235, 'SAMPLE02', 10, 2850);
1 row created.
SQL> INSERT INTO Emp(Empno, Ename, Deptno, Sal)
2 VALUES(1236, 'SAMPLE03', 20, 2850);
1 row created.
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE (Deptno, Sal) IN (SELECT Deptno, MAX(Sal)
4 FROM Emp
5 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
BLAKE 30 2850
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE Deptno IN (SELECT Deptno
4 FROM Emp
5 GROUP BY Deptno) AND
6 Sal IN (SELECT MAX(Sal)
7 FROM Emp
8 GROUP BY Deptno);
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
SAMPLE01 10 3000
BLAKE 30 2850
SAMPLE03 20 2850
SAMPLE02 10 2850
7 rows selected.
SQL> ROLLBACK;
Rollback complete.
SQL> cl scr
SQL> ROLLBACK;
Rollback complete.
SQL> cl scr
Inline Views and Scalar Subquery Expressions-----------
Subquery in the FROM clause - Inline View
**An Inline View is created by placing a subquery in the FROM clause and giving that subquery an alias. The subquery defines
a data source that can be referenced in the main query.
List the employee last names, salary, department_id for all those whose salary is less than the maximum salary of all those
who work in their department
SELECT a.last_name, a.salary, a.department_id, b.maxsal
FROM employees a,
(SELECT department_id, max(salary) AS maxsal
FROM employees
GROUP BY department_id) b
WHERE a.department_id = b.department_id
AND a.salary < b.maxsal;
FROM clause
A subquery can also be found in the FROM clause. These are called inline views.
Limitations
Oracle allows an unlimited number of subqueries in the FROM clause.
Inline views, also referred to as queries in the FROM clause, allow you to treat a query as a virtual table or view
An inline view is written in the FROM clause of a query and enclosed in a set of parentheses; this query has the alias e. The result of this
query is evaluated and executed
first,
One of the differences between a view and an inline view is that an inline view does not need to be created and stored in the data
dictionary. You can create an inline view or a virtual table by placing your query in the FROM clause of a SQL statement
SQL> SELECT Ename, Deptno, Sal, HireDate
2 FROM Emp;
ENAME DEPTNO SAL HIREDATE
---------- ---------- ---------- ---------
KING 10 5000 17-NOV-81
BLAKE 30 2850 01-MAY-81
CLARK 10 2450 09-JUN-81
JONES 20 2975 02-APR-81
MARTIN 30 1250 28-SEP-81
ALLEN 30 1600 20-FEB-81
TURNER 30 1500 08-SEP-81
JAMES 30 950 03-DEC-81
WARD 30 1250 22-FEB-81
FORD 20 3000 03-DEC-81
SMITH 20 800 17-DEC-80
ENAME DEPTNO SAL HIREDATE
---------- ---------- ---------- ---------
SCOTT 20 3000 09-DEC-82
ADAMS 20 1100 12-JAN-83
MILLER 10 1300 23-JAN-82
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT *
2 FROM (
3 SELECT Ename, Deptno, Sal, HireDate
4 FROM Emp
5* )
SQL> /
ENAME DEPTNO SAL HIREDATE
---------- ---------- ---------- ---------
KING 10 5000 17-NOV-81
BLAKE 30 2850 01-MAY-81
CLARK 10 2450 09-JUN-81
JONES 20 2975 02-APR-81
MARTIN 30 1250 28-SEP-81
ALLEN 30 1600 20-FEB-81
TURNER 30 1500 08-SEP-81
JAMES 30 950 03-DEC-81
WARD 30 1250 22-FEB-81
FORD 20 3000 03-DEC-81
SMITH 20 800 17-DEC-80
ENAME DEPTNO SAL HIREDATE
---------- ---------- ---------- ---------
SCOTT 20 3000 09-DEC-82
ADAMS 20 1100 12-JAN-83
MILLER 10 1300 23-JAN-82
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, Sal * 12 AnnSal, HireDate
2 FROM (
3 SELECT Ename, Deptno, Sal, HireDate
4 FROM Emp
5* )
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
BLAKE 30 2850
CLARK 10 2450
JONES 20 2975
MARTIN 30 1250
ALLEN 30 1600
TURNER 30 1500
JAMES 30 950
WARD 30 1250
FORD 20 3000
SMITH 20 800
ENAME DEPTNO SAL
---------- ---------- ----------
SCOTT 20 3000
ADAMS 20 1100
MILLER 10 1300
14 rows selected.
SQL> SELECT Deptno, AVG(Sal) SalAvg
2 FROM Emp
3 GROUP BY Deptno;
DEPTNO SALAVG
---------- ----------
10 2916.66667
20 2175
30 1566.66667
SQL> SELECT Ename, E.Deptno, Sal, AvgSal
2 FROM Emp E, (
3 SELECT Deptno, AVG(Sal) AvgSal
4 FROM Emp
5 GROUP BY Deptno
6
7 WHERE E.Deptno = E1.Deptno;
ENAME DEPTNO SAL AVGSAL
---------- ---------- ---------- ----------
KING 10 5000 2916.66667
BLAKE 30 2850 1566.66667
CLARK 10 2450 2916.66667
JONES 20 2975 2175
MARTIN 30 1250 1566.66667
ALLEN 30 1600 1566.66667
TURNER 30 1500 1566.66667
JAMES 30 950 1566.66667
WARD 30 1250 1566.66667
FORD 20 3000 2175
SMITH 20 800 2175
ENAME DEPTNO SAL AVGSAL
---------- ---------- ---------- ----------
SCOTT 20 3000 2175
ADAMS 20 1100 2175
MILLER 10 1300 2916.66667
14 rows selected.
SQL> SELECT E.Ename, E.Sal , E.Deptno, E1.SalAvg
2 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno AND
6 E.Sal > E1.SalAvg;
ENAME SAL DEPTNO SALAVG
---------- ---------- ---------- ----------
KING 5000 10 2916.66667
BLAKE 2850 30 1566.66667
) E1
JONES 2975 20 2175
ALLEN 1600 30 1566.66667
FORD 3000 20 2175
SCOTT 3000 20 2175
6 rows selected.
SQL> cl scr
SQL> SELECT E.Ename, E.Sal, E.Deptno,
2 ROUND(E1.SalAvg, 2) DeptAvgSal,
3 ROUND(E.Sal - E1.SalAvg) DiffSalAvg
4 FROM Emp E, (SELECT Deptno, AVG(Sal) SalAvg
5 FROM Emp
6 GROUP BY Deptno) E1
7 WHERE E.Deptno = E1.Deptno
8 ORDER BY Deptno
9 /
ENAME SAL DEPTNO DEPTAVGSAL DIFFSALAVG
---------- ---------- ---------- ---------- ----------
KING 5000 10 2916.67 2083
CLARK 2450 10 2916.67 -467
MILLER 1300 10 2916.67 -1617
JONES 2975 20 2175 800
FORD 3000 20 2175 825
SMITH 800 20 2175 -1375
SCOTT 3000 20 2175 825
ADAMS 1100 20 2175 -1075
BLAKE 2850 30 1566.67 1283
MARTIN 1250 30 1566.67 -317
ALLEN 1600 30 1566.67 33
ENAME SAL DEPTNO DEPTAVGSAL DIFFSALAVG
---------- ---------- ---------- ---------- ----------
TURNER 1500 30 1566.67 -67
JAMES 950 30 1566.67 -617
WARD 1250 30 1566.67 -317
14 rows selected.
SQL> SELECT E.Ename, E.Sal, E.Deptno, E1.SalSum
2 FROM Emp E, (SELECT Deptno, SUM(Sal) SalSUM
3 FROM Emp
4 GROUP BY Deptno) E1
5 WHERE E.Deptno = E1.Deptno
6 ORDER BY Deptno;
ENAME SAL DEPTNO SALSUM
---------- ---------- ---------- ----------
KING 5000 10 8750
CLARK 2450 10 8750
MILLER 1300 10 8750
JONES 2975 20 10875
FORD 3000 20 10875
SMITH 800 20 10875
SCOTT 3000 20 10875
ADAMS 1100 20 10875
BLAKE 2850 30 9400
MARTIN 1250 30 9400
ALLEN 1600 30 9400
ENAME SAL DEPTNO SALSUM
---------- ---------- ---------- ----------
TURNER 1500 30 9400
JAMES 950 30 9400
WARD 1250 30 9400
14 rows selected.
SQL> cl scr
SQL> SELECT T1.Deptno, Dname, Staff
2 FROM Dept T1,
3 (SELECT Deptno, COUNT(*) AS Staff
4 FROM Emp
5 GROUP BY Deptno) T2
6 WHERE T1.Deptno = T2.Deptno
7 AND Staff >= 5;
DEPTNO DNAME STAFF
---------- -------------- ----------
20 RESEARCH 5
30 SALES 6
SQL> SELECT E.Deptno, Dname, COUNT(*) Staff
2 FROM Emp E, Dept D
3 WHERE E.Deptno = D.Deptno
4 GROUP BY E.Deptno, Dname
5 HAVING COUNT(*) >= 5;
DEPTNO DNAME STAFF
---------- -------------- ----------
20 RESEARCH 5
30 SALES 6
SQL> cl scr
SQL> SELECT Deptno, SUM(Sal),
2 SUM(Sal)/Tot_Sal * 100 "Salary%"
3 FROM Emp,
4 (SELECT SUM(Sal) Tot_Sal
5 FROM Emp)
6 GROUP BY Deptno, Tot_Sal;
DEPTNO SUM(SAL) Salary%
---------- ---------- ----------
10 8750 30.1464255
20 10875 37.4677003
30 9400 32.3858742
SQL> SELECT Job, SUM(Sal),
2 ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"
3 FROM Emp, (SELECT SUM(Sal) Tot_Sal
4 FROM Emp)
5 GROUP BY Job, Tot_Sal;
JOB SUM(SAL) Salary%
--------- ---------- ----------
CLERK 4150 14.3
ANALYST 6000 20.67
MANAGER 8275 28.51
SALESMAN 5600 19.29
PRESIDENT 5000 17.23
SQL> cl scr
SQL> SELECT Ename, Sal, SUM(Sal)
2 FROM Emp
3 GROUP BY Ename, Sal;
ENAME SAL SUM(SAL)
---------- ---------- ----------
FORD 3000 3000
KING 5000 5000
WARD 1250 1250
ADAMS 1100 1100
ALLEN 1600 1600
BLAKE 2850 2850
CLARK 2450 2450
JAMES 950 950
JONES 2975 2975
SCOTT 3000 3000
SMITH 800 800
ENAME SAL SUM(SAL)
---------- ---------- ----------
MARTIN 1250 1250
MILLER 1300 1300
TURNER 1500 1500
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal, (SUM(Sal) / OrgSal) * 100
2 FROM Emp, (
3 SELECT SUM(Sal) OrgSal
4 FROM Emp
5
6* GROUP BY Ename, Sal, OrgSal
SQL> /
ENAME SAL (SUM(SAL)/ORGSAL)*100
---------- ---------- ---------------------
FORD 3000 10.3359173
KING 5000 17.2265289
)
WARD 1250 4.30663221
ADAMS 1100 3.78983635
ALLEN 1600 5.51248923
BLAKE 2850 9.81912145
CLARK 2450 8.44099914
JAMES 950 3.27304048
JONES 2975 10.2497847
SCOTT 3000 10.3359173
SMITH 800 2.75624462
ENAME SAL (SUM(SAL)/ORGSAL)*100
---------- ---------- ---------------------
MARTIN 1250 4.30663221
MILLER 1300 4.4788975
TURNER 1500 5.16795866
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Sal,
2 TRUNC((SUM(Sal) / OrgSal) * 100, 2)||' % Share' "%Share"
3 FROM Emp, (
4 SELECT SUM(Sal) OrgSal
5 FROM Emp
6
7* GROUP BY Ename, Sal, OrgSal
SQL> /
ENAME SAL %Share
---------- ---------- ------------------------------------------------
FORD 3000 10.33 % Share
KING 5000 17.22 % Share
WARD 1250 4.3 % Share
ADAMS 1100 3.78 % Share
ALLEN 1600 5.51 % Share
BLAKE 2850 9.81 % Share
CLARK 2450 8.44 % Share
JAMES 950 3.27 % Share
JONES 2975 10.24 % Share
SCOTT 3000 10.33 % Share
SMITH 800 2.75 % Share
ENAME SAL %Share
---------- ---------- ------------------------------------------------
MARTIN 1250 4.3 % Share
MILLER 1300 4.47 % Share
TURNER 1500 5.16 % Share
14 rows selected.
SQL> cl scr
SQL> SELECT TO_CHAR(HireDate, 'YYYY') Year,
2 SUM(Sal),
)
3 ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"
4 FROM Emp, (SELECT SUM(Sal) Tot_Sal
5 FROM Emp)
6 GROUP BY TO_CHAR(HireDate, 'YYYY'), Tot_Sal;
YEAR SUM(SAL) Salary%
---- ---------- ----------
1980 800 2.76
1981 22825 78.64
1982 4300 14.81
1983 1100 3.79
SQL> SELECT
2 TO_CHAR(HireDate, 'YYYY') Year,
3 TO_CHAR(HireDate, 'Month') "Month",
4 SUM(Sal),
5 ROUND(SUM(Sal)/Tot_Sal * 100, 2) "Salary%"
6 FROM Emp, (SELECT SUM(Sal) Tot_Sal
7 FROM Emp)
8 WHERE TO_CHAR(HireDate, 'YYYY') = &GiveYear
9 GROUP BY TO_CHAR(HireDate, 'YYYY'), TO_CHAR(HireDate, 'Month'), Tot_Sal;
Enter value for giveyear: 1981
old 8: WHERE TO_CHAR(HireDate, 'YYYY') = &GiveYear
new 8: WHERE TO_CHAR(HireDate, 'YYYY') = 1981
YEAR Month SUM(SAL) Salary%
---- --------- ---------- ----------
1981 April 2975 10.25
1981 December 3950 13.61
1981 February 2850 9.82
1981 June 2450 8.44
1981 May 2850 9.82
1981 November 5000 17.23
1981 September 2750 9.47
7 rows selected.
SQL> cl scr
SQL> SELECT E.EmpCount, D.DeptCount, S.GradeCnt
2 FROM
3 (SELECT COUNT(*) EmpCount
4 FROM Emp) E,
5 (SELECT COUNT(*) DeptCount
6 FROM Dept) D,
7 (SELECT COUNT(*) GradeCnt FROM
8 SalGrade) S;
EMPCOUNT DEPTCOUNT GRADECNT
---------- ---------- ----------
14 4 5
SQL> SELECT
2 E.EmpCount,
3 D.DeptCount,
4 S.GradeCnt,
5 E.EmpCount + D.DeptCount + S.GradeCnt TotalRecCnt
6 FROM
7 (SELECT COUNT(*) EmpCount
8 FROM Emp) E,
9 (SELECT COUNT(*) DeptCount
10 FROM Dept) D,
11 (SELECT COUNT(*) GradeCnt
12 FROM SalGrade) S;
EMPCOUNT DEPTCOUNT GRADECNT TOTALRECCNT
---------- ---------- ---------- -----------
14 4 5 23
SQL> SELECT
2 E.EmpCount,
3 D.DeptCount,
4 S.GradeCnt,
5 E.EmpCount + D.DeptCount + S.GradeCnt TotalRecCnt,
6 GREATEST(E.EmpCount, D.DeptCount, S.GradeCnt) HighRecinTab,
7 LEAST(E.EmpCount, D.DeptCount, S.GradeCnt) LowRecinTab
8 FROM
9 (SELECT COUNT(*) EmpCount
10 FROM Emp) E,
11 (SELECT COUNT(*) DeptCount
12 FROM Dept) D,
13 (SELECT COUNT(*) GradeCnt
14 FROM SalGrade) S;
EMPCOUNT DEPTCOUNT GRADECNT TOTALRECCNT HIGHRECINTAB LOWRECINTAB
---------- ---------- ---------- ----------- ------------ -----------
14 4 5 23 14 4
SQL> cl scr
SQL> SELECT A.Deptno "Department Number",
2 (A.NumEmp / B.TotalCount ) * 100 "%Employees",
3 (A.SalSum / B.TotalSal ) * 100 "%Salary"
4 FROM
5 (SELECT Deptno, COUNT(*) NumEmp,
6 SUM(Sal) SalSum
7 FROM Emp
8 GROUP BY Deptno) A,
9 (SELECT COUNT(*) TotalCount,
10 SUM(Sal) TotalSal
11 FROM Emp) B;
Department Number %Employees %Salary
----------------- ---------- ----------
10 21.4285714 30.1464255
20 35.7142857 37.4677003
30 42.8571429 32.3858742
.
Correlated Subqueries
This query is a correlated subquery because the inner query refers to columns from the outer query
Steps Performed by the Correlated Subquery
To select the correct records, Oracle performs the following steps.
1. Select a row from the outer query.
2. Determine the value of the correlated column(s).
3. Execute the inner query for each record of the outer query.
4. Feed the result of the inner query to the outer query and evaluate it. If it satisfies the criteria, return the row for output.
5. Select the next record of the outer query and repeat steps 2 through 4 until all the records of the outer query are evaluated
Correlated Subquery Examples
The following examples show the general syntax of a correlated subquery:
SELECT select_list
FROM table1 t_alias1
WHERE expr operator
(SELECT column_list
FROM table2 t_alias2
WHERE t_alias1.column
operator t_alias2.column);
UPDATE table1 t_alias1
SET column =
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);
DELETE FROM table1 t_alias1
WHERE column operator
(SELECT expr
FROM table2 t_alias2
WHERE t_alias1.column = t_alias2.column);
The following statement returns data about employees whose salaries exceed their department average. The following statement assigns
an alias to employees, the table containing the salary information, and then uses the alias in a correlated subquery:
SELECT department_id, last_name, salary
FROM employees x
WHERE salary > (SELECT AVG(salary)
FROM employees
WHERE x.department_id = department_id)
ORDER BY department_id;
For each row of the employees table, the parent query uses the correlated subquery to compute the average salary for members of the
same department. The correlated subquery performs the following steps for each row of the employees table:
1. The department_id of the row is determined.
2. The department_id is then used to evaluate the parent query.
3. If that row's salary is greater than the average salary for that row's department, then the row is returned.
The subquery is evaluated once for each row of the employees table.
The EXISTS Operator
The EXISTS operator is used for correlated subqueries. It tests whether the subquery returns at least one row. The EXISTS operator returns
either true or false, never
unknown. Because EXISTS tests only whether a row exists, the columns shown in the SELECT list of the subquery are irrelevant. Typically,
you use a single-character text literal, such as ‘1’ or ‘X’, or the keyword NULL.
The NOT EXISTS Operator
The NOT EXISTS operator is the opposite of the EXISTS operator; it tests whether a matching row cannot be found. The operator is the
most frequently used type of
correlated subquery construct.
NOT EXISTS versus NOT IN
NOT EXISTS operator tests for NULL values; the NOT IN operator does not.
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp
3 WHERE Sal > (
4 SELECT AVG(Sal)
5 FROM Emp
6 WHERE Deptno = Deptno
7
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, Deptno, Sal
2 FROM Emp OE
3 WHERE OE.Sal > (
4 SELECT AVG(Sal)
5 FROM Emp IE
6 WHERE IE.Deptno = OE.Deptno
7*
SQL> /
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
BLAKE 30 2850
JONES 20 2975
ALLEN 30 1600
FORD 20 3000
SCOTT 20 3000
6 rows selected.
SQL> SELECT Deptno, AVG(Sal) SalAvg
2 FROM Emp
3 GROUP BY Deptno;
DEPTNO SALAVG
---------- ----------
10 2916.66667
20 2175
30 1566.66667
SQL> SELECT Ename, OE.Deptno, Sal
2 FROM Emp OE, (
3 SELECT Deptno, AVG(Sal) SalAvg
4 FROM Emp IE
5 GROUP BY Deptno
6 ) IE
7 WHERE OE.Deptno = IE.Deptno AND
8 OE.Sal > IE.SalAvg;
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
SCOTT 20 3000
FORD 20 3000
JONES 20 2975
ALLEN 30 1600
BLAKE 30 2850
6 rows selected.
SQL> SELECT Ename, Deptno, Sal
2 FROM Emp OE
3 WHERE OE.Sal > (
4 SELECT AVG(Sal)
5 FROM Emp IE
6 WHERE IE.Deptno = OE.Deptno
7 );
ENAME DEPTNO SAL
---------- ---------- ----------
KING 10 5000
BLAKE 30 2850
JONES 20 2975
ALLEN 30 1600
FORD 20 3000
SCOTT 20 3000
6 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT Ename, OE.Deptno, Sal, SalAvg
2 FROM Emp OE, (
3 SELECT Deptno, AVG(Sal) SalAvg
4 FROM Emp IE
5 GROUP BY Deptno
6 ) IE
7 WHERE OE.Deptno = IE.Deptno AND
);
);
8* OE.Sal > IE.SalAvg
SQL> /
ENAME DEPTNO SAL SALAVG
---------- ---------- ---------- ----------
KING 10 5000 2916.66667
SCOTT 20 3000 2175
FORD 20 3000 2175
JONES 20 2975 2175
ALLEN 30 1600 1566.66667
BLAKE 30 2850 1566.66667
6 rows selected.
SQL> cl scr
SQL> SELECT Empno, Ename, E.Deptno, Sal, MGR
2 FROM Emp E
3 WHERE E.Sal > (
4 SELECT M.Sal
5 FROM Emp M
6 WHERE M.Empno = E.MGR
7
EMPNO ENAME DEPTNO SAL MGR
---------- ---------- ---------- ---------- ----------
7902 FORD 20 3000 7566
7788 SCOTT 20 3000 7566
SQL> SELECT Empno, Ename, Sal, MGR
2 FROM Emp
3 WHERE Empno = 7566;
EMPNO ENAME SAL MGR
---------- ---------- ---------- ----------
7566 JONES 2975 7839
SQL> cl scr
SQL> SELECT Deptno, Dname
2 FROM Dept D
3 WHERE EXISTS (
4 SELECT *
5 FROM Emp E
6 WHERE E.Deptno = D.Deptno
7
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
30 SALES
20 RESEARCH

1 SELECT Dept.Deptno, Dname
2 FROM Emp, Dept
3* WHERE Emp.Deptno = Dept.Deptno
SQL> /
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
30 SALES
10 ACCOUNTING
20 RESEARCH
30 SALES
30 SALES
30 SALES
30 SALES
30 SALES
20 RESEARCH
20 RESEARCH
DEPTNO DNAME
---------- --------------
20 RESEARCH
20 RESEARCH
10 ACCOUNTING
14 rows selected.
SQL> ED
Wrote file afiedt.buf
1 SELECT DISTINCT Dept.Deptno, Dname
2 FROM Emp, Dept
3* WHERE Emp.Deptno = Dept.Deptno
SQL> /
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
SQL> ED
Wrote file afiedt.buf
1 SELECT Dept.Deptno, Dname
2 FROM Emp, Dept
3 WHERE Emp.Deptno = Dept.Deptno
4* GROUP BY Dept.Deptno, Dname
SQL> /
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
SQL> SELECT Dept.Deptno, Dname
2 FROM Dept
3 WHERE Dept.Deptno IN (
4 SELECT Deptno
5 FROM Emp
6*
SQL> /
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
30 SALES
20 RESEARCH
SQL> cl scr
SQL> SELECT Deptno, Dname
2 FROM Dept D
3 WHERE NOT EXISTS (
4 SELECT *
5 FROM Emp E
6 WHERE E.Deptno = D.Deptno
7
DEPTNO DNAME
---------- --------------
40 OPERATIONS
SQL> cl scr
SQL> SELECT E.Ename
2 FROM Emp E
3 WHERE EXISTS (
4 SELECT *
5 FROM Emp E1
6 WHERE E1.Empno = E.Mgr
7
ENAME
----------
BLAKE
CLARK
JONES
)
);
);
);
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
SCOTT
ENAME
----------
ADAMS
MILLER
13 rows selected.
SQL> SELECT Ename FROM Emp WHERE MGR IS NOT NULL;
ENAME
----------
BLAKE
CLARK
JONES
MARTIN
ALLEN
TURNER
JAMES
WARD
FORD
SMITH
SCOTT
ENAME
----------
ADAMS
MILLER
13 rows selected.
SQL> cl scr
SQL> SELECT E.Ename
2 FROM Emp E
3 WHERE NOT EXISTS (
4 SELECT *
5 FROM Emp E1
6 WHERE E1.Empno = E.Mgr
7
ENAME
----------
KING
SQL> SELECT E.Ename
2 FROM Emp E
3 WHERE EXISTS (
4 SELECT *
5 FROM Emp E1
6 WHERE E1.Mgr = E.Empno
7
ENAME
----------
KING
BLAKE
JONES
FORD
SCOTT
CLARK
6 rows selected.
SQL> SELECT E.Ename
2 FROM Emp E
3 WHERE NOT EXISTS (
4 SELECT *
5 FROM Emp E1
6 WHERE E1.Mgr = E.Empno
7
ENAME
----------
TURNER
WARD
MARTIN
ALLEN
MILLER
SMITH
ADAMS
JAMES
8 rows selected.
SQL> SPOOL OFF
);
);
Difference between Correlated and NonCorrelated Subquery
Question: I want to learn the difference between a correlated subquery and a non-correlated subquery.
Answer: A correlated subquery is a subquery that uses values from the outer query, requiring the inner query to execute once for
each outer query
The Oracle database wants to execute the subquery once and use the results for all the evaluations in the outer query. With a
correlated subquery, the database must run the subquery for each evaluation because it is based on the outer query’s data.
Now we have seen correlated and noncorrelated subqueries and there example its much easier to understand difference between
correlated vs noncorrelated queries. By the way this is also one of the popular sql interview question and its good to know few
differences:
1.In case of correlated subquery inner query depends on outer query while in case of noncorrelated query inner query or subquery
doesn't depends on outer query and run by its own.
2.In case of correlated subquery, outer query executed before inner query or subquery while in case of NonCorrelated subquery inner
query executes before outer query.
3.Correlated Sub-queries are slower than non correlated subquery and should be avoided in favor of sql joins.
4.Common example of correlated subquery is using exits and not exists keyword while non correlated query mostly use IN or NOT IN
keywords.
Question: I have a complex SQL statements with two correlated subqueries that runs very slow:
The correlated subqueries are making this SQL very slow to execute. How do I tune a correlated subquery to make it run faster?
Answer: Correlated subqueries are usually used for EXISTS Booleans, and scalar subqueries
Correlated subqueries and slow because the sub-query is executed ONCE for each row returned by the outer query.
There are several ways to tune a correlated subquery:
 Query rewrite: Inspect the correlated subquery execution plan for the subqueries, and see if the explain plan is re-writing
the correlated subquery internally (set query_rewrite_enabled = true) into a more efficient form, a standard join.
 Check indexes: If you MUST use a correlated subquery, MAKE SURE that each of the referenced columns in the subquery
has an index! Without indexes on the correlated subquery columns, the correlated subquery might be forced to do an
expensive full-table scan, executed over and over, once for each outer row returned.
 Replace correlated query with a standard join: See if you can replicate the output without the subqueries, using vanilla
joins.
 Un-nest the subqueries: Oracle allows you to add a hint that will un-nest the correlated subquery.
 Materialize the correlated subqueries with global temporary tables of the WITH clause: See if you can create temporary
tables to represent the contents of the subquery. If you break down the query into its component pieces and use the WITH
clause, it may run faster.
Subquery Performance Considerations--
When you initially learn SQL, your utmost concern must be to obtain the correct result. Performance considerations should be
secondary. However, as you get more
experienced with the language or as you work with large data sets, you want to consider some of the effects of your constructed
statements. As you have seen, sometimes
you can achieve the same result with a join, a correlated subquery, or a noncorrelated subquery.
As previously mentioned, under specific circumstances, Oracle may automatically optimize your statement and implicitly transform
your subquery to a join. This implicit
transformation frequently results in better performance without your having to worry about applying any optimization techniques.
Performance benefits of one type of subquery over another type may be noticeable when you are working with very large volumes of
data. To optimize subqueries, you must
understand the key difference between correlated and noncorrelated subqueries.
A correlated subquery evaluates the inner query for every row of the outer query. Therefore, your optimization strategy should focus
on eliminating as many rows as possible
from the outer query. You can do this by adding additional restricting criteria in the WHERE clause of the statement. The advantage of
correlated subqueries is that they
can use indexes on the correlated columns, if any exist.
A noncorrelated subquery executes the inner query first and then feeds this result to the outer query. The inner query is executed
once. Generally speaking, this query is
best suited for situations in which the inner query does not returns a very large result set and where no indexes exist on the
compared columns.
If your query involves a NOT EXISTS condition, you cannot modify it to a NOT IN condition if the subquery can return null values. In
many circumstances, NOT EXISTS
offers better performance because indexes are usually used.
Because the STUDENT schema contains a fairly small number of records, the difference in execution time is minimal. The illustrated
various solutions throughout this book allow you to look at different ways to approach and solve problems. You might want to use
those solutions as starting points for ideas and perform your own tests, based on your distinct environment, data volume, and
requirements.
Nesting Multiple Subqueries
You can nest one subquery within another subquery. The innermost query is always evaluated first, then the next highest one, and
so on. The result of each subquery is fed into the enclosing statement.
Subqueries and Joins
A subquery that uses the IN or = operator can often be expressed as an equijoin if the subquery does not contain an aggregate
function
SELECT course_no, description
FROM course
WHERE course_no IN
(SELECT course_no
FROM section
WHERE location = 'L211')
COURSE_NO DESCRIPTION
--------- -----------------------------
142 Project Management
125 Java Developer I
122 Intermediate Java Programming
3 rows selected.
The following is the same query now expressed as an equijoin.
SELECT c.course_no, c.description
FROM course c, section s
WHERE c.course_no = s.course_no
AND s.location = 'L211'
Subqueries and Nulls-
One easily overlooked behavior of subqueries is the occurrence of null values
If you translate the result of the subquery into a list of values, you see the same result: No rows are returned from the query because the condition evaluates to unknown
when any value in the list is a null.
SELECT course_no, prerequisite
FROM course
WHERE prerequisite NOT IN (80, NULL)
no rows selected
therefore, you must be aware of any NOT IN operator subqueries that can potentially return null values.
The way to solve this null dilemma is to use the NOT EXISTS
The NVL and COALESCE functions are useful in dealing with null values. You can substitute a default value and apply the function to
both the subquery and the WHERE
clause condition
ORDER BY Clause in Subqueries
he ORDER BY clause is not allowed inside a subquery. If you attempt to include an ORDER BY clause, you receive an error message
SELECT course_no, description, cost
FROM course
WHERE cost IN
(SELECT cost
FROM course
WHERE prerequisite = 420
ORDER BY cost)
ORDER BY cost)
*
ERROR at line 7:
ORA-00907: missing right parenthesis
It is not immediately apparent where the problem lies unless you already know about this rule. The message essentially indicates that
an ORDER BY clause is not permitted in a subquery and that Oracle is expecting to see the right parenthesis, signifying the closing of
the subquery. An ORDER BY clause is certainly valid for the outer query—just not for the nested subquer
the DISTINCT keyword in the subquery. This keyword is not required and does not alter the
result, nor does it change the efficiency of the execution. Oracle automatically eliminates duplicates in a list of values as a result of
the subquery.
***********************There is no need to add a table alias to the subquery. Table aliases in subqueries are typically used only in
correlated subqueries or in subqueries that contain joins.
Selecting from the DUAL Table
DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is
accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value
X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only
one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table,
but the value will be returned as many times as there are rows in the table. Please refer to "SQL Functions" for many examples of
selecting a constant value from DUAL.