Advanced Oracle Database Query

Nesting another SQL statement in an SQL statement is called a subquery.

This query statement is used as a condition for another query statement. The SQL statement that contains other SQL statements is called the parent query.

Example:

If you want to query all the product IDs, names, and prices of the books in the product category

1. SelectId,Name, PriceFromEs_product
2. WhereSort_id = (
3. SelectIdFromEs_sortWhereSortname =
4. 'Book')

The general implementation steps of the where condition subquery are as follows:
1. We must first know what to query and specify the results to be displayed.

For example, the above is id, name, price

2. Where does the result come from?

Apparently, the information about these products comes from the es_product table.

3. What are the required query conditions?

Here, you need to query books by category, that is, sort_id = (book category id)

4. Where does the value required in the condition come from?

It comes from a subquery.

Sort_id = (SELECT id FROM es_sort WHERE sortname = 'book ')

5. the SQL statement of the subquery comes out naturally.

For example, we want to query the order number, receipt method, and order status of the person who uses Li qingqing as the next person.

1. explicitly display the result id, payment, status

2. Where is the result es_order?

3. The required condition user_id = (id of 'Li qingqing)

4. the value required for this condition comes from a subquery.

User_id = (select id from es_user where realname = 'Li qingqing ')

The subqueries mentioned above all return single row results, so the = Operator is used,

In fact, we can use >\>=\\<=\> \! = Operator

For example, you need to write an SQL statement to query the id, name, and price of a product that is greater than the average price of the product.

1. SelectId,Name, PriceFromEs_product
2. WherePrice> (Select Avg(Price)AsAverage commodity priceFromEs_product) 

However, sometimes a subquery may return more than one row.

For example, you need to query all the product IDs, product names, and prices under Order 1.

When we use the = Operator, an error is reported.

We should make such changes

Change the = Operator to in, that is

1. SelectId,Name, PriceFromEs_product
2. WhereIdIn(SelectProd_idFromEs_orderdetailWhereOrder_id = 1)

In addition to using in, we can also use any, all, exists

In addition, we need to query the id, name, and shelving time of the first five items in the product table.

To meet this requirement, we must first know the ROWNUM content.

When ROWNUM is ready, the SQL statement will be available.

1. SelectId,Name, SaledateFromEs_productWhereRownum <= 5

Demand continues to upgrade. This time, we will query the records of the first five items on the latest shelves, which may be written in this way.

However, this is incorrect because

Select execution sequence

It always executes the where clause before executing the order by statement.

However, we need to first sort the table records and then retrieve the first five records.

The idea is as follows:

If there is sorting, first treat the sorted table as a virtual table and then operate

The preceding SQL statements are as follows:

1. SelectId,Name, Saledate, rownumFrom(Select*FromEs_productOrder BySaledateDesc)WhereRownum <= 5

select id,name,saledate,rownum from(select * from es_product order by saledate desc) where rownum<=5

Remember that rownum is dynamically generated and does not exist in the table physical, so such as saledate. rownum does not exist.

1 2 3 4 5 Next Page

[Content navigation]
Page 1: subquery	Page 1: querying by PAGE
Page 3rd: Connection Query	Page 4th: Comprehensive cases of subqueries and connection queries
Page 5th: Set Query