MySQL LEFT JOIN usage analysis

Look at its grammar first.

The left JOIN keyword returns all rows from the left-hand table (TABLE_NAME1), even if the right table
(table_name2) does not have a matching row.

Left JOIN keyword syntax

The code is as follows	Copy Code
SELECT column_name (s) From table_name1 Left JOIN table_name2 On Table_name1.column_name=table_name2.column_name

Let's give a plain explanation.
Example Table A
Aid Adate
1 A1
2 A2
3 A3
Table B
Bid Bdate
1 B1
2 B2
4 B4
Two table a,b connected, to remove fields with the same ID

The code is as follows	Copy Code
SELECT * from a INNER join B on a.aid = B.bid

This is the only matching data to be fetched.
At this point, the removal is:
1 A1 B1
2 A2 B2
So the left join means:

The code is as follows	Copy Code
SELECT * from a LEFT join B on a.aid = B.bid

First remove all the data from table A and then add the data that matches the A,b
At this point, the removal is:
1 A1 B1
2 A2 B2
3 A3 NULL characters
There is also the right join
It means to first remove all the data from table B and then add the data that matches the a,b.
At this point, the removal is:
1 A1 B1
2 A2 B2
4 NULL character B4

On and where analysis in the left join

The on clause differs from the WHERE clause
• A better understanding with WHERE ... Simple method of complex matching criteria for IS NULL clause

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:

The code is as follows

Copy Code

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?

The code is as follows	Copy Code
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:

The code is as follows

Copy Code

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 the data rows that do not meet the criteria from the data in the left join