exists和join

use db_hive2;
--设置为本地运行模式
set hive.exec.mode.local.auto=true;
-- exists用法
with
t1 as (select s_id from score where c_id=1),  --修1科目的学生
t2 as (select s_id from score where c_id=2),  --修2科目的学生
t3 as (select s_id from t1 where not exists (select * from t2 where t1.s_id=t2.s_id))
--修1科目的学生中不修2科目的学生
select * from student,t3 where student.s_id=t3.s_id;
-- 各种连接
select * from score;
select * from student;
-- 内连接 :这三个的查询结果一样，都是两个表共同拥有的数据
select * from student inner join score s on student.s_id = s.s_id;
select * from student join score s on student.s_id = s.s_id;
select * from student,score where student.s_id=score.s_id;
-- 左连接，保留左边的全部数据，右面没有的用null填充 如student-score,student数据全
select * from student
left join score s on student.s_id = s.s_id;
-- 右连接:保留右边全部数据，如student-score,score数据全
select * from student
right join score s on student.s_id = s.s_id;
--左连接和右连接可以互相转化
-- 如
select * from student
left join score s on student.s_id = s.s_id;
-- 和
select * from score
right join student s on score.s_id = s.s_id;