Shulou(Shulou.com)05/31 Report--

This article mainly introduces the MySQL data query sub-query example analysis, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor with you to understand.

A subquery is a query in which one query statement is nested within another query statement. In the select clause, the subquery is calculated first, and the result of the subquery is used as a filter condition for another query, and the query can be based on one or more tables.

The operators commonly used in subqueries are any (some), all, in, and exists. Subqueries can be added to select, update, and delete statements and can be nested at multiple levels. Comparison operators, such as "=" and "! =", can also be used in subqueries.

(1) subquery with any and some keywords

(2) subquery with all keyword

(3) subquery with exists keyword

(4) subquery with in keyword

(5) subquery with comparison operator

(recommended for free study: mysql video tutorial)

(1), subquery with any and some keywords

The any and some keywords are synonyms that either of these conditions are met, allowing you to create an expression to compare the list of return values of a subquery, and to return a result as a condition for an outer query as long as any comparison condition in the inner subquery is met.

The following defines two tables, tbl1 and tbl2, and inserts data into them:

Mysql > create table tbl1 (num1 int not null); Query OK, 0 rows affected (0.13 sec) mysql > create table tbl2 (num2 int not null); Query OK, 0 rows affected (0.10 sec) mysql > insert into tbl1 values (1), (5), (13), (27); Query OK, 4 rows affected (0.05 sec) Records: 4 Duplicates: 0 Warnings: 0mysql > insert into tbl2 values (6), (14), (11), (20); Query OK, 4 rows affected (0.06 sec) Records: 4 Duplicates: 0 Warnings:

The any keyword is followed by a comparison operator to indicate that true is returned if it is more true than any job ratio returned by the subquery.

[example] return all the num2 columns of the tbl2 table, and then compare the value of num1 in tbl1 with it. As long as it is greater than any one of the values of num2, it is the qualified result.

Mysql > select num1 from tbl1 where num1 > any (select num2 from tbl2); +-+ | num1 | +-+ | 13 | | 27 | +-+ 2 rows in set (0.00 sec) (2), subquery with all keyword

The all keyword needs to meet the conditions of all inner queries at the same time.

The all keyword is followed by a comparison operator, indicating that all values returned by the subquery are compared to true, then true is returned.

[example] return the values in the tbl1 table that are greater than all the values in the num2 column of the tbl2 table. The SQL statement is as follows:

Mysql > select num1 from tbl1 where num1 > all (select num2 from tbl2); +-+ | num1 | +-+ | 27 | +-+ 1 row in set (0.00 sec) (3), subquery with exists keyword

The parameter after the exists keyword is an arbitrary subquery, and the system operates on the subquery to determine whether it returns rows. If at least one row is returned, the result of exists is true, and the outer query statement will query; if the subquery does not return any rows, the result returned by exists is false, and the outer statement will not query.

[example 1] query whether there is a supplier of s_id=107 in the suppliers table, and if so, query the records in the fruits table as follows:

Mysql > select * from fruits-> where exists-> (select s_name from suppliers where s_id = 107) +-+ | f_id | s_id | f_name | f_price | +-+ | 12 | 104 | lemon | 6.40 | | A1 | 101 | apple | 5.20 | | a2 | 103 | apricot | 2.20 | | b1 | 101 | blackberry | 10.20 | | b2 | 104 | berry | 7.60 | | b5 | 107 | xxxx | 3.60 | bs1 | 102 | orange | bs2 | melon | 8.20 | c0 | 101 | cherry | 3.20 | M1 | 106 | mango | 15.70 | m2 | 105 | xbabay | 2 | .60 | | m3 | 105 | xxtt | 11.60 | | O2 | 103 | coconut | 9.20 | T1 | 102 | banana | 10.30 | | T2 | 102 | grape | 5.30 | | T4 | xbabay | 3.60 | +-+ 16 rows in set (0.00 sec)

As can be seen from the results, the inner query results show that there are records of s_id=107 in the suppliers table, so the exists expression returns the true; outer query statement receives the true and then queries the table fruits and returns all the records.

[example 2] query whether there is a supplier of s_id=107 in the suppliers table, and if so, query the records in the fruits table whose f_price is greater than 10.20.The SQL statement is as follows:

Mysql > select * from fruits-> where f_price > 10.20 and exists-> (select s_name from suppliers where s_id = 107) +-+ | f_id | s_id | f_name | f_price | +-+ | bs1 | 102 | orange | 11.20 | | M1 | 106 | mango | 15.70 | | m3 | 105 | xxtt | 11.60 | T1 | | | 102 | banana | 10.30 | +-+ 4 rows in set (0.00 sec) |

