https://www.oschina.net/question/89964_65912 Even if you think you have a deep understanding of MySQL's left JOIN, I bet this article will definitely get you to learn something!
- The ON clause differs from the WHERE clause
- A better understanding with WHERE ... A simple method for complex matching conditions of the IS NULL clause
- The difference between matching-conditions and where-conditions
A little reminder of the "a LEFT join B on conditional expression" on condition ("a LEFT join B in conditional expression" on) is used to determine how data rows are retrieved from table B. If no row of data in table B matches the on condition, an additional row will be generated for all data that is listed as NULL in the matching phase WHERE clause conditions are not used. The WHERE clause condition is used only after the match phase is complete. It retrieves the filter from the data that is produced during the matching phase. Let's look at a Lfet JOIN example:
| 12345678910111213141516171819202122232425262728293031323334353637383940414 24344454647484950515253545556 |
mysql> CREATE TABLE ' product ' ( ' id ' int () unsigned not NULL auto_increment, ' amount ' int (10) Unsigned default null, primary key (' id ')) engine=myisam auto_increment=5 default Charset=latin1 mysql> CREATE TABLE ' product_details ' ( ' id ' int () unsigned not null, ' weight ' Int (ten) unsigned default null, ' exist ' int (ten) unsigned default null, primary key (' id ')) Engine=myisam DEFAULT charset=latin1 mysql> INSERT into product (Id,amount) values (1,100), (2,200), (3,300), (4,400); Query OK, 4 rows Affected (0.00 sec) records:4 duplicates:0 warnings:0 mysql> INSERT into prod Uct_details (id,weight,exist) values (2,22,0), (4,44,1), (5,55,0), (6,66,1 ); Query OK, 4 rows Affected (0.00 sec) records:4 duplicates:0 warnings:0 mysql> SELECT * from PR Oduct;+----+--------+| ID | Amount |+----+--------+| 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | |+----+--------+4 rows in Set (0.00 sec) mysql> SELECT * from Product_detai ls;+----+--------+-------+| ID | Weight | exist |+----+--------+-------+| 2 | | 0 | | 4 | | 1 | | 5 | | 0 | | 6 | | 1 |+----+--------+-------+4 rows in Set (0.00 sec) & Nbsp;mysql> SELECT * from product left JOIN Product_details on (product.id = product_details.id) +----+--------+------+--------+-------+| ID | Amount | id | Weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | NULL | NULL | | 2 | | 2 | | 0 | | 3 | 300 | NULL | null | NULL | | 4 | | 4 | | 1 |+----+- -------+------+--------+-------+4 rows in Set (0.00 sec) |
What is the difference between the ON clause and the WHERE clause? A question: What is the difference between the result sets of the two queries below??
| 123456 |
1. SELECT * FROM product left JOIN product_details on (product.id = product_details.id) and Product_deta Ils.id=2;2. SELECT * FROM product left JOIN product_details on (product.id = product_details.id) WHERE product_details . id=2; |
It's best to use examples to understand:?
| 12345678910111213141516171819202122 |
Mysql> SELECT * from product left JOIN product_details on (product.id = product_details.id) and product_details.id=2;+----+--------+------+--- -----+-------+| ID | Amount | id | Weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | null | NULL | | 2 | | 2 | | 0 | | 3 | 300 | NULL | null | NULL | | 4 | 400 | NULL | null | NULL |+----+--------+------+--------+-------+4 rows in Set (0.00 sec) mysql& Gt SELECT * FROM product left JOIN product_details on (product.id = product_ details.id) where product_details.id=2;+----+--------+----+--------+-- -----+| ID | Amount | Id| Weight | exist |+----+--------+----+--------+-------+| 2 | | 2 | 22 |& nbsp; 0 |+----+--------+----+--------+-------+1 row in Set (0.01 sec) |
The first query uses the on condition to determine that all rows of data that are compliant are retrieved from the Product_details table of the left join. The second query makes a simple left join, and then uses the WHERE clause to filter out non-qualifying rows of data from the data in the left join. Take another look at some examples:?
| 1234567 8910111213 |
mysql>mysql> select * from product left JOIN prod Uct_details on product.id = product_details.id and product.amount=100;+----+--------+------+--------+-------+| 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 |+----+--------+------+--------+-------+4 rows in Set (0.00 sec) |
All data rows from the product table are retrieved, but no records are matched in the product_details table (product.id = Product_details.id and product.amount=100 Condition does not match to any data)?
| 1234567 89101112 |
mysql> select * from product left JOIN Product_details on (product.id = product_details.id) and product.amount=200;+----+--------+------+--------+-------+| ID | Amount | id | Weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | null | NULL | | 2 | | 2 | | 0 | | 3 | 300 | NULL | null | NULL | | 4 | 400 | NULL | null | NULL |+----+--------+------+--------+-------+4 rows in Set (0.01 sec) |
Similarly, all data rows from the product table are retrieved, and a data match is made. Use WHERE ... is the NULL clause of the LEFT join when you use the WHERE ... What happens when it is a NULL clause? As mentioned earlier, where condition queries occur after the match phase, which means that the where ... The IS NULL clause filters out rows of data that do not meet the matching criteria from the data after the match phase. It looks pretty clear on paper, but it's confusing when you use multiple conditions in the ON clause. I've summed up a simple way to understand the situation:
- is NULL as a negative match condition
- Use! (A and B) = =! A OR! B Logical Judgment
Take a look at the following example:?
| 1234567891011 |
Mysql> SELECT a.* from Product a left JOIN Product_details B on A.id=b.id and b.weight!=44 and b.exist=0 WH ERE b.ID is null;+----+--------+| ID | Amount |+----+--------+| 1 | 100 | | 3 | 300 | | 4 | |+----+--------+3 rows in Set (0.00 sec) |
Let's check the on match clause:?
| 1 |
(a.id=b.id) and (b.weight!=44) and (b.exist=0) |
We can think of the IS NULL clause as a negative match condition. This means that we will retrieve the following line:?
| 123 |
! (exist (b.id that equals to a.id) and B.weight!=44 and b.exist=0)!exist (b.id, equals to a.id) | | ! (b.weight!=44) | | ! (b.exist=0)!exist (b.id that equals to a.id) | | B.weight = 44 | | B.exist=1 |
Just like the logical AND and logical or expressions in C, the operands are evaluated from left to right. If the first parameter is sufficient to determine the result of the operation, then the second parameter is not evaluated (short-circuit effect) to see another example:?
| 123456789101112 |
Mysql> SELECT a.* from Product a left JOIN Product_details B on A.id=b.id and b.weight!=44 and B.exist=1 WH ERE b.ID is null;+----+--------+| ID | Amount |+----+--------+| 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | |+----+--------+4 rows in Set (0.00 sec) |
The Battle of Matching-conditions and where-conditions if you just put the basic query condition in the ON clause and put the remaining negative conditions in the WHERE clause, you will get the same result. For example, you may not write this:?
| 123 |
SELECT a.* from Product a left joins Product_details BON A.id=b.id and b.weight!=44 and B.exist=0where b.id is NULL; |
You can write this:?
| 123 |
SELECT a.* from Product a left joins Product_details BON A.id=b.idwhere b.id is null or b.weight=44 or b.exist=1; |
?
| 1234567891011 |
Mysql> SELECT a.* from Product a left JOIN product_details b in a.id=b.id WHERE b.id is null OR b.weight=44 OR b.exist=1;+----+--------+| ID | Amount |+----+--------+| 1 | 100 | | 3 | 300 | | 4 | |+----+--------+3 rows in Set (0.00 sec) |
You can not write like this:?
| 123 |
SELECT a.* from Product a left joins Product_details BON A.id=b.id and b.weight!=44 and B.exist!=0where b.id is NULL; |
Can write like this:?
| 123 |
SELECT a.* from Product a left joins Product_details BON A.id=b.idwhere b.id is null or b.weight=44 or b.exist=0; |
?
| 123456789101112 |
Mysql> SELECT a.* from Product a left JOIN product_details b in a.id=b.id WHERE b.id is null OR b.weight=44 OR b.exist=0;+----+--------+| ID | Amount |+----+--------+| 1 | 100 | | 2 | 200 | | 3 | 300 | | 4 | |+----+--------+4 rows in Set (0.00 sec) |
Do these queries really work the same way? If you only need the data from the first table, these queries will return the same result set. One case is that if you retrieve data from the table in the left join, the result of the query is different. The WHERE clause is used to filter after the match phase, as previously owned. For example:?
| 12345678910111213141516171819202122232425 |
Mysql> SELECT * FROM Product a left JOIN product_details b on a.id=b.id and B. Weight!=44 and B.exist=1 where b.id is null;+----+--------+------+-------- +-------+| 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 |+----+--------+------+--------+-------+4 rows in Set (0.00 sec) mysql& Gt SELECT * FROM Product a left JOIN product_details b on a.id=b.id & Nbsp; where b.id is NULL or b.weight=44 or b.exist=0;+----+--------+------+--------+-------+| ID | Amount | id | Weight | exist |+----+--------+------+--------+-------+| 1 | 100 | NULL | null | NULL | | 2 | | 2 | | 0 | | 3 | 300 | NULL | null | NULL | | 4 | | 4 | | 1 |+----+- -------+------+--------+-------+4 rows in Set (0.00 sec) |
1 General notes: If you use the left JOIN to look for records that do not exist in some tables, you need to do the following: Col_name is NULL for the where part (where the Col_name column is defined as NOT null), and MYSQL queries to a match on OIN the condition will stop searching for more rows (under a specific key combination).
MySQL left JOIN You may need to know three points
