When LEFT JOIN joins tables, the problem of invalid multiple conditions after ON
/ * * create table * * / / * * create table 1**/CREATE TABLE `product` (`id` INT (10) UNSIGNED NOT NULL AUTO_INCREMENT, `quantit` INT (10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;/** create table 2**/CREATE TABLE `product_ details` (`id` INT (10) UNSIGNED NOT NULL, `weight` INT (10) UNSIGNED DEFAULT NULL, `weight` INT (10) UNSIGNED DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=INNODB DEFAULT CHARSET=utf8 / * * insert data * * / INSERT INTO product (id,amount) VALUES (1100), (2200), (3300), (4400); INSERT INTO product_details (id,weight,exist) VALUES (2 FROM product;SELECT 22), (4, 4, 4, 4), (5, 5, 5, 0), (6, 6, 66); / * * query data * * / SELECT * FROM product;SELECT * FROM product_details
1. Left outer link query
/ * * left join query * * / SELECT * FROM product LEFT JOIN product_detailsON (product.`id` = product_ details.`id`)
(true low with 51CTO watermark!)
SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product_details.id=2
This query uses the ON condition to retrieve all matching rows from LEFT JOIN's product_details table.
SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) WHERE product_details.id=2
This query does a LEFT JOIN, and then uses the WHERE clause to filter out ineligible rows from the LEFT JOIN's data.
Let's look at another example:
SELECT * FROM product LEFT JOIN product_details ON product.id = product_details.id AND product.amount=100
All rows from the product table were retrieved, but no records were matched in the product_ details table
(the product.id = product_details.id AND product.amount=100 condition does not match any data)
SELECT * FROM product LEFT JOIN product_details ON (product.id = product_details.id) AND product.amount=200
All the rows from the product table are retrieved, and one piece of data matches.
From the above, we can see that the WHERE condition occurs after the matching phase!