You can see that there are records of s_id=107 in the inner query table name suppliers table, so the exists expression returns the true; outer query statement receives true, and then queries the fruits table according to the query condition f_price > 10.20, and returns 4 records with a f_price greater than 10.20.

Not exists uses the same method as exists, but returns the opposite result. If the subquery returns at least one row, the result of not exists is false, and the outer query statement will not query; if the subquery does not return any rows, the result returned by not exists will be true, and the outer statement will query.

[example 3] query whether there is a supplier with s_id = 107in the suppliers table, and if not, query the records in the fruits table with the following SQL statement:

Mysql > select * from fruits-> where not exists-> (select s_name from suppliers where s_id = 107); Empty set (0.00 sec)

As you can see, the inner query returns false, and the outer expression receiving false no longer queries the records in the fruits table.

Note: the results of exists and not exists depend only on whether rows are returned, not on the contents of those rows, so the input list of this subquery is usually irrelevant.

(4) subquery with in keyword

When the in keyword runs a subquery, the inner query statement returns only one data column, and the values in this data column are provided to the outer query statement for comparison operations.

[example 1] query the order number with f_id c0 in the orderitems table, and query the customer with order number according to the order number as follows:

Mysql > select c_id from orders where o_num in-> (select o_num from orderitems where f_id = 'c0'); +-+ | c_id | +-+ | 10004 | | 10001 | +-+ 2 rows in set (10004 sec)

The above statement is an abbreviation for the following query:

Mysql > select o_num from orderitems where f_id = 'c0chromosoman sec + | o_num | +-+ | 30003 | | 30005 | +-+ 2 rows in set (0.00 sec) mysql > select c_id from orders where o_num in (30003Lab 30005); +-+ | c_id | +-+ | 10004 | 10001 | +-+ 2 rows in set (0.00 sec)

The following describes the not in keyword as opposed to in:

[example 2] is similar to example 1, except that the not in keyword is used in the select statement. The SQL statement is as follows:

Mysql > select c_id from orders where o_num not in-> (select o_num from orderitems where f_id = 'c0'); +-+ | c_id | +-+ | 10001 | | 10003 | 10005 | +-+ 3 rows in set (10001 sec)

You can see that three tables have been returned. Looking at the records in orders, we can see that there is more than one order for a customer whose c_id is equal to 10001:

Mysql > select * from orders +-+ | o_num | o_date | c_id | +-+ | 30001 | 2008-09-01 00:00:00 | 10001 | 30002 | 2008-09-12 00:00 : 00 | 10003 | | 30003 | 2008-09-3000: 00:00 | 10004 | | 30004 | 2008-10-03 00:00:00 | 10005 | 30005 | 200810-08 00:00:00 | 10001 | +-+ 5 rows in set (30003 sec)

As a result, the order number is excluded, but it is still possible to choose the same customer.

Subqueries can also be done through join queries, but subqueries make MySQL code easier to read and write.

(5) subquery with comparison operator

[example 1] query the supplier s_id whose s_city equals "Tianjin" in the suppliers table, and then query the fruits table for all the types of fruits provided by the supplier. The SQL statement is as follows:

Mysql > select scuttlehead from fruits-> where s_id =-> (select s1.s_id from suppliers as S1 where s1.s_city = 'Tianjin') +-+-+ | s_id | f_name | +-+-+ | 101 | apple | 101 | blackberry | | 101 | cherry | +-+-+ 3 rows in set (0.00 sec)

[example 2] query the supplier whose s_city equals "Tianjin" in the suppliers table, s_id, and then query the fruits table for all kinds of fruits that are not provided by the supplier. The SQL statement is as follows:

Mysql > select s_id, f_name from fruits-> where s_id-> (select s1.s_id from suppliers as S1 where s1.s_city = 'Tianjin') +-+-+ | s_id | f_name | +-+-+ | lemon | lemon | 103 | apricot | | 104 | berry | | xxxx | 102 | orange | | melon | 106 | mango | xbabay | xbabay | xxtt | 103 | coconut | 102 | banana | 102 | grape | xbabay | +-+-+ 13 rows In set (0.00 sec) Thank you for reading this article carefully. I hope the article "sample Analysis of the Child query of MySQL data query" shared by the editor will be helpful to you. At the same time, I also hope that you will support and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.