What is a mysql subquery? How to filter using subqueries?

Subquery

MySQL 4.1 introduced support for subqueries, so to use the SQL described in this chapter, you must use MySQL 4.1 or higher.

SELECT statement is a SQL query. All SELECT statements we have seen so far have been simple queries, i.e., single statements that retrieve data from a single database table.

Query (query) Any SQL statement is a query. However, this term generally refers to the SELECT statement.

SQL also allows the creation of subqueries (subquery), that is, queries nested in other queries. Why do we do this? The best way to understand this concept is to examine a few examples.

Filtering using subqueries

The database tables used in all chapters of this book are relational tables (for a description of each table and relationship, please refer to Appendix B ). Orders are stored in two tables. The orders table stores one row for each order that contains the order number, customer ID, and order date. Items for each order are stored in the related orderitems table. The orders table does not store customer information. It only stores the customer's ID. The actual customer information is stored in the customers table. Now, if you need to list all customers who ordered item TNT2, how should you retrieve it? Specific steps are listed below.

(1) Retrieve the numbers of all orders containing item TNT2.

(2) Retrieve the IDs of all customers with the order number listed in the previous step.

(3) Retrieve the customer information of all customer IDs returned in the previous step.

Each of the above steps can be executed as a separate query. The results returned by one SELECT statement can be used in the WHERE clause of another SELECT statement.

You can also use subqueries to combine 3 queries into one statement.

The meaning of the first SELECT statement is clear, for all order items with prod_id TNT2, it retrieves their order_num column. Output lists two orders that contain this item:

Input:

select order_num from orderitems where prod_id = 'TNT2'；

Output:

Next, query with order 20005 and customer ID 20007. Using the IN clause, write the following SELECT statement:

Input:

select cust_id from orders where order_num in (20005,20007);

Output:

Now, put the first query (the one that returns the order number) becomes a subquery combining the two queries. Please look at the following SELECT statement:

Input:

select cust_id from orders where order_num in (select order_num from orderitems where prod_id = 'TNT2');

Output:

Analysis: In the SELECT statement, the subquery is always Work from the inside out. When processing the above SELECT statement, MySQL actually performs two operations.

First, it executes the following query:

select order_num from orderitems where prod_id = 'TNT2';

This query returns two order numbers: 20005 and 20007. These two values ​​are then passed to the WHERE clause of the outer query in the comma-separated format required by the IN operator. The outer query becomes:

select cust_id from orders where order_num in (20005,20007);

As you can see, the output is correct and the same as the value returned by the previous hard-coded WHERE clause.

Formatted SQL SELECT statements that contain subqueries can be difficult to read and debug, especially if they are complex. Breaking the subquery into multiple lines and indenting it appropriately as shown above can greatly simplify the use of subqueries.

Now we have the IDs of all customers who ordered item TNT2. The next step is to retrieve the customer information for these customer IDs. The SQL statement to retrieve two columns is:

Input:

select cust_name,cust_contact from customers where cust_id in (10001,10004);

You can convert the WHERE clause into a subquery instead of hardcoding these customer IDs:

Input:

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'));

Output:

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 that is used in the WHERE clause of its outer subquery. The outer subquery returns the list of customer IDs used in the WHERE clause of the outermost query. The outermost query does return the required data.

It can be seen that using subqueries in the WHERE clause can write powerful and flexible SQL statements. There is no limit to the number of nested subqueries, but in actual use due to performance limitations, too many subqueries cannot be nested.

Columns must match When using a subquery in a WHERE clause (as shown here), you should ensure that the SELECT statement has the same number of columns as in the WHERE clause. Typically, a subquery will return and match a single column, but multiple columns can be used if needed.

Although subqueries are generally used in conjunction with the IN operator, they can also be used to test equality ( = ), not equal ( ), etc.

Subquery and Performance The code given here works and gets the desired results. However, using subqueries is not always the most efficient way to perform this type of data retrieval. For more discussion, see Chapter 15, where this example is given again.

[Related recommendations]

1. mysql creates calculated fields and uses subqueries

2. #What are joins and relational tables in mysql?

3. Why use joins and how to create joins

4. The importance of WHERE clause in MySQL and how to join multiple tables
   

The above is the detailed content of What is a mysql subquery? How to filter using subqueries?. For more information, please follow other related articles on the PHP Chinese website!