Subqueries - Hochschule Anhalt

Using a Subquery to Solve a Problem
•
“Who has a salary greater than Jones’s?”
Main Query
?
“Which employees have a salary greater
than Jones’s salary?”
Subquery
?
“What is Jones’s salary?”
DBS1_2004
SQL (6)
Seite 1
Klöditz
Hochschule Anhalt (FH)
Subqueries
•
The subquery (inner query) executes once before the main
query
•
The result of the subquery is used by the main query (outer
query)
SELECT
FROM
WHERE
Klöditz
Hochschule Anhalt (FH)
select_list
table
expr operator
(SELECT
FROM
select_list
table);
DBS1_2004
SQL (6)
Seite 2
Using a Subquery
SQL> SELECT ename
2 FROM
emp
2975
3 WHERE sal >
4
(SELECT sal
5
FROM
emp
6
WHERE empno=7566);
ENAME
---------KING
FORD
SCOTT
DBS1_2004
SQL (6)
Seite 3
Klöditz
Hochschule Anhalt (FH)
Guidelines for Using Subqueries
•
Enclose subqueries in parentheses
•
•
•
•
Place subqueries on the right side of the comparison operator
Do not add an ORDER BY clause to a subquery
Use single-row operators with single-row subqueries
Use multiple-row operators with multiple-row subqueries
Klöditz
Hochschule Anhalt (FH)
DBS1_2004
SQL (6)
Seite 4
Types of Subqueries
•
Single-row subquery
Main query
Subquery
•
returns
CLERK
Multiple-row subquery
Main query
Subquery
•
returns
CLERK
MANAGER
returns
CLERK
7900
MANAGER 7698
Multiple-column subquery
Main query
Subquery
DBS1_2004
SQL (6)
Seite 5
Klöditz
Hochschule Anhalt (FH)
Single-Row Subqueries
•
Return only one row
•
Use single-row comparison operators
Operator
Meaning
=
Equal to
>
Greater than
>=
<
Klöditz
Hochschule Anhalt (FH)
Greater than or equal to
Less than
>=
Less than or equal to
<>
Not equal to
DBS1_2004
SQL (6)
Seite 6
Executing Single-Row Subqueries
SQL>
2
3
4
5
6
7
8
9
10
SELECT
FROM
WHERE
AND
ename, job
emp
job =
(SELECT
FROM
WHERE
sal >
(SELECT
FROM
WHERE
CLERK
job
emp
empno = 7369)
1100
sal
emp
empno = 7876);
ENAME
JOB
---------- --------MILLER
CLERK
DBS1_2004
SQL (6)
Seite 7
Klöditz
Hochschule Anhalt (FH)
Using Group Functions in a Subquery
SQL> SELECT
2 FROM
3 WHERE
4
5
ename, job, sal
emp
sal =
(SELECT
FROM
800
MIN(sal)
emp);
ENAME
JOB
SAL
---------- --------- --------SMITH
CLERK
800
Klöditz
Hochschule Anhalt (FH)
DBS1_2004
SQL (6)
Seite 8
HAVING Clause with Subqueries
•
The Oracle Server executes subqueries first
•
The Oracle Server returns results into the main query’s HAVING
clause
SQL>
2
3
4
5
6
7
SELECT
FROM
GROUP BY
HAVING
deptno, MIN(sal)
emp
deptno
MIN(sal) >
(SELECT
FROM
WHERE
800
MIN(sal)
emp
deptno = 20);
DBS1_2004
SQL (6)
Seite 9
Klöditz
Hochschule Anhalt (FH)
What Is Wrong with This Statement?
y
er
u
SQL> SELECT empno, ename
bq
2 FROM
emp
u
s
3 WHERE sal =
w
o
4
(SELECT
MIN(sal)
-r
e
5
FROM
emp
l
p
6
GROUP BYulti deptno);
m
ERROR:
ith
ORA-01427: single-row subquery
returns more than
w
r
one row
to
no rows selected
Klöditz
Hochschule Anhalt (FH)
le
ng
i
S
w
-ro
a
er
p
o
DBS1_2004
SQL (6)
Seite 10
Will This Statement Work?
SQL> SELECT ename,
2 FROM
emp
3 WHERE job =
4
5
6
no rows selected
job
es
u
l
va
(SELECT job
no
FROM
emp
s
WHERE
ename='SMYTHE');
rn
tu
re
y
er
u
bq
Su
DBS1_2004
SQL (6)
Seite 11
Klöditz
Hochschule Anhalt (FH)
Multiple -Row Subqueries
•
Return more than one row
•
Use multiple-row comparison operators
Operator
IN
Klöditz
Hochschule Anhalt (FH)
Meaning
Equal to any member in the list
ANY
Compare value to each value returned
by the subquery
ALL
Compare value to every value returned
by the subquery
DBS1_2004
SQL (6)
Seite 12
Using ANY Operator in Multiple-Row Subqueries
SQL>
2
3
4
5
6
7
SELECT
FROM
WHERE
AND
EMPNO
--------7654
7521
empno, ename, job 1300
1100
emp
800
sal < ANY
950
(SELECT
sal
FROM
emp
WHERE
job = 'CLERK')
job <> 'CLERK';
ENAME
---------MARTIN
WARD
JOB
--------SALESMAN
SALESMAN
DBS1_2004
SQL (6)
Seite 13
Klöditz
Hochschule Anhalt (FH)
Using ALL Operator in Multiple -Row Subqueries
SQL> SELECT
2 FROM
3 WHERE
4
5
6
EMPNO
--------7839
7566
7902
7788
Klöditz
Hochschule Anhalt (FH)
empno, ename, job
emp
2175
2916.6667
sal > ALL
(SELECT
FROM
GROUP BY
ENAME
---------KING
JONES
FORD
SCOTT
1566.6667
avg(sal)
emp
deptno)
JOB
--------PRESIDENT
MANAGER
ANALYST
ANALYST
DBS1_2004
SQL (6)
Seite 14