为什么MySQL的LEFT JOIN返回"NULL"?用WHERE子句记录什么时间? [英] Why MySQL's LEFT JOIN is returning "NULL" records when with WHERE clause?

问题描述

今天，我尝试了一些更复杂的MySQL查询，并且我注意到MySQL的LEFT JOIN不能与WHERE子句一起使用.我的意思是，它确实会返回一些记录，但不会返回右侧为空的记录.

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.

例如，假设我们要到表:

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 ;

当我运行此查询时:

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

我明白了:

artist title tracks  rank
------ ----- ------ -----
Mark   CD #7     35    15

但是当我在此查询中将"WHERE"替换为"AND"时，我得到:

but when I replace "WHERE" with "AND" in this query I get:

artist     title tracks  rank
------ --------- ------ -----
Mark       CD #7     35    15
John Doe  Mix CD     20  NULL

为什么第一个不返回带有"NULL"的记录(null不等于17 ...)

Why the first one is not returning records with "NULL" (null is not equal to 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.

推荐答案

左连接条件和条件过滤器都不相同.物理连接完成后，数据由where子句过滤.如果您查找左联接，则通常会返回左表中的每一行，但是一旦有了where子句，它将过滤联接的输出，因此结果类似于内部联接.您将需要关注下图左侧的两个图表.

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.

这篇关于为什么MySQL的LEFT JOIN返回"NULL"?用WHERE子句记录什么时间?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！