mysql exists深入理解

前言：

某天用exists语句时发现跟我的理解有出入，我以前理解的exists是子查询的结果不会影响最终返回的结果，只要子查询查到有结果，则返回true,没有则返回false。即下面图中的sql，只要house_appraisal不是空表，那么两条sql都应该等价于

SELECT h.* FROM house h WHERE 1=1

但结果却是有所不同，第二条语句像是被筛选了一样


我怀疑是我以前理解有误，但问了gpt确实是这样理解没错

1.exists的本质

  exists是一个用于判断子查询结果是否为空的逻辑操作符。它用于检查exists括号内是否返回了至少一行结果。如果子查询返回了至少一行结果，则返回true，否则返回false。
  由于这种特性使用exists子查询时，数据库引擎会在找到满足条件的第一行后立即停止查询，大大提升了查询效率

2.现象解释

  问了大佬之后，发现原来exists括号内没有跟主表联系的话确实是按原先理解的方式执行，但是有了跟主表的关联之后就会执行相当于对结果集筛选的操作。
  比如下面这条语句，在括号内你无论对house_appraisal表怎么筛选（比如where后面加个and id=‘114514’ and create_time >xxx…等等）只要括号内但凡有一条数据，那么就会返回house全表的列

SELECT  h.* FROM house h 
WHERE EXISTS 
  (SELECT 1 FROM
    house_appraisal ha 
  WHERE ha.house_id > 5)

但是如果加了主表相关联的东西，如下面这条语句，要保证括号内是true的情况下，不仅SELECT 1 FROM house_appraisal ha WHERE ha.house_id > 5 必须有至少一条结果，还要相当于保证 AND h.house_id = ha.house_id 为true，这就导致最终结果会以h.house_id = ha.`house_id为筛选条件

SELECT h.*  FROM house h 
WHERE EXISTS 
  (SELECT 1 FROM
    house_appraisal ha 
  WHERE ha.house_id > 5 
    AND h.`house_id` = ha.`house_id`)

即等价于

SELECT h.*  FROM house h 
WHERE EXISTS 
  (SELECT 1 FROM
    house_appraisal ha 
  WHERE ha.house_id > 5  AND h.`house_id` = ha.`house_id`)

用GPT用in改写上面的sql拿去数据库执行得到相同结果，说明这种理解方式是对的

今日收获：

SELECT * FROM stu,class,grade WHERE stu.id='001' AND class.name='web'

这条sql相当于stu、class、grade三个表做笛卡尔积然后进行筛选，其结果集等价于

SELECT * FROM stu 
INNER JOIN class ON stu.class_id = class.id 
INNER JOIN grade ON stu.grade_id = grade.id
WHERE stu.id = '001' AND class.name = 'web';

  但inner join 操作会先根据连接条件筛选出需要关联的行进行合并，而不是生成所有可能的组合。这大大节省了存储和处理的开销，数据量大的话必须用inner join，不要给后来人挖坑。

2024.10.16日补充

有些同学还是看不懂exists怎么运作，这里补充最一下最底层的原理。

SELECT h.*  FROM house h 
WHERE EXISTS 
  (SELECT 1 FROM
    house_appraisal ha 
  WHERE ha.house_id > 5)

上面这条语句只要house_appraisal 表存在ha.house_id > 5，那么这条语句相当于SELECT h.* FROM house h WHERE1=1

举个例子说明原理：

假设 audit_report_info 表中有以下数据：

id	project_id
1	100
2	200
3	300

假设 project_basic_info 表中有以下数据：

id	protal_project_id
100	1835650242202759170
200	1835650242202759171
300	1835650242202759170

如果执行以下sql

SELECT id AS reportId, project_id AS projectId 
FROM audit_report_info 
WHERE EXISTS (
    SELECT 1 
    FROM project_basic_info 
    WHERE protal_project_id = '1835650242202759170' AND 
          id = audit_report_info.project_id
);

对于 audit_report_info 表中的每一条记录：
 id = 1，project_id = 100：
 子查询 SELECT 1 FROM project_basic_info WHERE protal_project_id = ‘1835650242202759170’ AND id = 100 返回 TRUE。
 id = 2，project_id = 200：
 子查询 SELECT 1 FROM project_basic_info WHERE protal_project_id = ‘1835650242202759170’ AND id = 200 返回 FALSE。
 id = 3，project_id = 300：
 子查询 SELECT 1 FROM project_basic_info WHERE protal_project_id = ‘1835650242202759170’ AND id = 300 返回 TRUE。

reportId	projectId
1	100
3	300

  如果你把AND id = audit_report_info.project_id ，子查询就变成SELECT 1 FROM project_basic_info WHERE protal_project_id = ‘1835650242202759170’ ，而1835650242202759170是存在的，所以exits返回的结果必然是true，相当于1=1了。这样会导致所有 audit_report_info 表中的记录都被选中，即使project_id不符合要求。