SQL表关联中的逻辑与效率

最新推荐文章于 2024-06-13 19:25:53 发布

原创

最新推荐文章于 2024-06-13 19:25:53 发布 · 4.1k 阅读

3 ·

CC 4.0 BY-SA版权

本文探讨了SQL关联查询中ON和WHERE子句的区别，重点分析了它们在内连接和外连接中的作用及效率。通过vertica和impala数据库的执行计划，展示了不同位置的过滤条件如何影响查询性能。在外连接中，ON后的条件可能导致效率降低，而WHERE后的条件在某些情况下可以被优化器提前处理。通过实例展示了如何以较高效率实现特定的查询逻辑，并强调了理解ON和WHERE的逻辑差异对于编写高效SQL的重要性。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

入职后用了SQL也有一年了，对SQL和各个数据库的了解也渐渐多了起来。感觉SQL的关联蛮有意思的，在这里写一下自己对这玩意的认识的总结吧。

本篇主要是通过目前接触到的两个数据库–vertica和impala(Oracle和MySql也有，但是个人用的不多)的执行计划来写的，优化器做到差不多地步的数据库应该大同小异

P.S.本篇所用的表数据量都在数千万到数亿不等。如果用一个只有几十几百行的小表来跑的话，很可能数据库的优化器就不再浪费时间做优化了。
又P.S. 两个数据库执行计划相同的话，就只截了一张图

一、关联中的ON与WHERE
在关联中这两个子句都可以起到对数据过滤的作用，但他们过滤的时机不同。ON主要是关联之前过滤，WHERE则是关联之后过滤。虽然现在大部分数据库优化器都可以自动把WHERE的过滤条件提前了，但是WHERE在某些时候还是会和ON有区别的，同样的过滤条件放在WHERE或ON后面可能会有完全不同的结果。下面就详细的分析一下。

1、内连接
由于表名不重要，所以此处隐去了表名

语句1
SELECT
    t1.*,
    t2.*
FROM
    xxxx t1
INNER JOIN
    yyyy t2
ON
    t1.city_id = '10303'
    AND t2.city_id = '10305'
    AND t1.oid = t2.oid

这条语句在vertica和impala中生成的执行计划是差不多的，分别是
vertica和IMPALA中的执行计划（由于他们的执行计划一样，lz比较懒就放了一张图上来)：

从执行计划里可以看到，他们都是把on中的过滤条件，提前到了扫描数据表的时候执行，这样可以减少需要关联的数据条目数，提高执行效率