【Oracle 官方技术支持】Les18_高级子查询.ppt

Hidden Slide Schedule: Timing Topic 60 minutes Lecture 50 minutes Practice 110 minutes Total Lesson Aim In this lesson, you learn how to write multiple-column subqueries and subqueries in the FROM clause of a SELECT statement. You also learn how to solve problems by using scalar, correlated subqueries and the WITH clause. What Is a Subquery? A subquery is a SELECT statement that is embedded in a clause of another SQL statement, called the parent statement. The subquery (inner query) returns a value that is used by the parent statement. Using a nested subquery is equivalent to performing two sequential queries and using the result of the inner query as the search value in the outer query (main query). Subqueries can be used for the following purposes: To provide values for conditions in WHERE, HAVING, and START WITH clauses of SELECT statements To define the set of rows to be inserted into the target table of an INSERT or CREATE TABLE statement To define the set of rows to be included in a view or snapshot in a CREATE VIEW or CREATE SNAPSHOT statement To define one or more values to be assigned to existing rows in an UPDATE statement To define a table to be operated on by a containing query. (You do this by placing the subquery in the FROM clause. This can be done in INSERT, UPDATE, and DELETE statements as well.) Note: A subquery is evaluated once for the entire parent statement. Instructor Note You can skip this slide if the students are already familiar with these concepts. Subqueries You can build powerful statements out of simple ones by using subqueries. Subqueries can be very useful when you need to select rows from a table with a condition that depends on the data in the table itself or some other table. Subqueries are very useful for writing SQL statements that need values based on one or more unknown conditional values. In the syntax: operator includes a comparison operator such as , =, or IN Note: Comparison operators fall into two classe