Shulou(Shulou.com)06/01 Report--

This paper gives an example to illustrate the difference between the usage of left join setting condition in mysql when on and where. Share with you for your reference, the details are as follows:

First of all, 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', `aid` int (11) DEFAULT' 0' COMMENT'a form ID', `name` varchar (32) DEFAULT''COMMENT' name', PRIMARY KEY (`id`) ENGINE=InnoDB DEFAULT CHARSET=utf8

The data for the two tables is shown in the figure:

Run the following left join query:

Select * from a left join b on a.id = b.a_id

Let's add conditions after on and where respectively to see if 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

The above two statements, the condition we set is the same is b.id > 3, why the display results are different.

The keyword order of sql statement query is generally from > where > group by > having > order by.

Left join is in the range of from. The on condition will filter the right table of left join first, and then filter the result where after filtering.

Multiple left join will generate a temporary table, the on condition is conditional filtering on the right table of left join, and the where condition is filtering on the final generated temporary table.

So:

If the condition of b.id > 3 is written after on, the right table (associated table) is filtered first to get the qualified rows, and then the main table left join returns all the rows of the main table. The right table is not matched by null.

If the condition of b.id > 3 is written after where, the main table left join right side table (associated table) returns all rows, and then the where condition filters 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 of the main table is still displayed, but it affects the display of the right-hand table (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

More readers who are interested in MySQL-related content can check out this site topic: "MySQL query skills Collection", "MySQL Common function Summary", "MySQL Log Operation skills Collection", "MySQL transaction Operation skills Summary", "MySQL stored procedure skills Collection" and "MySQL Database Lock related skills Summary"

It is hoped that what is described in this article will be helpful to everyone's MySQL database design.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.