HIVE踩坑——LEFTJOIN后⾯接AND和接WHERE的区别
LEFT JOIN 后⾯接AND和接WHERE的区别
主要结论
之前在LEFT JOIN这⼀块踩了不少坑，这⾥重点记录⼀下，主要结论如下：
LEFT JOIN 后⾯如果只接ON查询，会显⽰所有左表的数据，右表中的数据是否显⽰取决于后⾯的查询条件
LEFT JOIN 后⾯接WHERE查询，会根据WHERE条件先对数据进⾏过滤
LEFT JOIN 后⾯条件查询，条件⼀定不要恒为真，否则会出现笛卡尔积
LEFT JOIN 后⾯条件查询，条件⼀定不要恒为假，否则查询结果中右表数据始终为NULL
RIGHT JOIN 和LEFT JOIN 特性相同，INNER JOIN没这个特殊性，不管条件是放在ON中，还是放在WHERE中，返回的结果集是相同的。
具体验证
在HIVE中新建两张表test_user和test_class，其中test_user⽤来保存学⽣基本信息，test_class⽤来保存班级基本信息，为了快速查询，我们在Impala中进⾏相关SQL操作。
[nd1:21000]default>select*from test_user;
+----+------+-----+---------+
| id | name | age | classid |
+----+------+-----+---------+
|1|张三|20|1|
|2|李四|21|1|
|3|王五|22|2|
|4| Lucy |18|3|
|5| Jack |18|3|
|6| Tom  |18|3|
+----+------+-----+---------+
[nd1:21000]default>select*from test_class;
+----+----------+
| id | classname|
+----+----------+
|1| class101 |
|2| class102 |
|3| class103 |
|4| class104 |
+----+----------+
这⾥我们分别根据不同的SQL查询条件，来观察⽐较数据。
SQL01：只带ON的SQL查询
left join test_class t2 on t1.classid=t2.id
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class101 |
|2|李四| class101 |
|3|王五| class102 |
|4| Lucy | class103 |
|5| Jack | class103 |
|6| Tom  | class103 |
+----+------+----------+
SQL02：带ON和AND的SQL查询（带()和不带()）
select t1.id,t1.name,t2.classname from test_user t1
left join test_class t2
on t1.classid=t2.id and t1.id=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class101 |
|2|李四|NULL|
|3|王五|NULL|
|4| Lucy |NULL|
|5| Jack |NULL|
|6| Tom  |NULL|
+----+------+----------+
--这⾥我们把on后⾯的查询条件放在括号⾥⾯，结果⼀致。
select t1.id,t1.name,t2.classname from test_user t1
left join test_class  t2
on(t1.classid = t2.id and t1.id =1)
SQL03：带ON和AND的SQL查询
select t1.id,t1.name,t2.classname from test_user t1
left join test_class t2
on t1.classid=t2.id and t2.id=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class101 |
|2|李四| class101 |
|3|王五|NULL|
|4| Lucy |NULL|
|5| Jack |NULL|
|6| Tom  |NULL|
+----+------+----------+
⼩结：通过数据对⽐，我们发现，查询三条SQL都会显⽰出左表的全部数据，但是右表中的数据是否显⽰则取决于AND后⾯的限制条件，这⾥SQL02只显⽰了t1.id=1的右表中的数据，其它显⽰为NULL，SQL02只显⽰了t2.id=1的右表中的数据，其它显⽰为NULL。
SQL04：带ON和WHERE的SQL查询
left join test_class t2
on t1.classid=t2.id
where t1.id=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
sql left join 多表连接
|1|张三| class101 |
+----+------+----------+
SQL05：带ON和WHERE的SQL查询
select t1.id,t1.name,t2.classname from test_user t1
left join test_class t2
on t1.classid=t2.id
where t2.id=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class101 |
|2|李四| class101 |
+----+------+----------+
⼩结：通过数据对⽐，我们发现，当LEFT JOIN ON后⾯接WHERE条件的时候，数据会根据实际情况进⾏过滤，不会全部显⽰左表数据。SQL06： LEFT JOIN ON 后⾯的条件恒为真的SQL查询
select t1.id,t1.name,t2.classname from test_user t1
left join test_class t2
on1=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class104 |
|1|张三| class101 |
|1|张三| class102 |
|1|张三| class103 |
|2|李四| class104 |
|2|李四| class101 |
|2|李四| class102 |
|2|李四| class103 |
|3|王五| class104 |
|3|王五| class101 |
|3|王五| class102 |
|3|王五| class103 |
|4| Lucy | class104 |
|4| Lucy | class101 |
|4| Lucy | class102 |
|4| Lucy | class103 |
|5| Jack | class104 |
|5| Jack | class101 |
|5| Jack | class102 |
|5| Jack | class103 |
|6| Tom  | class104 |
|6| Tom  | class101 |
|6| Tom  | class102 |
|6| Tom  | class103 |
+----+------+----------+
SQL07： LEFT JOIN ON 后⾯的条件恒不为真的SQL查询
select t1.id,t1.name,t2.name,t1.dataday from test_user t1
left join test_class t2
on1=2
+----+------+------+
| id | name | classname |
+----+------+------+
|1|张三|NULL|
|2|李四|NULL|
|3|王五|NULL|
|4| Lucy |NULL|
|5| Jack |NULL|
|6| Tom  |NULL|
+----+------+------+
⼩结：通过数据⽐较，我们发现当我们在使⽤LEFT JOIN这种关联查询的时候，除⾮是业务需要，⼀定不要在ON后⾯接上恒为真或恒不为真的查询条件。
SQL08：INNER JOIN ON 带WHERE的SQL查询
select t1.id,t1.name,t2.classname from test_user t1
inner join test_class t2
on t1.classid=t2.id
where t1.id=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class101 |
+----+------+----------+
SQL09：INNER JOIN ON 带AND的SQL查询
select t1.id,t1.name,t2.classname from test_user t1
inner join test_class t2
on t1.classid=t2.id
and t1.id=1
+----+------+----------+
| id | name | classname|
+----+------+----------+
|1|张三| class101 |
+----+------+----------+
⼩结：通过数据⽐较，我们发现INNER JOIN不管条件是放在ON中，还是放在WHERE中，返回的结果集都是相同的。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。