Objective: To Learn subqueries and how to use them. Subquery: queries any SQL statement. However, this term generally refers to a select statement. SQL also allows you to create subqueries, that is, queries nested in other queries. Filter by subquery: Q: searches all customers who have purchased tnt2. Problem Analysis: (3) retrieve the customer information of all customer IDs returned in the previous step. Selece cust_namefrom customerswhere cust_id = (select cust_id, from orderswhere order_num = (select order_numfrom orderitemswhere prod_id = 'tnt2') (2) retrieve the customer ID with the order number listed in the previous step. Select cust_id, from orderswhere order_num = (select order_numfrom orderitemswhere prod_id = 'tnt2') (1) retrieve all order numbers containing the item tnt2. Select order_numfrom orderitemswhere prod_id = 'tnt2' # subquery. Q: all customers who purchased tnt2 are retrieved. Select cust_name, cust_contactfrom customerswhere cust_id in (select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'tnt2'); format the SQL: select statements containing subqueries are difficult to read and debug, especially when they are complex. As shown above, subqueries are decomposed into multiple rows and indented appropriately, which greatly simplifies the use of subqueries. You can use subqueries in the where clause to write SQL statements that are highly functional and flexible. There is no limit on the number of subqueries that can be nested. However, in actual use, due to performance constraints, too many subqueries cannot be nested. Columns must match: when using a subquery in the where clause, make sure that the SELECT statement has the same number of columns as in the WHERE clause. Use a subquery as a calculated field: [research required] Another method for using a subquery is to create a calculated field. Q: The total number of orders for each customer in the MERs table must be displayed. The order and corresponding customer ID are stored in the orders table. Problem Analysis: (1) retrieving the customer list from the MERs table (2) counting the number of orders in the orders table for each customer retrieved. # Use subquery as a calculated field. Q: query the total number of orders of each customer in the MERs table .???? Select count (*) as ordersfrom orderswhere cust_id = 10001; select cust_name, cust_state, (select count (*) from orders where orders. cus_id = MERs. cust_id) as ordersfrom customersorder by cust_name; related subqueries (correleted subquery) Involve subqueries of external queries. This type of subquery is called a related subquery. At any time, this syntax is required only when the column name may have multiple meanings (the table name and column name are separated by a period ). If the column name is not fully qualified, MySQL assumes that you compared the cust_id in the orders table. Select count (*) from orders where cust_id = cust_id; always returns the total number of orders in the orders table (because MySQL checks whether the cust_id of each order matches itself, of course, they always match ). Gradually add subqueries to create queries: It is very skillful to test and debug queries using subqueries, especially when the review of these statements continues to increase. The most reliable way to create (and test) a query using subqueries is to perform them gradually, which is the same as how MySQL processes them. First, create and test the innermost layer query. Then, use the hardcoded data to establish and test the outer query, and embed the subquery only after confirming that it is normal. Test it again. Repeat these steps for each subquery to be added. This only adds a little time to construct a query, but saves a lot of time in the future (find out why the query is abnormal) and greatly improves the possibility that the query will work normally at the beginning.
Chapter 1 subquery
