关于Presto的LEFT JOIN，有一个很容易被忽略的点

LEFT JOIN 在 SQL 查询中是一种很常见的数据查询操作，其查询出来的数据是以左表为主表，保留左表的记录，如果右表没有匹配的记录，依然会返回左表的记录，此时右表的字段用 NULL 填充。

但是，在 Presto 中使用 LEFT JOIN 联结表查询，并需要对左表或右表进行过滤时，有一个特别容易忽略的地方是过滤条件的位置——对左表过滤时条件应写在 where 语句后，对右表过滤时条件应写在 on 后面。

如果对左表的过滤条件写在 on 后面，那么左表的每一行记录都会被保留，相当于没有过滤左表的数据。如果对右表的过滤条件写在 where 后面，那么 LEFT JOIN 查询出来的结果就后 JOIN 查询出来的结果一样，左表没有与右表匹配的记录也会被过滤。

下面通过一个例子来说明。

有两张表——用户信息表和用户地址表，表名分别为：tmp_user 和 tmp_addr，表中的记录如下：

表 tmp_user：

user_id	nickname
1	jack
2	johnson
3	marry

表 tmp_addr：

user_id	city	country
1	Nanjing	China
2	NewYork	USA

这里省略建表、插入数据步骤，直接使用 WITH 和 UNION ALL 关键字来模型这两张表的数据：

with tmp_user as (
	select 
		1 as user_id,
		'jack' as nickname
	union all 
	select 
		2 as user_id,
		'johnson' as nickname
	union all 
	select 
		3 as user_id,
		'marry' as nickname
),

tmp_addr as (
	select 
		1 as user_id, 
		'Nanjing' as city,
		'China' as country
	union all 
	select
		2 as user_id,
		'NewYork' as city,
		'USA' as country
)

现在使用 LEFT JOIN，以表 tmp_user 为主表联结表 tmp_addr 进行查询。

首先，两张表都不加过滤条件：

select
	*
from
	tmp_user t1
left join tmp_addr t2 on
	t1.user_id = t2.user_id

其查询结果如下：

user_id	nickname	user_id	city	country
1	jack	1	Nanjing	China
2	johnson	2	NewYork	USA
3	marry	[NULL]	[NULL]	[NULL]

user_id 为 3 的用户，没有对应的地址信息，右表的字段使用 NULL 填充。与我们预期的结果一致。

接下来，对左表应用过滤条件，只查 user_id = 1 的用户。

过滤条件写在 where 语句后：

select
	*
from
	tmp_user t1
left join tmp_addr t2 on
	t1.user_id = t2.user_id
where
	t1.user_id = 1

其查询出来的结果如下，与我们预期的一致：

user_id	nickname	user_id	city	country
1	jack	1	Nanjing	China

过滤条件写在 on 后：

select
	*
from
	tmp_user t1
left join tmp_addr t2 on
	t1.user_id = t2.user_id
	and t1.user_id = 1

结果如下：

user_id	nickname	user_id	city	country
1	jack	1	Nanjing	China
3	marry	[NULL]	[NULL]	[NULL]
2	johnson	[NULL]	[NULL]	[NULL]

左表 tmp_user 的每一行记录都返回来了。

最后，对右表应用过滤条件，只查出 country = ‘China’ 的地址。

过滤条件写在 where 后：

select
	*
from
	tmp_user t1
left join tmp_addr t2 on
	t1.user_id = t2.user_id
where
	t2.country = 'China'

返回结果：

user_id	nickname	user_id	city	country
1	jack	1	Nanjing	China

结果与我们期望的不一致，我们希望即使用户地址表的 country 字段不是 China，也要返回左表的数据。

过滤条件写在 on 后：

select
	*
from
	tmp_user t1
left join tmp_addr t2 on
	t1.user_id = t2.user_id
	and t2.country = 'China'

结果如下：

user_id	nickname	user_id	city	country
1	jack	1	Nanjing	China
3	marry	[NULL]	[NULL]	[NULL]
2	johnson	[NULL]	[NULL]	[NULL]

与我们期望的一致。

（END）