sql union NOT EXISTS not in

最新推荐文章于 2024-05-09 09:19:04 发布

原创最新推荐文章于 2024-05-09 09:19:04 发布 · 205 阅读

0 ·

CC 4.0 BY-SA版权

本文探讨了使用SQL进行数据筛选的方法，包括如何通过不同类型的JOIN操作和子查询来排除特定角色的用户，以及如何利用NOT EXISTS来优化查询效率。




#SELECT a.userid FROM es_adminuser a left join es_user_role s on  s.roleid not in(2,3)
#SELECT userid FROM  es_user_role  where roleid not in(2,3)
#SELECT * FROM es_adminuser where  roleid not in(SELECT userid FROM  es_user_role  where roleid  in(2,3))
#SELECT a.userid FROM es_adminuser a left join es_user_role s on  s.userid = a.userid where s.roleid not in(2,3)
#SELECT s.userid FROM es_adminuser a , es_user_role s where s.roleid not in(2,3)
#SELECT a.userid FROM es_adminuser a , es_user_role s where s.roleid<>2 and s.roleid<>3
#SELECT userid FROM  es_user_role  where roleid not in(2,3) union 
#SELECT title FROM titles WHERE NOT EXISTS (SELECT title_id FROM sales WHERE title_id = titles.title_id)
#SELECT userid FROM es_adminuser WHERE NOT EXISTS (SELECT userid FROM es_user_role WHERE userid = es_adminuser.userid)
SELECT userid FROM  es_user_role  where roleid not in(2,3) union SELECT userid FROM es_adminuser WHERE NOT EXISTS (SELECT userid FROM es_user_role WHERE userid = es_adminuser.userid)  order by userid

select * from es_adminuser WHERE userid in(SELECT userid FROM  es_user_role  where roleid not in(2,3) union SELECT userid FROM es_adminuser WHERE NOT EXISTS (SELECT userid FROM es_user_role WHERE userid = es_adminuser.userid) order by userid)