Analysis of the difference between the usage of left join setting conditions in on and where in mysql

This article uses examples to illustrate the difference between the usage of on and where conditions for left join in MySQL. Share with you for your reference, the details are as follows:

1. First, we prepare two tables for testing.

CREATE TABLE `a` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `name` varchar(32) DEFAULT '' COMMENT 'name',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `b` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
 `a_id` int(11) DEFAULT '0' COMMENT 'a table ID',
 `name` varchar(32) DEFAULT '' COMMENT 'name',
 PRIMARY KEY (`id`)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;

The data of the two tables are shown in the figure:

Run the following left join query:

select * from a left join b on a.id = b.a_id;

We add conditions after on and where respectively to see whether the execution results are the same.

select * from a left join b on a.id = b.a_id and b.id > 3;

select * from a left join b on a.id = b.a_id where b.id > 3;

In the above two statements, the conditions we set are the same, both are b.id > 3, why the displayed results are different.

The keyword sequence of SQL statement query is generally from > where > group by > having > order by

When left join is in the from range, the on condition will first filter the right table of the left join, and then filter the results of the where condition.

Multiple left joins will generate a temporary table. The on condition is to filter the left join right table, and the where condition is to filter the last generated temporary table.

so:

If the condition b.id > 3 is written after on, the right table (related table) is first screened to obtain the rows that meet the conditions, and then the main table is left joined to return all the rows of the main table. The rows that are not matched in the right table are represented by null.

If the condition b.id > 3 is written after where, the main table is left joined with the right table (associated table) to return all rows, and then the where condition is used to filter the results.

Note: The condition after on is for the table on the right (the associated table) and has no effect on the main table.

select * from a left join b on a.id = b.a_id and a.id > 3;

We added the condition a.id > 3 for the main table after on, but all the data in the main table is still displayed, but it affects the display of the table on the right (the associated table).

If you want to filter the main table, you should write the condition after where.

select * from a left join b on a.id = b.a_id where a.id > 3;

Readers who are interested in more MySQL-related content can check out the following topics on this site: "MySQL query skills", "MySQL common functions summary", "MySQL log operation skills", "MySQL transaction operation skills summary", "MySQL stored procedure skills" and "MySQL database lock related skills summary"

I hope this article will be helpful to everyone's MySQL database design.