如何在查询中用JOIN替换NOT IN

Question

如何使用我提到的特定查询的连接编写查询?如果有人能帮我，那就太好了!!我正在尝试许多天，但我没有得到与Join的预期结果，我已经尝试了左，右连接，但我仍然没有得到适当的结果，

不加入

:

select distinct SchoolId, utc_timestamp
from schools 
where schoolId not in ( select schoolId
                        from school_grades
                        where gradeId like '2abaf802-70c5-4096-a830-7e8873ab3772')
  and graphCode in ('Florida');

Answer 1

你应该试试这个

SELECT DISTINCT
        s.SchoolId ,
        utc_timestamp
FROM    schools s
        INNER JOIN school_grades ON s.schoolId != schoolId
                                    AND gradeId LIKE '2abaf802-70c5-4096-a830-7e8873ab3772'
                                    AND s.graphCode IN ( 'Florida' ) ;

Answer 2

在这种情况下使用EXISTS比较合适:

select distinct s.SchoolId, utc_timestamp
from schools s
where NOT EXISTS (select  *
                        from school_grades
                        where gradeId like '2abaf802-70c5-4096-a830-7e8873ab3772' AND 
                              schoolId = s.schoolId)
  and s.graphCode in ('Florida');

取决于你为什么要用JOIN代替IN