MySQL data query child query

Subquery refers to a query nested within another query statement. The subquery is calculated first in the select clause, and the subquery result is used as the filter condition of another outer query. The query can be based on one table or multiple tables.

Commonly used operators in subqueries include 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 can also be used in subqueries, such as "<", "<=", ">", ">=" and "!=".

(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

(free learning recommendation: mysql Video tutorial)

(1), subquery with any and some keywords

any and some keywords are synonyms , indicating that any one of the conditions is met, allowing the creation of an expression to compare the return value list of the subquery. As long as any comparison condition in the inner subquery is met, a result is returned as the condition of the outer query.

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

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: 0

The any keyword is followed by a comparison operator, indicating that if it is compared with any returned by the subquery If the job comparison is true, return true.

[Example] Return all 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 value of num2, it is a 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

• all keyword needs to satisfy the conditions of all inner queries at the same time.
• The all keyword is followed by a comparison operator, which means that if compared with all values ​​returned by the subquery, true will be returned.

[Example] Return the value in the tbl1 table that is 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. 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. At this time, the external The layer query statement will query; if the subquery does not return any rows, then the result returned by exists is false, and the outer layer statement will not query at this time.

[Example 1] Query whether the supplier with s_id=107 exists in the suppliers table. If it exists, query the records in the fruits table. The SQL statement is 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     |   11.20 || bs2  |  105 | 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   |  107 | xbabay     |    3.60 |+------+------+------------+---------+16 rows in set (0.00 sec)

By The results show that the inner query result shows that there is a record of s_id=107 in the suppliers table, so the exists expression returns true; after receiving true, the outer query statement queries the fruits table and returns all records.

[Example 2] Query whether there is a supplier with s_id=107 in the suppliers table. If it exists, query the records with f_price greater than 10.20 in the fruits table. 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)

Yes It can be seen that there is a record of s_id=107 in the inner query table name suppliers table, so the exists expression returns true; after the outer query statement receives true, the fruits table is queried according to the query condition f_price>10.20, and the returned result is 4 f_price Records greater than 10.20.

• not exists is used in the same way as exists, but returns the opposite result. If the subquery returns at least one row, then the result of not exists is false, and the outer query statement will not query; if the subquery does not return any rows, then the result returned by not exists is true, and the outer query statement will be executed. Inquire.

[Example 3] Query whether the supplier with s_id =107 exists in the suppliers table. If it does not exist, query the records in the fruits table. The SQL statement is as follows:

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

You can see When the inner query returns false, if the outer expression receives false, it will no longer query the records in the fruits table.

Note: The results for exists and not exists only depend on whether rows will be returned, not on the content of those rows, so this subquery input list is usually irrelevant.

(4), subquery with in keyword

• When the in keyword is used to perform a subquery, the inner query statement only returns one data column. This data column The value inside will be provided to the outer query statement for comparison operation.

[Example 1] Query the order number with f_id c0 in the orderitems table, and query the customer c_id with the order number based on the order number. The SQL statement is 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 (0.00 sec)

The above The statement is the abbreviation of the following query method:

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

The following is the not in keyword that is opposite to in:

[Example 2] is similar to Example 1, but 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 (0.00 sec)

You can see that three tables were returned. Looking at the records in orders, we can see that the customer with c_id equal to 10001 has more than one order:

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-30 00:00:00 | 10004 || 30004 | 2008-10-03 00:00:00 | 10005 || 30005 | 2008-10-08 00:00:00 | 10001 |+-------+---------------------+-------+5 rows in set (0.00 sec)

The result is only that the order number is excluded , but it is still possible to select the same customer.

The functionality of subqueries can also be accomplished through join queries, but subqueries make MySQL code easier to read and write.

(5)、带比较运算符的子查询

【例1】在suppliers表中查询s_city等于"Tianjin"的供应商s_id，然后在fruits表中查询所有该供应商提供的水果的种类，SQL语句如下：

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     |+------+------------+|  101 | apple      ||  101 | blackberry ||  101 | cherry     |+------+------------+3 rows in set (0.00 sec)

【例2】在suppliers表中查询s_city等于"Tianjin"的供应商，s_id，然后在fruits表中查询所有非该供应商提供的水果的种类，SQL语句如下：

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  |+------+---------+|  104 | lemon   ||  103 | apricot ||  104 | berry   ||  107 | xxxx    ||  102 | orange  ||  105 | melon   ||  106 | mango   ||  105 | xbabay  ||  105 | xxtt    ||  103 | coconut ||  102 | banana  ||  102 | grape   ||  107 | xbabay  |+------+---------+13 rows in set (0.00 sec)

更多相关免费学习推荐：mysql教程(视频)

The above is the detailed content of MySQL data query child query. For more information, please follow other related articles on the PHP Chinese website!