Oracle Subquery

Subquery ： Queries that embed other kinds of queries

Be careful ：

Inquire about （Query）: whatever SQL sentence . however , This is a term commonly used to refer to SELECT sentence .

1、 Filter through subqueries

All tables demonstrated in this section are relational tables . The order data is stored in two tables . stay Order In the table , Each order is stored in a separate line , This includes the order number 、 customer ID And order date . Each order item is stored in the relevant orderitems In the table .order The table does not store customer information , He only stores customers ID. The actual customer information is stored in customers In the table .

Now suppose you want to order goods TNT2 List of all customers . How to retrieve information ？ The operation steps are given below .

1. Search includes items TNT2 The order number of all orders for .

2. Retrieve all customers with orders listed in the order number returned in the previous step ID.

3. For all customers returned in the previous step ID Retrieve relevant customer information .

   Each of these steps can be executed as a separate query . By doing so , You can use a SELECT Statement to act as the next SELECT Of the statement WHERE Condition of clause .

   You can also use subqueries to put all 3 Queries are combined into a single statement .

   at present , Article 1 with a SELECT The statement should be self-evident . It uses prod_id yes TNT2 Search all order items for conditions order_num Column . Two orders containing this item are listed in the output

SELECT order_num
FROM orderitems
WHERE prod_id = 'TNT2';

The next step is to retrieve and order 20005 and 20007 Connected customers ID. Use the 7 Explained in Chapter IN Clause can create a SELECT sentence , as follows ：

SELECT cust_id
FROM orders
WHERE order_num IN (20005,20007);

Now? , By putting the first query （ Return the query of order number ） Become a subquery , So as to combine the two queries . Check out SELECT sentence ：

SELECT cust_id
FROM orders
WHERE order_num IN (SELECT order_num
                    FROM orderitems
                    WHERE prod_id = 'TNT2');

Be careful ：

Subqueries are always from the innermost SELECT Statement starts processing , And execute outwards .

First , It runs subqueries ：

SELECT order_num FROM orderitems WHERE prod_id='TNT2'

This query returns two order numbers 20005 and 20007. then , Will be with IN The comma separated format required by the operator passes these two values to the outer query WHERE Clause . The outer query now becomes ：

SELECT cust_id FROM orders WHERE order_num IN (20005,20007)

Tips ： format SQL

Including subqueries SQL Statements may be difficult to read and debug , Especially when their complexity increases . As shown here , Break the query down into multiple rows and indent them appropriately , It can greatly simplify the processing of subqueries .

Now there are ordering goods TNT2 Of all our customers ID. The next step is to retrieve with all these ID Associated customer information . For retrieving two columns SQL The statement is as follows ：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (10001,10004);

There is no need to hard code those customers ID, You can put this WHERE Clause into another subquery ：

SELECT cust_name, cust_contact
FROM customers
WHERE cust_id IN (SELECT cust_id
                  FROM orders
                  WHERE order_num IN (SELECT order_num
                                      FROM orderitems
                                      WHERE prod_id = 'TNT2'));

In order to implement this SELECT sentence ,Oracle In fact, we have to implement 3 strip SELECT sentence . The innermost sub query returns a list of order numbers , They will then be used as the WHERE Clause . The latter sub query returns customers ID A list of , They will be used as WHERE Clause . The top-level query actually returns the data we want .

Be careful ：

With further nested queries , Performance will begin to degrade .

Warning ： Columns must match

stay WHERE When using subqueries in clause （ As shown here ）, Make sure SELECT The statement has the same function as WHERE The same number of columns in the clause . Usually , The subquery will return a single column , And match with a single column , However, you can also use multiple columns if necessary .

Although subqueries are usually associated with IN Operators are used in conjunction with , But they can also be used to test equality （ Use =）、 Inequality （ Use <>） etc. .

Be careful ：

Oracle Allow in WHERE Clauses are nested at most 255 Sub query of levels （ Although it is difficult to find a situation that requires this ）.

Warning ： Subqueries and performance

The code shown here will retrieve the results we want , However, using subqueries is not always the most efficient way to perform such data retrieval .

2、 Take subquery as calculation field

Another way to use subqueries is to create calculated fields . Suppose you want to show by customers The total number of orders placed by each customer in the table . Order and corresponding customer ID Stored together in orders In the table .

To do this , You can follow the steps below .

1． from customers Search the list of customers in the table .

2． For every customer retrieved , Statistics orders Quantity of related orders in the table .

have access to SELECT COUNT(*) Rows in the statistics table , And by providing a WHERE Clause to filter specific customers ID, You can only count the orders of this customer . for example , The following code is used for statistics by customers 10001 Number of orders placed

SELECT COUNT(*) AS orders
FROM orders
WHERE cust_id = 10001;

For every customer COUNT() Calculation , You can put COUNT As a subquery . Take a look at the code below ：

SELECT cust_name,
       cust_state,
       (SELECT COUNT(*)
       FROM orders
       WHERE orders.cust_id = customers.cust_id) AS orders
FROM customers
ORDER BY cust_name;

This article SELECT Statement for customers Each customer in the table returns 3 Column ：cust_name、cust_state and orders.orders Is a calculated field , Subquery settings provided by parentheses . Execute sub query every time a customer is queried . In this example , The subquery will execute 5 Time , Because it retrieves 5 Customers .

Correlation subquery （Correlated Subquery）： Subqueries referencing external queries .

Tips ： Incrementally build queries with subqueries

Testing and debugging queries with subqueries can be tricky , Especially when these statements become more and more complex . structure （ And testing ） The safest way to query with subqueries is to use an incremental approach , This is related to Oracle They are handled in a very similar way . First, build the innermost query , Then build and test outer queries with hard coded data , And embed subqueries only after verifying that it will run . Then test it again , And keep repeating these steps for every other query . This will take a little longer to construct the query , But it can save you a lot of time in the future （ When you try to figure out why queries don't work correctly ）, And significantly increase the probability that they will work correctly for the first time .

Be careful ：

Except in this chapter SELECT and WHERE Clause ,Oracle Also in support of FROM Clause using subqueries . This kind of subquery is called inline view （Inline View）, It is a way to create various virtual tables . Inline views are less commonly used , I will not introduce it here .