在mysql的JOIN中，ON和WHERE之间有什么区别？
在mysql的JOIN中，ON和WHERE之间有什么区别？
可能重复问题：
SELECT *
from T1
JOIN T2 ON T1.X = T2.X
AND T1.Y = T2.Y
和
SELECT * from T1 JOIN T2 ON T1.X = T2.X WHERE T1.Y = T2.Y
之间有什么区别？
MySQL supports two types of join operations: inner join and outer join. The main difference between these two types lies in the way they handle unmatched rows.
In an inner join, only the matching rows from both tables are returned. The ON clause is used to specify the join condition. It filters out any rows that do not satisfy the join condition. This means that only the rows with matching values in the join columns will be included in the result.
On the other hand, an outer join returns all the rows from one table and the matching rows from the other table. If there is no match for a row in the other table, NULL values are returned for the fields of that table. This is where the difference between ON and WHERE clauses in joins becomes apparent.
When using ON clause in an outer join, the join condition is still applied, but unmatched rows are not filtered out. Instead, they are included in the result with NULL values for the fields of the other table.
In contrast, the WHERE clause is used to specify additional conditions on the result set after the join operation has taken place. It is applied after the join, so it can filter out rows from the result set, including unmatched rows in an outer join.
To illustrate this, consider the following example:
SELECT * FROM table1 LEFT JOIN table2 ON table1.id = table2.id WHERE table2.name = 'John';
In this query, we perform a left outer join between table1 and table2 using the ON clause to match rows based on the id column. The WHERE clause is then used to filter out rows where the name column in table2 is not 'John'. This means that only the rows with a matching id and name as 'John' will be included in the result.
In summary, the key difference between ON and WHERE clauses in MySQL joins is that the ON clause is used to specify the join condition, whereas the WHERE clause is used to filter the result set after the join has taken place. Understanding this distinction is crucial when working with different types of joins and ensuring the desired outcome is achieved.
在MySQL的JOIN语句中，ON和WHERE的区别仅仅存在于外连接(outer join)中。我们可以通过下面的两个例子来解释这个区别，这样你就能更好地理解了。在实际应用中，这些例子通常都会被重写为INNER JOIN。
例子1:
SELECT * FROM t1
LEFT OUTER JOIN t2
ON ( true )
WHERE false
例子2:
SELECT * FROM t1
LEFT OUTER JOIN t2
ON ( false )
WHERE true
问题的出现主要是因为对于外连接，ON和WHERE子句在筛选数据时会有不同的作用。在例子1中，ON子句中的条件为true，意味着t1和t2表会进行连接，然后WHERE子句中的条件为false，意味着不会返回任何数据，即结果集为空。
而在例子2中，ON子句中的条件为false，意味着t1和t2表不会进行连接，结果集中t1表的所有数据都会被返回，然后WHERE子句中的条件为true，意味着只有满足条件的数据才会被返回。
解决方法就是根据实际需求来选择使用ON还是WHERE子句。如果想在连接之前对数据进行筛选，可以使用WHERE子句；如果想在连接之后对数据进行筛选，可以使用ON子句。
总结起来，ON子句用于连接表，WHERE子句用于筛选数据。对于内连接，ON和WHERE子句的作用是一样的，但对于外连接，它们的作用是不同的。
