MySQL Sub-query

A sentence is the result of a subquery as a comparison condition for an external query

A subquery is a query that is nested inside another query statement, that is, a select in the Select.

In the SELECT statement, the subquery is evaluated first, and the result of the subquery is the filter for the outer layer of the other query.

The commonly used operators in subqueries are:

Any (some), all in, exists

Subqueries can be added to select, UPDATE, and DELETE statements, and can be nested in multiple layers.

Sub-query with any, some keyword

The any and some keywords are synonyms that satisfy any of these conditions

Nonsense not much to say, look directly at examples

Define two tables Tb1 and TB2:

CREATE TABLE TB1 (NUM1 int not null);

CREATE TABLE TB2 (num2 int not null);

Insert data like two tables, respectively:

INSERT into TB1 values (1), (5), (13), (27);

INSERT into TB2 values (6), (14), (11), (20);

Query statement:

Select Num1 from tb1 where Num1 > A (select num2 from TB2);

Any column of the selected record that satisfies the criteria

subquery with the ALL keyword

All to satisfy all the conditions of the inner query.

Select Num1 from tb1 where NUM1 < All (select num2 from TB2);

Subqueries with the EXISTS keyword

The argument after the EXISTS keyword is an arbitrary subquery that the system queries to determine whether it returns rows.

If the result of exists is true, then the outer query statement will be queried

If the subquery does not return any rows, the result returned by exists is false, at which point the outer statement will not query

Example:

SELECT * from Fruits

where exists

(select s_name from suppliers where s_id = 107);

Whether the query exists s_id = 107 of suppliers

The use of not exists is just the opposite of exists.

It exist or not exist just returns a state that has nothing to do with the previous query.

SELECT * from Fruits

Where NOT EXISTS

(select s_name from suppliers where s_id = 107);

Note:

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

Keyword subquery with in

The IN keyword inside the query statement returns only one column of data (OK? ), the value in this data column will provide the outer query statement for comparison operations.

Select c_id

From Orders

where O_num in

(select O_num from orderitems where f_id = ' c0 '); Sub-query first find out C0 order number O_num as a comparison list,

Then filter the records from the Orders table that appear in the results of the query, and then select the columns you want from the records

The result of an in-context query is the comparison condition of the outer query.

The not-in keyword can be used in a Selec statement, which acts in the exact opposite direction of in.

Subqueries can also be done with connection queries, but subqueries make MySQL code easier to read and write

Select c_id from orders left join OrderItems on orders.o_num where f_id = ' c0 ';

That's not how it's used, bro.

Select c_id from orders left joins OrderItems on orders.o_num = orderitems.o_num where f_id = ' c0 ';

But there is no f_id this field in the Orders table, why not, this is to connect two tables to query, will generate a temporary table of MXN Row Records,

Then query from this temporary table, and this f_id is in the interim result.

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 |
+-------+---------------------+-------+

Mysql> select * from OrderItems;
+-------+--------+------+----------+------------+
| O_num | O_item | f_id | Quantity | Item_price |
+-------+--------+------+----------+------------+
|      30001 | 1 |       A1 |       10 | 5.20 |
|      30001 | 2 |        B2 |       3 | 7.60 |
|      30001 | 3 |        BS1 |      5 | 11.20 |
|      30001 | 4 |       BS2 |       15 | 9.20 |
|      30002 | 1 |        B3 |      2 | 20.00 |
|      30003 | 1 |      C0 |      100 | 10.00 |
|      30004 | 1 |       O2 |       50 | 2.50 |
|      30005 | 1 |        C0 |      5 | 10.00 |
|      30005 | 2 |       B1 |       10 | 8.99 |
|      30005 | 3 |       A2 |       10 | 2.20 |
|      30005 | 4 |        M1 |      5 | 14.99 |
+-------+--------+------+----------+------------+
Sub-query with comparison operation

First, in the Suppliers table, query the vendor s_id that s_city equals "Tianjin", and then query the fruits for all the types of fruit that the vendor offers.

Select s_id from suppliers where s_city = ' Tianjin '; This step is done from the query, find out s_id

Select F_name s_id from fruits where s_id = result of subquery

The whole sentence is:

Select fruits.s_id, fruits.f_name from fruits where fruits.s_id = (select suppliers.s_id from suppliers where s_city = ' Ti Anjin ');

Once successful

Then try it again with left join.

Select fruits.s_id, fruits.f_name from fruits left joins suppliers on fruits.s_id = suppliers.s_id where s_city = ' Tianjin ' ;

A success, after checking out refreshed

MySQL Sub-query