SQL查询⼀个表中另外⼀个表不存在的数据
sql语句怎么查询两张表的数据
#⽅法⼀：使⽤ not in ，容易理解，效率低  ~执⾏时间为：1.395秒~
SELECT COUNT(1) FROM ecs_goods WHERE ds_id NOT IN (SELECT ecs_ds_id FROM ecs_member_price);
#⽅法⼆：使⽤ , "B.ID isnull" 表⽰左连接之后在B.ID 字段为 null的记录  ~执⾏时间：0.739秒~
SELECT COUNT(1) FROM ecs_goods LEFT JOIN ecs_member_price ON ds_id=ecs_ds_id WHERE ecs_ds_id IS NULL;
#⽅法三：逻辑相对复杂，但是速度最快  ~执⾏时间: 0.570秒~
SELECT COUNT(1) FROM  ecs_goods c WHERE (SELECT COUNT(1) AS num FROM ecs_member_price WHERE ecs_ds_id=ds_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 ws_article_id = article.article_id ) = 0')
->order('article_time desc')
->paginate(10,false,['query'=>request()->param()]);