sql 中 where和join 生成中间表问题

sql 中 where和join 生成中间表问题

      数据库在通过连接两张或多张表来返回记录时，都会生成一张中间的临时表，然后再将这张临时表返回给用户

• join on 的情况,以left join on 为例,它不管on的条件是否为真,都会把左表中全部记录返回到中间表里面,右表中符合on条件的会追加到左表记录后面
• where 条件的情况,是在临时表生成好之后(例如 from tableA,tableB,中间表则为 tableA记录和tableB记录做笛卡尔积),再根据条件对中间表过滤

示例,查询球员tom2个赛季的得分数差

表数据,  名字,得分,赛季

 

错误写法1 这种写法虽然在 on后面限定了只需要 t1.name='tom' 但是结果其他人的数据也一样被查出来了

SELECT
	t1. NAME NAME,
	(t1.score - t2.score) score
FROM
	testhw t1
LEFT JOIN testhw t2 ON t1. NAME = t2. NAME
AND t1.season = 1
AND t2.season = 2
AND t1. NAME = 'tom';

正确写法

SELECT
	t1. NAME NAME,
	(t1.score - t2.score) score
FROM
	testhw t1
LEFT JOIN testhw t2 ON t1. NAME = t2. NAME
AND t2.season = 2
WHERE
	t1. NAME = 'tom'
AND t1.season = 1;

错误写法2

SELECT
	t1. NAME NAME,
	(t1.score - t2.score) score
FROM
	testhw t1
LEFT JOIN testhw t2 ON t1. NAME = t2. NAME
AND t2.season = 2
WHERE
	t1. NAME = 'tom';

如果where 条件中不加 AND t1.season = 1 会额外计算出一个 "第二赛季-第二赛季的数据"

在 on 后面加额外限定的条件的结果对比 

SELECT
	t1. NAME,
	t1.season,
	t2.season,
	(t1.score - t2.score) score
FROM
	testhw t1
LEFT JOIN testhw t2 ON t1. NAME = t2. NAME
AND t2.season = 2
AND t1.season = 1
WHERE
	t1. NAME = 'tom';

on 后面的限定条件只会限定 t2表数据 不会对 t1表产生任何影响 例如 on 后面限定了 t1.season=1 但是结果中仍然包含 t1表 season=2 的数据

hive 版本 2.3.3

转载于:https://my.oschina.net/u/2969788/blog/3023123