MySQL learning footprint record 12-use subquery

MySQL learning footprint record 12-use subquery 1. subquery: Query raw data nested in other queries is as follows:

Mysql> SELECT order_num FROM orderitems; + ----------- + | order_num | + ----------- + | 20005 | 20005 | 20009 | 20005 | 20009 | 20008 | 20006 | 20009 | 20009 | 20005 | 20007 | + ----------- + 11 rows in set (0.01 sec) mysql> SELECT cust_id FROM orders; + --------- + | cust_id | + --------- + | 10001 | 10001 | 10003 | 10004 | 10005 | + --------- + 5 rows in set (0.01 sec) step-by-Step query: Step 1: mysql> SELECT order_num -> FROM orderitems-> WHERE prod_id = 'tnt2'; + ----------- + | order_num | + ----------- + | 20005 | 20007 | + ----------- + 2 rows in set (0.00 sec) step 2: mysql> SELECT cust_id FROM orders-> WHERE order_num IN (20005, 20007 ); + --------- + | cust_id | + --------- + | 10001 | 10004 | + --------- + 2 rows in set (0.00 sec) Step 3: Use the subquery to set step 1, step 2: combine them (replace 20005 and 20007) mysql> SELECT cust_id-> FROM orders-> WHERE order _ Num IN (SELECT order_num-> FROM orderitems-> WHERE prod_id = 'tnt2 '); + --------- + | cust_id | + --------- + | 10001 | 10004 | + --------- + 2 rows in set (0.00 sec) TIPS: in the SELECT statement, subqueries are always processed internally and externally. Subqueries can be nested with multiple step4: mysql> SELECT cust_name, cust_contact-> FROM MERs MERS-> WHERE cust_id IN (10001,10004); # (10001,10004) both the result of step 3 query + ---------------- + -------------- + | cust_name | cust_contact | + ---------------- + -------------- + | Coyote Inc. | Y Lee | Yosemite Place | Y Sam | + ---------------- + -------------- + 2 rows in set (0.01 sec) Step 5: set the IN (,) of Step 4) replace it with the subquery mysql> SELECT cust_name, cust_contact-> FROM MERs MERS-> WHERE cust_id IN (SELECT cust_id-> FROM orders-> WHERE order_num IN (SELECT order_num-> FROM orderitems-> WHERE prod_id = 'tnt2 ')); + ---------------- + ------------ + | cust_name | cust_contact | + ---------------- + -------------- + | Coyote Inc. | Y Lee | Yosemite Place | Y Sam | + ---------------- + -------------- + 2 rows in set (0.00 sec)

 

2. Use the subquery to query raw data for calculated fields

   mysql> SELECT cust_id FROM orders;+---------+| cust_id |+---------+|   10001 ||   10001 ||   10003 ||   10004 ||   10005 |+---------+5 rows in set (0.01 sec)mysql> SELECT cust_id FROM customers;+---------+| cust_id |+---------+|   10001 ||   10002 ||   10003 ||   10004 ||   10005 |+---------+5 rows in set (0.00 sec)mysql> SELECT cust_id,(SELECT COUNT(*) FROM orders         ->                                WHERE orders.cust_id = customers.cust_id) AS orders        -> FROM customers       -> ORDER BY cust_id;+---------+--------+              | cust_id | orders |+---------+--------+|   10001 |      2 ||   10002 |      0 ||   10003 |      1 ||   10004 |      1 ||   10005 |      1 |+---------+--------+5 rows in set (0.00 sec)

 

TIPS: subqueries are most commonly used IN the IN operator of the WHERE clause and are used to fill the computed column.