SQL中in和exists的异同

 

-- 学生表
CREATE TABLE t_student (                 
    id BIGINT PRIMARY KEY,               
    name VARCHAR(255),                   
    gender VARCHAR(255),                 
    age INT,                             
    address VARCHAR(255),                
    INDEX idx_age (age)                  
);                                       

-- 分数表
CREATE TABLE t_stu_score (
    stu_id BIGINT,
    course VARCHAR(255),
    score INT(255),
    INDEX idx_userid_course (user_id, course),
    INDEX idx_score (score),
    UNIQUE INDEX uk_userid_course (user_id, course)
);

 

 

in和exists有什么异同点？

相同点：in和exists都可以作为where的条件，用于子查询

 

exists用法：

select a.* from A a where exists(select 1 from B b where a.id=b.id)；

select stu.* from t_student stu where exists (select stu_score.* from t_stu_score stu_score where stu_score.stu_id = stu.id)

说明：                  A表是主查询的表，B表是子查询的表， A表驱动B表，exists的作用是检查子查询是否有返回结果，如果有，主查询中指定的列。如果子查询返回空集，则主查询将返回空集。

执行先后和次数：先执行一次主查询（有a条结果），并将结果放在内存，然后执行a次子查询，共a+1次数据库交互

命中索引情况：   子查询可以命中索引，主查询不走索引

适用的情况：       A表能命中的记录少，B表能命中的记录多

 

in用法：

select a.* from A a where a.id in (select id from B)；

select stu.* from t_student stu where stu.id in (select stu_score.stu_id from t_stu_score stu_score)

说明：                   A表是主查询的表，B表是子查询的表， B表驱动A表，先查出子查询的结果集，在执行多次主查询，判断主表的字段是否存在于子查询的返回结果集里

执行先后和次数： 先执行子查询（有b条结果），并将结果放在内存，后针对b个结果，执行b次主查询，共b+1次数据库交互

命中索引情况：    主查询和子查询都可以走索引

适用情况：            A表能命中的记录多，B表能命中的记录少，in还可以固定几个值

 

怎么选：

子查询结果集小选in，主查询结果集小选exists，差不多大小再结合索引来看
小表驱动大表（结果集小的驱动结果集大的）
IN查询在主查询表和子查询表上都可以命中索引
Exists查询只能在子查询表上命中索引

not exists和not in的比较

not exists能对子查询走索引

not in （通常）主查询和子查询都不能走索引

结论：子查询基本上选择not exists

 

 常用的一种写法，从多表查询出结果

select a.*, b.* from A a, B b where a.id = b.id  等价于使用inner join

 

left join ：左连接  A为主，B为辅

 

 

right join：右连接 B为主，A为辅

 

inner join：内连接，A与B

 

full join：全连接，A或B

 

 

 

full join:两个查询的列合并

union：两个查询的行合并，去重

union all：两个查询的行合并，不去重