Deep understanding of Mysql's left join using the detailed _mysql

The ON clause differs from the WHERE clause
A better understanding with WHERE ... Simple method of complex matching criteria for IS NULL clause
The difference between matching-conditions and where-conditions

A reminder of the "a left JOIN B on condition expression"

The on condition (on in a left JOIN B in condition expression) is used to determine how to retrieve rows of data from table B.

If no row of data in table B matches the condition on, a row of all columns of NULL data is generated

The conditions in the match phase WHERE clause are not used. The WHERE clause condition is used only after the match phase completes. It retrieves the filter from the data generated during the match phase.

Let's look at a Lfet JOIN example:

mysql> CREATE TABLE ' product ' (
' ID ' int (a) unsigned not NULL auto_increment,
' Amount ' int (a) unsigned default NULL,
PRIMARY KEY (' id ')
) Engine=myisam auto_increment=5 DEFAULT charset=latin1

mysql> CREATE TABLE ' product_details ' (
' ID ' int (a) unsigned not NULL,
' Weight ' int (a) unsigned default NULL,
' Exist ' int (a) 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 Product_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 product;
+----+--------+
| ID | Amount |
+----+--------+
|    1 | 100 |
|    2 | 200 |
|    3 | 300 |
|    4 | 400 |
+----+--------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from Product_details;
+----+--------+-------+
| ID | Weight | exist |
+----+--------+-------+
|     2 |     22 | 0 |
|     4 |     44 | 1 |
|     5 |     55 | 0 |
|     6 |     66 | 1 |
+----+--------+-------+
4 rows in Set (0.00 sec)

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 |    200 |     2 |     22 | 0 |
|    3 | 300 |   NULL |  NULL | NULL |
|    4 |    400 |     4 |     44 | 1 |
+----+--------+------+--------+-------+
4 rows in Set (0.00 sec)

What is the difference between an ON clause and a WHERE clause?

One question: What is the difference between the result sets for the following two queries?

1. SELECT * FROM product left JOIN Product_details
On (product.id = product_details.id)
and product_details.id=2;
2. SELECT * FROM product left JOIN Product_details
On (product.id = product_details.id)
WHERE product_details.id=2;

Use examples to understand the best:

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 |    200 |     2 |     22 | 0 |
|    3 | 300 |   NULL |  NULL | NULL |
|    4 | 400 |   NULL |  NULL | NULL |
+----+--------+------+--------+-------+
4 rows in Set (0.00 sec)

Mysql> SELECT * from product left JOIN Product_details
On (product.id = product_details.id)
WHERE product_details.id=2;
+----+--------+----+--------+-------+
| ID | Amount | ID | Weight | exist |
+----+--------+----+--------+-------+
|    2 |  200 |     2 |     22 | 0 |
+----+--------+----+--------+-------+
1 row in Set (0.01 sec)

The first query uses the on condition to determine all rows of data that are eligible to be retrieved from the Product_details table in the left join.

The second query makes a simple left join, and then uses the WHERE clause to filter out data rows that do not meet the criteria from the data in the left join.

Let's look at some examples:

Mysql>
Mysql> SELECT * from product left JOIN Product_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 not matched to records in the Product_details table (product.id = Product_details.id and product.amount=100 Condition does not match to any data)

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 |    200 |     2 |     22 | 0 |
|    3 | 300 |   NULL |  NULL | NULL |
|    4 | 400 |   NULL |  NULL | NULL |
+----+--------+------+--------+-------+
4 rows in Set (0.01 sec)

Again, all data rows from the product table are retrieved, and a single piece of data is matched.

Use WHERE ... Left JOIN of IS NULL clause

When you use the WHERE ... What happens when you are a NULL clause?

As mentioned earlier, the Where condition query occurs after the match phase, which means where ... The IS NULL clause filters out data rows that do not meet the matching criteria from the data after the match phase.

It seems clear on paper, but when you use multiple conditions in the ON clause, you're confused.

I have summed up an easy way to understand the above:

Use is NULL as a negation matching criterion
Use! (A and B) = =! A OR! B Logical Judgment

Take a look at the following example:

Mysql> SELECT a.* from Product a left JOIN product_details b
On A.id=b.id and b.weight!=44 and b.exist=0
WHERE b.ID is NULL;
+----+--------+
| ID | Amount |
+----+--------+
|    1 | 100 |
|    3 | 300 |
|    4 | 400 |
+----+--------+
3 Rows in Set (0.00 sec)

Let's check the on matching clause:

(a.id=b.id) and (b.weight!=44) and (b.exist=0)

We can think of the IS NULL clause as a negative matching condition.

This means that we will retrieve the following line:

! (Exist (b.id, 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, equals to a.id) | | B.weight = 44 | | B.exist=1

As with logical and logical or expressions in C, the operands are evaluated from left to right. If the first argument is enough to determine the result of the operation, then the second argument is not evaluated (short-circuit effect)

See other examples:

Mysql> SELECT a.* 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 |
+----+--------+
|    1 | 100 |
|    2 | 200 |
|    3 | 300 |
|    4 | 400 |
+----+--------+
4 rows in Set (0.00 sec)

The Battle of Matching-conditions and Where-conditions

If you 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:

SELECT a.* from Product a left JOIN product_details b
On A.id=b.id and b.weight!=44 and b.exist=0
WHERE b.ID is NULL;

You can write this:

SELECT a.* from Product a left JOIN product_details b
On A.id=b.id
WHERE b.id is null or b.weight=44 or b.exist=1;

Mysql> SELECT a.* from Product a left JOIN product_details b
On A.id=b.id
WHERE b.id is null or b.weight=44 or b.exist=1;
+----+--------+
| ID | Amount |
+----+--------+
|    1 | 100 |
|    3 | 300 |
|    4 | 400 |
+----+--------+
3 Rows in Set (0.00 sec)

You may not write this:

SELECT a.* from Product a left JOIN product_details b
On A.id=b.id and b.weight!=44 and b.exist!=0
WHERE b.ID is NULL;

You can write this:

SELECT a.* from Product a left JOIN product_details b
On A.id=b.id
WHERE b.id is null or b.weight=44 or b.exist=0;

Mysql> SELECT a.* from Product a left JOIN product_details b
On 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 | 400 |
+----+--------+
4 rows in Set (0.00 sec)

Are these queries really the same effect?

If you only need the data from the first table, the queries will return the same result set. In one case, if you retrieve data from a table in a left join, the query results are different.

such as the former, where clause is filtered after the match phase.

For example:

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> SELECT * FROM Product a left JOIN product_details b
On A.id=b.id
WHERE b.id is NULL or b.weight=44 or b.exist=0;
+----+--------+------+--------+-------+
| ID | Amount | ID | Weight | exist |
+----+--------+------+--------+-------+
|    1 | 100 |   NULL |  NULL | NULL |
|    2 |    200 |     2 |     22 | 0 |
|    3 | 300 |   NULL |  NULL | NULL |
|    4 |    400 |     4 |     44 | 1 |
+----+--------+------+--------+-------+
4 rows in Set (0.00 sec)

General Note:

If you use a LEFT join to look for records that do not exist in some tables, you need to do the following: the where part of the col_name is null (where the col_name column is defined as not NULL), MYSQL queries to a match to a go join bar will stop searching for more rows (under a specific key combination).