An in-depth discussion of MySQL left join

Even if you think you've had a deep understanding of MySQL's left JOIN, I bet this article might probably 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 "a left JOIN B on conditional expression"

The on condition ("a left JOIN B in conditional expression" on) is used to determine how data rows are retrieved from table B.

If none of the rows in table B match The on condition, an additional row will be generated for all columns with NULL data

The condition of the WHERE clause in the match phase is 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:

CREATE TABLE' product ' (' ID ' )int(Ten) unsigned not NULLauto_increment, ' Amount 'int(Ten) unsignedDEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=MyISAM auto_increment=5 DEFAULTCHARSET=UTF8;

CREATE TABLE' product_details ' (' ID ')int(Ten) unsigned not NULL, ' weight 'int(Ten) unsignedDEFAULT NULL, ' exist 'int(Ten) unsignedDEFAULT NULL,  PRIMARY KEY(' id ')) ENGINE=MyISAMDEFAULTCHARSET=UTF8;

INSERT  into product (id,amount)        VALUES (1,+), (2, +), (3,+), (4,+), (5,+);

INSERT  intoproduct_details (id,weight,exist)VALUES(2, A,0),(4, -,1),(5, -,0),(6, the,1),(7, the,1)

Perform:

SELECT *  from  Left JOIN product_details  on = product_details.id);

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

A question: What's the difference between the result sets of two queries?

1. SELECT * from product left JOIN product_details         = product_details.id)         and   product _details.id=2; 2. SELECT * from product left JOIN product_details         = product_details.id)         WHERE product _details.id=2;

The best way to understand this is to use examples directly:

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.

Let's look at some examples:

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), continue to look at:

Similarly, all data rows from the product table are retrieved, and a data match is made.

Use WHERE ... Left JOIN for the IS NULL clause

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:

SELECT A.*from leftjoins  product_details b       on a.ID=and b.weight ! = and B.exist=0        WHEREisNULL;

Let's check the on match clause:

(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.

Look at other examples:

SELECT A.*from leftjoins  product_details b       on a.ID=and b.weight ! = and B.exist=1        WHEREisNULL;

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.*, B.*from leftjoins  product_details bon a.id=and b.weight ! = and B.exist=0  WHERE  is NULL;

You can write this:

SELECT A.*, B.*from leftjoins  product_details bon a.id=b.idWHEREisnullOR b.weight=  - OR b.exist=1;

You can not write this:

SELECT A.*, B.*from leftjoins  product_details bon a.id= and b.weight!= and b.exist! =0  WHEREisNULL;

It can be written like this:

SELECT A.*, B.*from leftjoins  product_details bon a.id=b.idWHEREisnullOR b.weight=  - OR b.exist=0;

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:

SELECT *  from  Left JOIN product_details b        on a.id= and b.weight ! = and b.exist  =1        WHEREisnull;

SELECT *  from  Left JOIN product_details b         on a.id=b.id       WHEREisNULLOR b.weight =  - OR b.exist=0;

Summarize:

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 The left JOIN condition stops searching for more rows (under a specific key combination).

An in-depth discussion of MySQL left join