![Page 1: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/1.jpg)
Introduction to Oracle9i: SQL 1
Subqueries
![Page 2: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/2.jpg)
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
![Page 3: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/3.jpg)
Introduction to Oracle9i: SQL 3
Chapter Objectives
• Distinguish between single-row and multiple-row comparison operators
• Use a multiple-row subquery in a WHERE clause
• Use a multiple-row subquery in a HAVING clause
• Use a multiple-column subquery in a WHERE clause
![Page 4: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/4.jpg)
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
• Nest a subquery inside another subquery
![Page 5: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/5.jpg)
Introduction to Oracle9i: SQL 5
Subquery
• Used when query is based on unknown value
• A query nested inside another query
• Requires SELECT and FROM clauses
• Must be enclosed in parentheses
• Place on right side of comparison operator
![Page 6: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/6.jpg)
Introduction to Oracle9i: SQL 6
Types of Subqueries
![Page 7: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/7.jpg)
Introduction to Oracle9i: SQL 7
Single-Row Subquery Operators
• Can only return one result to outer query
• Operators include =, >, <, >=, <=, < >
![Page 8: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/8.jpg)
Introduction to Oracle9i: SQL 8
Single-Row Subquery – In WHERE Clause
Used for comparison against individual data
![Page 9: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/9.jpg)
Introduction to Oracle9i: SQL 9
Single-Row Subquery – In HAVING Clause
Required when returned value is compared to grouped data
![Page 10: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/10.jpg)
Introduction to Oracle9i: SQL 10
Single-Row Subquery – In SELECT Clause
Replicates subquery value for each row displayed
![Page 11: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/11.jpg)
Introduction to Oracle9i: SQL 11
Multiple-Row Subqueries
• Return more than one row of results
• Require use of IN, ANY, ALL, or EXISTS operators
![Page 12: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/12.jpg)
Introduction to Oracle9i: SQL 12
ANY and ALL Operators
Combine with arithmetic operators
![Page 13: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/13.jpg)
Introduction to Oracle9i: SQL 13
EXISTS Operator
Determines whether condition exists in subquery
![Page 14: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/14.jpg)
Introduction to Oracle9i: SQL 14
Multiple-Row Subquery – In WHERE Clause
![Page 15: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/15.jpg)
Introduction to Oracle9i: SQL 15
Multiple-Row Subquery – In HAVING Clause
![Page 16: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/16.jpg)
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
![Page 17: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/17.jpg)
Introduction to Oracle9i: SQL 17
Multiple-Column Subquery – In FROM Clause
Creates temporary table
![Page 18: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/18.jpg)
Introduction to Oracle9i: SQL 18
Multiple-Column Subquery –In WHERE Clause
Returns multiple columns for evaluation
![Page 19: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/19.jpg)
Introduction to Oracle9i: SQL 19
NULL Values
When subquery might return NULL values, use NVL function
![Page 20: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/20.jpg)
Introduction to Oracle9i: SQL 20
Uncorrelated Subqueries
• Processing sequence:– Inner query executed first– Result passed to outer query– Outer query executed
![Page 21: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/21.jpg)
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
![Page 22: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/22.jpg)
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.
![Page 23: Introduction to Oracle9i: SQL1 Subqueries. Introduction to Oracle9i: SQL2 Chapter Objectives Determine when it is appropriate to use a subquery Identify](https://reader035.vdocuments.us/reader035/viewer/2022062516/56649d355503460f94a0d19d/html5/thumbnails/23.jpg)
Introduction to Oracle9i: SQL 23
Nested Subquery Example
Innermost resolved first (3), then second level (2), then outer query (1)