SQL查询一个表中另外一个表不存在的数据

#方法一：使用 not in ,容易理解,效率低  ~执行时间为：1.395秒~

SELECT COUNT(1) FROM ecs_goods WHERE ecs_goods.goods_id NOT IN (SELECT ecs_member_price.goods_id FROM ecs_member_price);

 
#方法二：使用 left join...on... , "B.ID isnull" 表示左连接之后在B.ID 字段为 null的记录  ~执行时间：0.739秒~

SELECT COUNT(1) FROM ecs_goods LEFT JOIN ecs_member_price ON ecs_goods.goods_id=ecs_member_price.goods_id WHERE ecs_member_price.goods_id IS NULL;

#方法三：逻辑相对复杂,但是速度最快  ~执行时间: 0.570秒~

SELECT COUNT(1) FROM  ecs_goods c WHERE (SELECT COUNT(1) AS num FROM ecs_member_price WHERE ecs_member_price.goods_id=ecs_goods.goods_id) = 0; 

 

 

在thinkphp中可以这样来写

 

        $data = Db::name('article')
            ->field('article_id,article_title')
            ->where('article_cate_id',$cate_id)
            ->where('( select COUNT(1) AS num from news where news.news_article_id = article.article_id ) = 0')
            ->order('article_time desc')
            ->paginate(10,false,['query'=>request()->param()]);