Oracle Chapter 13th when one query relies on another query

Advanced subquery subquery (subquery)

Knowledge Point one: the relationship between multi-table joins

For example, want to know which kind of books have been lent out.

Select DISTINCT C.parent_catagory, c.sub_category

From category C, Bookshelf b, bookshelf_checkout BC

where c.category_name = b.category_name and B.title = Bc.title

Distinct keywords go to weight

The relationship between the 3 table associations in this example: to connect the 3 table, you must concatenate two of the tables with the third table.

The 1.CATEGORY table is connected to the Bookshelf table, and the results of their connection are then connected to the Bookshelf_checkout table. Distinct sentence to go heavy

Note: Not every table is connected to every other table. The connection between tables is typically 1 less than the number of tables connected.

Knowledge Point two: correlated subqueries

A.where subqueries where the clause can contain subqueries of a SELECT statement, where the clauses of a subquery can also be nested

Select DISTINCT C.parent_categorty, c.sub_category

From Categort C

where Category_name in

(Select Category_name from Bookshelf

where title in

(select title from Bookshelf_checkout)

);

This query is the same as the query results in the knowledge point one

A: A subquery can reference a column of a table used in its main query (that is, the following example does not have a title column in the Bookshelf table)

Select title from Bookshelf_author

where title in

(select title from Bookshelf

where author_name = ' Setphena hay Gould ')

Oracle Chapter 13th when one query relies on another query