php 为什么 MySQL 的 LEFT JOIN 在使用 WHERE 子句时返回“NULL”记录？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28589529/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why MySQL's LEFT JOIN is returning "NULL" records when with WHERE clause?
提问by nass
Today I've tried some more complex MySQL queries and I've noticed that MySQL's LEFT JOIN is not working with WHERE clause. I mean, it does return some records but it does not return the ones which are empty on the right side.
今天我尝试了一些更复杂的 MySQL 查询，我注意到 MySQL 的 LEFT JOIN 不适用于 WHERE 子句。我的意思是，它确实返回了一些记录，但它不返回右侧为空的记录。
For example let's say we've got to tables:
例如，假设我们有表格：
albums ; albums_rap id artist title tracks ; id artist title rank ---- -------- ---------- --------------- ; ---- --------- ----- -------------- 1 John Doe Mix CD 20 ; 3 Mark CD #7 15 2 Mark CD #7 35 ;
And when I run this query:
当我运行此查询时：
SELECT
t1.artist as artist,
t1.title as title,
t1.tracks as tracks,
t2.rank as rank,
FROM
albums as t1
LEFT JOIN
albums_rap as t2
ON
t1.artist LIKE t2.artist
AND
t1.title LIKE t2.title
WHERE
t2.rank != 17
I get this:
我明白了：
artist title tracks rank ------ ----- ------ ----- Mark CD #7 35 15
but when I replace "WHERE" with "AND" in this query I get:
但是当我在这个查询中用“AND”替换“WHERE”时，我得到：
artist title tracks rank ------ --------- ------ ----- Mark CD #7 35 15 John Doe Mix CD 20 NULL
Why the first one is not returning records with "NULL" (null is not equal to 17...)
为什么第一个不返回带有“NULL”的记录（null 不等于 17...）
I hope You understood what I meant and you'll explain somehow me the difference. Sorry for my bad english, it's not my mother tongue.
我希望你明白我的意思，你会以某种方式向我解释差异。抱歉我的英语不好，这不是我的母语。
回答by akhila vangala
A left join condition and where condition filter are not both same. Data is filtered by the where clause after the physical join is done. if you look a left join it will normally return every row from your left table, but once you have a where clause, it will filter the output of the join so the result is like an inner join. You will want to focus on the two diagrams on the left side of the image below.
左连接条件和 where 条件过滤器并不相同。物理连接完成后，数据由 where 子句过滤。如果您查看左连接，它通常会返回左表中的每一行，但是一旦您有了 where 子句，它将过滤连接的输出，因此结果类似于内部连接。您将需要关注下图左侧的两个图表。
回答by Big Data DBA
First Case :
第一种情况：
After joining, the records are getting filtered by the (WHERE clause). So only 1 result.
加入后，记录将被 (WHERE 子句) 过滤。所以只有1个结果。
Second Case(when you replace WHERE with AND)
第二种情况（当你用 AND 替换 WHERE 时）
Will return all join entries + entries satisfied in second condition (t2.rank != 17). That is why here you get 2 records ( one from join + another from AND clause)
将返回所有连接条目 + 满足第二个条件的条目 (t2.rank != 17)。这就是为什么在这里你会得到 2 条记录（一条来自 join + 另一条来自 AND 子句）
回答by Aditya Guru
The solution to this question becomes quite intuitive once one is aware of the execution order or Logical Query Processing Phases of the SQL statement. The order is: -
一旦了解了 SQL 语句的执行顺序或逻辑查询处理阶段，这个问题的解决方案就变得非常直观。顺序是：-
1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
10. ORDER BY
11. TOP
As ON is performed before the OUTER(LEFT/RIGHT) part(adding NULL valued rows) of the JOIN, the 1st case has rows with NULL values in rank column. In the 2nd case the rows get filtered out based on the values of the rank column after the OUTER JOIN(LEFT JOIN here) is performed. Hence, the rows with NULL values in the rank column are filtered out.
由于 ON 在 JOIN 的 OUTER(LEFT/RIGHT) 部分（添加 NULL 值行）之前执行，因此第一种情况在 rank 列中具有 NULL 值的行。在第二种情况下，在执行 OUTER JOIN（此处为 LEFT JOIN）后，根据排名列的值过滤掉行。因此，排名列中具有 NULL 值的行将被过滤掉。
One very important thing that can be noticed in your SQL query is the comparison with NULLThis area requires special attention as the NULL values when with NULL/NON-NULL values using the normal arithmetic operators result NULL(it is neither TRUE nor FALSE) because NULL means no value is available for comparison. This behavior is defined in the ANSI SQL-92 standard.(This can be overridden by turning ansi null(actual name may vary) parameter off in some SQL processors) So, your SQL query with where clause filters out rows with NULL value in rank column which might seem counter-intuitive due to "17 != NULL" seems TRUE ex-
在您的 SQL 查询中可以注意到的一件非常重要的事情是与 NULL 的比较此区域需要特别注意，因为当使用普通算术运算符使用 NULL/NON-NULL 值时 NULL 值会导致 NULL（它既不是 TRUE 也不是 FALSE），因为NULL 表示没有可供比较的值。此行为在 ANSI SQL-92 标准中定义。（这可以通过在某些 SQL 处理器中关闭ansi null（实际名称可能会有所不同）参数来覆盖）因此，带有 where 子句的 SQL 查询会过滤掉 rank 中具有 NULL 值的行由于“17 != NULL”而可能看起来违反直觉的列似乎是真实的，例如
NULL = NULL results NULL/UNKNOWN
NULL = NULL 结果 NULL/UNKNOWN
17 = NULL results NULL/UNKNOWN
17 = NULL 结果 NULL/UNKNOWN
17 != NULL results NULL?UNKNOWN
17 != NULL 结果 NULL?UNKNOWN
Some interesting posts/blogs for reference are:
一些有趣的帖子/博客供参考：
http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/http://blog.sqlauthority.com/2009/03/15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/http://www.xaprb.com/blog/2006/05/18/why-null-never-compares-false-to-anything-in-sql/
http://blog.sqlauthority.com/2009/04/06/sql-server-logical-query-processing-phases-order-of-statement-execution/ http://blog.sqlauthority.com/2009/03/ 15/sql-server-interesting-observation-of-on-clause-on-left-join-how-on-clause-effects-resultset-in-left-join/ http://www.xaprb.com/blog/ 2006/05/18/为什么空-从不比较-假到任何东西在-sql/
回答by David Zehr
"The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match."
“LEFT JOIN 关键字返回左表 (table1) 中的所有行，以及右表 (table2) 中的匹配行。当没有 match 时，右侧的结果为 NULL。”
The above quote is from w3schools.com
以上引用来自w3schools.com
However, when you place a WHERE clause on a LEFT JOIN, SQL now treats that as an INNER JOIN and:
但是，当您在 LEFT JOIN 上放置 WHERE 子句时，SQL 现在将其视为 INNER JOIN 并且：
"The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables."
“只要两个表中的列匹配，INNER JOIN 关键字就会从两个表中选择所有行。”
Also quoted from w3schools.com
TLDR;
TLDR；
The introduction of the WHERE clause to a LEFT OUTER JOIN gives the join the behavior of an INNER JOIN
将 WHERE 子句引入 LEFT OUTER JOIN 使连接具有 INNER JOIN 的行为
