mysql - 希望删除子查询

Question

此查询适用于我，但我不想使用子查询。我尝试了很多但都失败了，我可以在没有子查询的情况下使用这个查询吗？

select a1.active_id
from active_table a1
where a1.active_id
NOT IN ( select a2.active_id from view a2 group by a2.active_id )
AND datediff(now(),active_date)>30

Answer 1

使用LEFT JOIN:

SELECT a1.active_id FROM active_table a1 
LEFT JOIN view a2
ON a1.active_id = a2.active_id
WHERE a2.active IS NULL
AND datediff(now(),active_date)>30

LEFT JOIN/IS NULL 等同于 NOT IN。详细解释见:

What's the difference between NOT EXISTS vs. NOT IN vs. LEFT JOIN WHERE IS NULL?