Mysql pay special attention to the point!

Initial environment:

CREATE TABLE product (id int UNSIGNED NOT NULL auto_increment, amount INT UNSIGNED DEFAULT NULL, PRIMARY KEY (ID)) EN Gine = InnoDB CHARSET = ' utf8 '; INSERT into product VALUES (1,100), (2,200), (3,300), (4,400); SELECT * from Product; CREATE TABLE product_details (id int UNSIGNED NOT NULL, weight int UNSIGNED default NULL, exist int UNSIGNED default NULL, PRIMARY KEY (id)) ENGINE = InnoDB CHARSET = ' utf8 '; INSERT into Product_details VALUES (2,22,0), (4,44,1), (5,55,0), (6 , 66, 1); SELECT * from Product_details;

Execute the following two sql:

1. SELECT * FROM Product a left joins Product_details B on A.id=b.id and a.amount=100; results:

ID	Amount	ID	Weight	exist
1	100	Null	Null	Null
2	200	Null	Null	Null
3	300	Null	Null	Null
4	400	Null	Null	Null

2. SELECT * FROM Product a left joins Product_details B on A.id=b.id and a.amount=200; results:

ID	Amount	ID	Weight	exist
2	200	2	22	0
1	100	Null	Null	Null
3	300	Null	Null	Null
4	400	Null	Null	Null

Explanation of Reason:

First to clear the SQL execution order

First, A and B tables are made Cartesian product to generate virtual table VT1

The virtual table is generated by swiping the VT1 data according to the on condition VT2

If it is an outer join, you will need to add the filtered data (the outer row) in the reserved table to the VT2 to generate the VT3

The specific analysis statement 1,a B table generated Cartesian product according to A.id=b.id and a.amount=100 conditions, there are no qualifying rows, so all rows of table A table are preserved, all rows of table A are added to the virtual table in the field of table B is null, So we get the result.

Statement 2 is consistent with the 1 process, and the actual result is obtained by adding an external line based on the a.id=b.id and a.amount=200 brush.

Reference: "MySQL Technology Insider: SQL Programming. Kang"

Mysql pay special attention to the point!