MYSQL Learning Notes Collation Three: sub-query

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.