为什么MySQL的LEFT JOIN返回"NULL"?用WHERE子句记录什么时间?

本文介绍了为什么MySQL的LEFT JOIN返回"NULL"?用WHERE子句记录什么时间?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

今天，我尝试了一些更复杂的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.