8/6/2019 Chap Sub Queries

1/23

Introduction to Oracle9i: SQL 1

Subqueries

8/6/2019 Chap Sub Queries

2/23

Introduction to Oracle9i: SQL 2

Chapter Objectives Determine when it is appropriate to use a subquery

Identify which clauses can contain subqueries

Distinguish between an outer query and a

subquery

Use a single-row subquery in a WHERE clause

Use a single-row subquery in a HAVING clause Use a single-row subquery in a SELECT clause

8/6/2019 Chap Sub Queries

3/23

Introduction to Oracle9i: SQL 3

Chapter Objectives Distinguish between single-row and multiple-

row comparison operators

Use a multiple-row subquery in a WHEREclause

Use a multiple-row subquery in a HAVINGclause

Use a multiple-column subquery in a WHEREclause

8/6/2019 Chap Sub Queries

4/23

Introduction to Oracle9i: SQL 4

Chapter Objectives Create an inline view using a multiple-

column subquery in a FROM clause

Compensate for NULL values in subqueries

Distinguish between correlated and

uncorrelated subqueries

Nesta subquery inside another subquery

8/6/2019 Chap Sub Queries

5/23

Introduction to Oracle9i: SQL 5

Subquery Used when query is based on unknown

value

A query nested inside another query

Requires SELECTand FROM clauses

Must be enclosed in parentheses

Place on right side of comparison operator

8/6/2019 Chap Sub Queries

6/23

Introduction to Oracle9i: SQL 6

Types of Subqueries

8/6/2019 Chap Sub Queries

7/23

Introduction to Oracle9i: SQL 7

Single-Row Subquery Operators Can only return one resultto outer query

Operators include =, >, =,

8/6/2019 Chap Sub Queries

8/23

Introduction to Oracle9i:

SQL 8

Single-Row Subquery

In WHERE ClauseUsed for comparison against individual data

8/6/2019 Chap Sub Queries

9/23

Introduction to Oracle9i:

SQL 9

Single-Row Subquery

In HAVING ClauseRequired when returned value is compared

to grouped data

8/6/2019 Chap Sub Queries

10/23

Introduction to Oracle9i:

SQL 10

Single-Row Subquery

In SELECTClauseReplicates subquery value for each row displayed

8/6/2019 Chap Sub Queries

11/23

Introduction to Oracle9i:

SQL 11

Multiple-Row Subqueries Return more than one row of results

Require use of IN, ANY, ALL, or EXISTS

operators

8/6/2019 Chap Sub Queries

12/23

Introduction to Oracle9i:

SQL 12

ANY and ALL OperatorsCombine witharithmetic operators

8/6/2019 Chap Sub Queries

13/23

Introduction to Oracle9i: SQL 13

EXISTS OperatorDetermines whether condition exists in subquery

8/6/2019 Chap Sub Queries

14/23

Introduction to Oracle9i: SQL 14

Multiple-Row Subquery

In WHERE Clause

8/6/2019 Chap Sub Queries

15/23

Introduction to Oracle9i: SQL 15

Multiple-Row Subquery

In HAVING Clause

8/6/2019 Chap Sub Queries

16/23

Introduction to Oracle9i: SQL 16

Multiple-Column Subquery Returns more than one column in results

Can return more than one row

Column list on left side of operator must be

in parentheses

Uses IN operator for WHERE and

HAVING clauses

8/6/2019 Chap Sub Queries

17/23

Introduction to Oracle9i: SQL 17

Multiple-Column Subquery

In FROM ClauseCreates temporary table

8/6/2019 Chap Sub Queries

18/23

Introduction to Oracle9i: SQL 18

Multiple-Column Subquery

In WHERE ClauseReturns multiple columns for evaluation

8/6/2019 Chap Sub Queries

19/23

Introduction to Oracle9i: SQL 19

NULL ValuesWhen subquery might return NULL values, use NVL function

8/6/2019 Chap Sub Queries

20/23

Introduction to Oracle9i: SQL 20

Uncorrelated Subqueries Processing sequence:

Inner query executed first

Resultpassed to outer query

Outer query executed

8/6/2019 Chap Sub Queries

21/23

Introduction to Oracle9i: SQL 21

Correlated Subqueries Inner query executed once for each row

processed by outer query

Inner query references row contained in

outer query

8/6/2019 Chap Sub Queries

22/23

Introduction to Oracle9i: SQL 22

Nested Subqueries Maximum 255 subqueries if nested in

WHERE clause

No limit if nested in FROM clause

Innermost subquery resolved first, then next

level, etc.

8/6/2019 Chap Sub Queries

23/23

Introduction to Oracle9i: SQL 23

Nested Subquery ExampleInnermost resolved first (3), then second level (2),

then outer query (1)