In-depth understanding of left join in mysql

ON clause and WHERE clause are different
A simple method to better understand complex matching conditions with WHERE... is null clauses
Matching-Conditions is different from Where-conditions.

A note ON "a left join B ON conditional expression"

ON condition (ON in "a left join B on condition expression") is used to determine how to retrieve data rows from Table B.

If no row of data in Table B matches the ON condition, an additional row of data with all columns being NULL is generated.

None of the WHERE clause conditions in the matching phase will be used. The WHERE clause condition is used only after the matching stage is complete. It will retrieve and filter the data generated in the matching phase.

Let's look at an lfet join example:

Mysql> create table 'product '(
'Id' int (10) 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 (10) unsigned not null,
'Weight' int (10) unsigned default NULL,
'Exist' int (10) 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 (, 0), (, 1), (, 0), (, 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 |
| 5 | 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 | 1 | 100 | NULL |
| 1 | 200 | 2 | 22 | 0 |
| 4 | 300 | NULL |
| 5 | 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 are the differences between the following two query result sets?

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;

It is better to understand it using examples:

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 | 1 | 100 | NULL |
| 1 | 200 | 2 | 22 | 0 |
| 4 | 300 | NULL |
| 1 | 400 | 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 |
+ ---- + -------- + ------- +
| 1 | 200 | 2 | 22 | 0 |
+ ---- + -------- + ------- +
1 row in set (0.01 sec)

The first query uses the ON condition to retrieve all data rows from the product_details table of left join.

The second query performs a simple left join operation, and then uses the WHERE clause to filter out non-conforming data rows from the left join data.

Let's take a 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 | 1 | 100 | NULL |
| 2 | 200 | NULL |
| 4 | 300 | NULL |
| 1 | 400 | NULL |
+ ---- + -------- + ------ + -------- + ------- +
4 rows in set (0.00 sec)

All data rows from the product table are retrieved, but no matching record is found in the product_details table (the product. id = product_details.id AND product. amount = 100 condition does not match 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 | 1 | 100 | NULL |
| 1 | 200 | 2 | 22 | 0 |
| 4 | 300 | NULL |
| 1 | 400 | NULL |
+ ---- + -------- + ------ + -------- + ------- +
4 rows in set (0.01 sec)

Similarly, all data rows from the product table are retrieved and one data row matches.

Use left join of WHERE... is null clause

What happens when you use the WHERE... is null clause?

As mentioned above, the WHERE condition query occurs after the matching phase, which means that the WHERE... is null clause filters out data rows that do not meet the matching conditions from the data after the matching phase.

It looks clear ON paper, but you are confused when you use multiple conditions in the ON clause.

I have summarized a simple way to understand the above situation:

Use is null as a condition for negative matching
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 |
| 5 | 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 regard the is null clause as a negative matching condition.

This means we will retrieve the following rows:

! (Exist (B. id that equals to a. id) AND B. weight! = 44 AND B. exist = 0)
! Exist (B. id that 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 logical OR expressions in C, the operands are evaluated from left to right. If the first parameter is sufficient to judge the operation result, the second parameter will not be calculated (short circuit effect)

Take a look at 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 |
| 5 | 400 |
+ ---- + -------- +
4 rows in set (0.00 sec)

Battle between Matching-Conditions and Where-conditions

If you place the basic query conditions in the ON clause and the rest of the negative conditions in the WHERE clause, you will get the same result.

For example, you may not write like 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 as follows:

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 |
| 5 | 400 |
+ ---- + -------- +
3 rows in set (0.00 sec)

You may not write it like 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 as follows:

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 |
| 5 | 400 |
+ ---- + -------- +
4 rows in set (0.00 sec)

Are these queries really the same?

If you only need data in the first table, these queries return the same result set. One scenario is that if you retrieve data from a left join table, the query results are different.

For example, the WHERE clause is used for filtering after the matching stage.

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

Note:

If you use left join to find records that do NOT exist in some tables, you need to perform the following test: col_name is null in the WHERE section (WHERE the col_name column IS defined as not null ), after MYSQL finds a condition that matches the left join condition, it stops searching for more rows (under a specific combination key ).