SELECT student_id,num
FROM
table_A AS a----别名,注意哦 这里没有括号
WHERE
channel = 3
AND NOT EXISTS -----别只会做加法,presto、SQL做减法查询,凡是另一个表出现的都不要都剔除
( SELECT 1 FROM table_B AS b -----别名,as可以省略
WHERE
a.student_id = b.student_id)
order by student_id desc
额外说一下,sql和presto、hive中,not exists 与not in的一点区别
使用not exists,将条件下推到里面,就不会出现子查询:
```sql
select *
from test t1
where info like '%test%'
and not exits (select 1 from test_back t2 where t2.id = t1.id)
使用 not in就会造成极大的性能损耗,例如:
select *
from test
where id not in (select id from test_back)
and info like '%test%';
----not in 参考代码
select sno,sname
from student
where sno not in
(select sno
from SC
where cno in
(select cno
from course
where tno in(
select tno from teacher
where tname='悟空')
)
)