Detailed analysis of the problem of adding where conditions using left join in MySQL

Current demand:

There are two tables, group and factor. One group corresponds to multiple factors. Now we want to query the valid groups and the corresponding valid factors . Both tables have the isDel logical deletion flag.

The first mistake :

SELECT g.*,f.* FROM groups g LEFT JOIN factor f ON f.groupId = g.id where g.isDel=0 and f.isDel=0

The LEFT JOIN keyword returns all rows from the left table (table_name1), even if there are no matching rows in the right table (table_name2).
If the table on the left does not have corresponding data in the table on the right, the query cannot be done because of the code problem of f.isDel=0 .

Wrong way of writing:

SELECT g.*,f.* FROM groups g LEFT JOIN factor f ON f.groupId = g.id and g.isDel=0 and f.isDel=0

This way of writing g.isDel=0 will not take effect, resulting in incorrect query data.

Cause Analysis:

Several knowledge points about where and on conditions in left join:

• Multi-table left join will generate a temporary table and return it to the user
• The where condition is to filter the last generated temporary table and filter out the records that do not meet the where condition.
• The on condition is to conditionally filter the right table of the left join, but all rows of the left table are still returned, and those that are not in the right table are filled with NULL
• If there are restrictions on the left table in the on condition, all rows of the left table will be returned regardless of whether the condition is true or false, but the matching values ​​of the right table will be affected. That is to say, the restriction conditions of the left table in on only affect the matching content of the right table, and do not affect the number of returned rows.

Correct way to write it:

SELECT g.*,f.* FROM groups g LEFT JOIN factor f ON f.groupId = g.id and f.isDel=0 where g.isDel=0

in conclusion:

1. To add conditional restrictions to the left table, add them in the where condition and do not put them after on.
2. Adding conditional restrictions to the right table needs to be placed after on. Adding them in the where condition will cause problems with the number of data rows.

This is the end of this article about analyzing the problem of using left join to add where conditions in MySQL. For more relevant content about adding where to MySQL left join, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!