SQL 查询 exist join in 的用法和相应的适用场景 （优化查询）

在SQL中常用的存在的 关联查询 exist join in ，优化查询

一、 “查询A表中在（或者不在）B表中的记录”

1、 join /in /exists 都可以用来实现，，这种查询，在查询的两个表大小相当的情况下，3种查询方式的执行时间通常是： exists <= in <= join
当表中字段允许NULL时，not in 的方式最慢；
not exists <= left join <= not in

比较：

• JOIN 和 IN
  select * from A where id in(select id from B)
  select * from A left join B on A.id = B.id
  使用join也可以实现这种功能（“查询A表中在（或者不在）B表中的记录”），但是往往吃力不讨好，因为还需要处理NULL，JOIN的使用场景是连接两个表，而不是判断一个表的记录是否在另一个表中

• in 和 exists
  select * from A where id in(select id from B)

但是，通常情况下，两个表中数据是一个较大，一个较小，这种情况下，
in适合子查询表B 数据小的情况
exists适合子查询表B 数据大的情况

原因：in在查询的时候，先查子查询的表B，然后将内表和外表做一个笛卡尔积，然后按照条件筛选，所以子查询表比较小的时候，in的速度较快； 而exists 是对外表A做loop循环，每次loop循环再对内表B进行查询，即我们先查询的不是子查询B的内容，而是查我们的主查询的表A，所以子查询表数据比较大的时候，exists的速度较快

select 3 in (1,2,3); -- true
select 3 in (1,2,4); -- false
select 3 in (1,2,null); -- null
select 3 not in (1,2,null); -- null

SELECT EXISTS (SELECT 1 FROM (SELECT 3 AS num) AS temp WHERE temp.num IN (1,2,4,3)) AS result; -- true
SELECT EXISTS (SELECT 1 FROM (SELECT 3 AS num) AS temp WHERE temp.num IN (1,2,4)) AS result; -- false
SELECT EXISTS (SELECT 1 FROM (SELECT 3 AS num) AS temp WHERE temp.num IN (1,2,4,null)) AS result; -- false

当 ansi_nulls 打开时，3 <> NULL 被解释为 UNKNOWN，因为在 ANSI SQL 中，任何与 NULL 进行比较的结果都是未知的。
当 ansi_nulls 关闭时，3 <> NULL 被解释为 TRUE，因为 SQL Server 认为 3 不等于 NULL。

ansi_nulls 表示是否严格遵循 ANSI SQL 标准，ansi_nulls

• not in 和 not exists
  select * from A where id not in(select id from B)

无论哪个表大，not exists 总是比 not in 执行效率高

2、sql性能优化 性能优化

3、 索引

• 单列索引（Single-Column Index）：
  单列索引是针对单个列创建的索引。在给定的表中，每个索引项只包含一个列的值。
  例如，CREATE INDEX idx_table_name ON table_name(name); 创建的是一个针对 name 列的单列索引。
  单列索引适用于对单个列进行查询、排序、过滤和连接操作。它可以加速对单个列的等值查询（=）、范围查询（BETWEEN）、排序查询（ORDER BY）等操作。

• 组合索引（Composite Index）：
  组合索引是针对多个列组合而成的索引。在给定的表中，每个索引项包含多个列的值。
  例如，CREATE INDEX idx_table_name ON table_name(name, comment_info); 创建的是一个针对 name 列和 comment_info 列的组合索引。
  组合索引适用于对多个列进行联合查询、排序、过滤和连接操作。它可以加速联合查询中的多个列的等值查询、范围查询、排序查询等操作。另外，组合索引也可以减少索引的数量，节省存储空间。

• 唯一索引（Unique Index）：
  唯一索引是保证索引列的值唯一的索引。在给定的表中，每个索引项包含的列的值都是唯一的。
  例如，CREATE UNIQUE INDEX unique_idx ON table_name(table_id); 创建的是一个唯一索引，确保 table_id 列中的值都是唯一的。
  唯一索引适用于确保表中某个列或列组的值唯一性，通常用于主键约束或唯一性约束。