6
Subqueries
Copyright
Oracle Corporation, 1998. All rights reserved.
Objectives
After completing this lesson, you should
be able to do the following:
Describe the types of problems that
subqueries can solve
Define subqueries
List the types of subqueries
Write single-row and multiple-row
subqueries
6-2
Copyright
Oracle Corporation, 1998. All rights reserved.
Using a Subquery
to Solve a Problem
Who has a salary greater than Jones?
Main Query
Which employees have a salary greater
than Jones salary?
Subquery
6-3
Copyright
What is Jones salary?
Oracle Corporation, 1998. All rights reserved.
Subqueries
SELECT
FROM
WHERE
select_list
table
expr operator
(SELECT
FROM
select_list
table);
The subquery (inner query) executes once before the main query.
The result of the subquery is used by the main query (outer
query).
6-4
Copyright
Oracle Corporation, 1998. All rights reserved.
Using a Subquery
SQL> SELECT ename
2 FROM
emp
2975
3 WHERE sal >
4
(SELECT sal
5
FROM
emp
6
WHERE empno=7566);
ENAME
ENAME
------------------KING
KING
FORD
FORD
SCOTT
SCOTT
6-5
Copyright
Oracle Corporation, 1998. All rights reserved.
Guidelines for Using Subqueries
Enclose subqueries in parentheses.
Place subqueries on the right side of the
comparison operator.
Use single-row operators with singlerow subqueries.
Use multiple-row operators with
multiple-row subqueries.
6-6
Copyright
Oracle Corporation, 1998. All rights reserved.
Types of Subqueries
Single-row subquery
Main query
Subquery
returns
CLERK
Multiple-row subquery
Main query
Subquery
returns
CLERK
MANAGER
Multiple-column subquery
Main query
Subquery
6-7
Copyright
returns
CLERK
7900
MANAGER 7698
Oracle Corporation, 1998. All rights reserved.
Single-Row Subqueries
Return only one row
Use single-row comparison operators
Operator
6-8
Meaning
Equal to
>
Greater than
>=
Greater than or equal to
<
Less than
<=
Less than or equal to
<>
Not equal to
Copyright
Oracle Corporation, 1998. All rights reserved.
Executing Single-Row Subqueries
SQL>
2
3
4
5
6
7
8
9
10
SELECT
FROM
WHERE
AND
ENAME
ENAME
------------------MILLER
MILLER
6-9
ename, job
emp
job =
(SELECT
FROM
WHERE
sal >
(SELECT
FROM
WHERE
CLERK
job
emp
empno = 7369)
1100
sal
emp
empno = 7876);
JOB
JOB
----------------CLERK
CLERK
Copyright
Oracle Corporation, 1998. All rights reserved.
Using Group Functions
in a Subquery
SQL> SELECT
2 FROM
3 WHERE
4
5
ename, job, sal
emp
sal =
(SELECT
FROM
ENAME
ENAME
------------------SMITH
SMITH
JOB
SAL
JOB
SAL
----------------- ----------------CLERK
800
CLERK
800
6-10
Copyright
800
MIN(sal)
emp);
Oracle Corporation, 1998. All rights reserved.
HAVING Clause with Subqueries
The Oracle Server executes subqueries
first.
The Oracle Server returns results into
the HAVING clause of the main query.
SQL>
2
3
4
5
6
7
6-11
SELECT
FROM
GROUP BY
HAVING
Copyright
deptno, MIN(sal)
emp
deptno
MIN(sal) >
(SELECT
FROM
WHERE
800
MIN(sal)
emp
deptno = 20);
Oracle Corporation, 1998. All rights reserved.
What Is Wrong
with This Statement?
SQL> SELECT empno, ename
2 FROM
emp
3 WHERE sal =
4
h(SELECT
t
i
w
or
5
FROM
t
a
r
e
y
6 ow o p
quer GROUP BY
b
-r
u
e
l
s
g
Sin ple-row
i
t
l
u
m
MIN(sal)
emp
deptno);
ERROR:
ERROR:
ORA-01427:
ORA-01427: single-row
single-row subquery
subquery returns
returns more
more than
than
one
one row
row
no
no rows
rows selected
selected
6-12
Copyright
Oracle Corporation, 1998. All rights reserved.
Will This Statement Work?
SQL> SELECT ename,
2 FROM
emp
3 WHERE job =
4
5
6
no
no rows
rows selected
selected
job
(SELECT job
FROM
emp
WHERE
ename='SMYTHE');
ry
e
u
q
b
u
S
6-13
Copyright
s
e
u
al
v
no
s
rn
u
t
re
Oracle Corporation, 1998. All rights reserved.
Multiple-Row Subqueries
Return more than one row
Use multiple-row comparison operators
Operator
Meaning
IN
Equal to any member in the list
ANY
Compare value to each value returned by
the subquery
Compare value to every value returned by
the subquery
ALL
6-14
Copyright
Oracle Corporation, 1998. All rights reserved.
Using ANY Operator
in Multiple-Row Subqueries
SQL>
2
3
4
5
6
7
SELECT
FROM
WHERE
AND
EMPNO
EMPNO
----------------7654
7654
7521
7521
6-15
empno, ename, job 1300
1100
emp
800
950
sal < ANY
(SELECT
sal
FROM
emp
WHERE
job = 'CLERK')
job <> 'CLERK';
ENAME
ENAME
------------------MARTIN
MARTIN
WARD
WARD
Copyright
JOB
JOB
----------------SALESMAN
SALESMAN
SALESMAN
SALESMAN
Oracle Corporation, 1998. All rights reserved.
Find the employee names who get
the department wise lowest salary
Select ename from emp
Where sal in (Select min(sal) from emp
Group by deptno);
6-16
Copyright
Oracle Corporation, 1998. All rights reserved.
Using ALL Operator
in Multiple-Row Subqueries
SQL> SELECT
2 FROM
3 WHERE
4
5
6
EMPNO
EMPNO
----------------7839
7839
7566
7566
7902
7902
7788
7788
6-17
empno, ename, job 1566.6667
2175
emp
2916.6667
sal > ALL
(SELECT
avg(sal)
FROM
emp
GROUP BY
deptno);
ENAME
ENAME
------------------KING
KING
JONES
JONES
FORD
FORD
SCOTT
SCOTT
Copyright
JOB
JOB
----------------PRESIDENT
PRESIDENT
MANAGER
MANAGER
ANALYST
ANALYST
ANALYST
ANALYST
Oracle Corporation, 1998. All rights reserved.
Summary
Subqueries are useful when a query is
based on unknown values.
SELECT
FROM
WHERE
6-18
select_list
table
expr operator
(SELECT select_list
FROM
table);
Copyright
Oracle Corporation, 1998. All rights reserved.
Practice Overview
Creating subqueries to query values based on unknown criteria
Using subqueries to find out what values exist in one set of data and not in another
6-19
Copyright
Oracle Corporation, 1998. All rights reserved.
