1. Filter by sub-query eg: If you need to list all the customers ordering items TNT2, To retrieve all the order numbers containing the item TNT2 retrieve the ID of all customers that have the order number listed in the previous step retrieve customer information for all customer IDs returned in the previous step each of these steps can be performed individually as a single query. You can use the result returned by one SELECT statement for the WHERE statement of another SELECT statement. You can also use a subquery to combine 3 queries into a single statement. Select Order_num from OrderItems WHERE prod_id= ' TNT2 '; the output is:order_num:20005,20007 next, query the customer ID with orders 20005 and 20007. SELECT cust_id from Orders where Order_num in (20005,20007); output:cust_id:10001,10004 now, Change the first query (the one that returns the order number) to a subquery combination of two queries. See: Select cust_id from Orders where Order_num in (SELECT order_num from OrderItems where prod_id= ' TNT2 '); output is: cust_id:100 01,10004 Analysis: In a SELECT statement, subqueries are always handled from inside out. When dealing with the above statement, MySQL actually performed two operations. now has all the customer IDs for the ordered items TNT2, the next step is to retrieve customer information for these customer IDs, and retrieve two columns of SQL statements: SELECT Cust_name, cust_contact from customers where cust_id In (10001,10004), you can convert the WHERE clause into a subquery instead of hard-coding these customer IDs. Select Cust_name, cust_contact from Customers where Cust_idin (select cust_id from Orders where Order_num in (select Order_n Umfrom orderitems WHERE prod_id= ' TNT2 '); analysis: In order to execute the above SELECT statement, MySQL must actually execute 3 SELECT statements. The innermost subquery returns a list of order numbers, this listThe WHERE clause for the subquery outside it. The outer subquery Returns a list of customer IDs that are used for the WHERE clause of the outermost query. The outermost query does return the data that you want. However, there are too many subqueries that cannot be nested because of performance limitations when actually used. Note: Columns must match. Using subqueries in the WHERE clause, you should ensure that the SELECT statement has the same number of columns as the WHERE clause, typically the subquery returns a single column and matches a single column, but you can use more than one column if you want. Although subqueries are generally used in conjunction with the in operator, they can also be used to test equals (=), not equal to (<>), and so on. 2, another way to use subqueries as calculated fields using subqueries is to create calculated fields. In order to do this, if you need to display the total number of orders per customer in the Customers table, follow these steps: Retrieve a list of customers from the Customers table for each customer that is retrieved, count the number of orders it has in the Orders table. Example: The following code counts orders for 10001 of customers. Select COUNT (*) as orders from orders WHERE cust_id=10001; to perform a count (*) calculation for each client, COUNT (*) should be counted as a subquery, see below: Select Cust_name,cust_state, (select COUNT (*) from Orderswhere orders.cust_id=customers.cust_id) as Ordersfrom Customersorder by Cust_name; Analysis: This SELECT statement returns 3 columns for each customer in the Customers table: Cust_name,cust_state,orders. Orders is a calculated field that is established by a subquery of parentheses, which is executed once for each client that is retrieved. In this example, the subquery was executed 5 times, so 5 customers were retrieved. Summary: The most common use of subqueries is in the in operator of the WHERE clause, and is used to populate computed columns.
MYSQL Learning Notes collation three: subqueries
