exists语法真香

x33g5p2x  于2021-08-23 转载在 Java  
字(2.0k)|赞(0)|评价(0)|浏览(133)

一、用法

exists: ()内子查询语句返回结果不为空,说明where条件成立就会执行主sql语句。如果为空就表示where条件不成立,sql语句就不会执行。not exists 和 exists相反,子查询语句结果为空,则表示where条件成立,执行sql语句。否则不执行。

exists : 强调是否返回结果集,不要求知道返回什么,与in的区别就是,in只能返回一个字段值,exists允许返回多个字段。如:

select name from student where sex = 'm' and mark exists(select 1 from grade where ...) 

exists引导的子句有结果集返回,那么exists这个条件就算成立了,返回的字段始终为1,这个数字没有意义。所以exists子句不在乎返回什么,而是在乎是不是有结果集返回。

  • exists (sql返回的结果为真)
  • not exists (sql不返回的结果为真)

exists 效率一般优于 in ,使用EXISTS,Oracle会首先检查主查询,然后运行子查询知道它找到第一个匹配项,这就节省了时间。IN 子查询时,首先执行子查询,并将获得的结果列表存放在一个加了索引的临时表中。

exists 效率优于DISTINCT 当,提交一对多表信息的查询时,避免在SELECT 子句中使用DISTINCT

exists适合外表的结果集小的情况
in适合内外表都很大的情况

二、实例

1.exists简单用法

create table a(
  id int,
  name varchar(10)
);
insert into a values(1,'A1');
insert into a values(2,'A2');
insert into a values(3,'A3');

image.png

create table b(
  id int,
  aid int,
  name varchar(10)
);
insert into b values(1,1,'B1');
insert into b values(2,2,'B2');
insert into b values(3,2,'B3');

image.png

表A和表B示1对多的关系 A.ID => B.AID

SELECT ID,NAME FROM A WHERE 
EXISTS(SELECT * FROM B WHERE A.ID = B.AID)

执行结果:

image.png

分解

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=1) 
--->SELECT * FROM B WHERE B.AID=1有值返回TRUE所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=2) 
--->SELECT * FROM B WHERE B.AID=2有值返回TRUE所以有数据 

SELECT ID,NAME FROM A WHERE EXISTS (SELECT * FROM B WHERE B.AID=3) 
--->SELECT * FROM B WHERE B.AID=3无值返回TRUE所以没有数据 
  • NOT EXISTS 就是反过来

以上代码等价于:

SELECT id, name from A WHERE id in (
select aid from B)

SQL中IN, NOT IN, EXISTS, NOT EXISTS 的用法和差别:

  • IN:确定给定的值是否与子查询或列表中的值相匹配。IN关键字选择与列表中的任意一个值匹配的行。IN 关键字之后的项目必须用逗号隔开,并且括在括号中。
  • NOT IN : 通过 NOT IN 关键字引入的子查询也返回一列零值或更多值。
  • EXISTS 指定一个子查询,检测行的存在。相当于两个集合的交集。EXISTS 后面可是整句的查询语句,IN的后面只能是对单列。
  • NOT EXISTS 相当于两个集合的差集。
  • EXISTS 和 NOT EXISTS 返回的结果类型是 Boolean,如果子查询包含行,则返回TRUE/FALSE

二、EXISTS 替代DISTINCT

SELECT distinct dept_no, dept_name from dept D, EMP E 
WHERE D.dept_no = E.dept_no;
SELECT dept_no, dept_name from dept D 
WHERE EXISTS (
   SELECT 1 from emp E WHERE E.dept_no =D.dept_no
);

RDBMS 核心模块将在子查询的条件一旦满足后,立即返回结果,所以自带去重。

三、按A列去重,按B列取条件(如最大/小值等)

  1. 在下表中查询出,同一ID内的B_DATE最近的记录。

image.png

SELECT * from t t1 
   WHERE NOT EXISTS(
      select * from t where id = t1.id and b_date>t1.b_date)

结果:
image.png

分析:
子查询中,先看id = 1 的情形,只有当t1.b_date 取最大值时,没有返回结果,因为是NOT EXISTS关键字,所以Where条件成立,返回符合条件的查询结果。

出处:https://www.jianshu.com/p/cfdb70368589

相关文章

热门文章

更多