前言

一、说个真事：LEFT JOIN 好好的客户少了小一半

去年有个团队订单程序从 MySQL 迁 KES。回归绿灯，上线第二天财务就来——对账报表少了好几个客户。最终揪出一句特别不起眼的查询：

SELECT c.cust_name,o.order_no。o.amount
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'PAID';

说到需求直白。列出所有客户，每人带已支付订单。怎么说呢，LEFT JOIN 写得明明白白，按理左表一行不该漏。一跑——没订单的客户全没了。第一反应这方面，KES 有 bug。真不是原样丢回 MySQL、Oracle 一样少这几行，毛病在 SQL 语义里。

痛点体现：对账少客导致财务审计受阻，业务方质疑程序可靠性。

二、外连接消除：LEFT JOIN 被改成了内连接

LEFT JOIN 说的“左表全保留”只管 ON 后面那串条件。WHERE 管不着——它是连接做完后对结果再筛一道，分不清外连接内连接。说起来，WHERE 里冲着右表去的条件。会让右表为 NULL 的行算出 Unknown，被过滤掉。

调整器更进一步，发现这条件天生排斥空值。直接把 LEFT JOIN 成 INNER JOIN。建表验证一下留意王五名下没订单：

CREATE TABLE customers );CREATE TABLE orders );INSERT INTO customers VALUES,,;INSERT INTO orders VALUES,,;-- 王五没订单

条件塞 WHERE，翻车写法：

SELECT c.cust_name。o.order_no,o.status
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'PAID';按理说，

cust_name | order_no | status
-----------+----------+--------
张三 | | PAID
李四 | | PAID

王五没了。按理说，EXPLAIN 看看到底改没改成内连接：

EXPLAIN SELECT c.cust_name。o.order_no
FROM customers c
LEFT JOIN orders o ON c.cust_id = o.cust_id
WHERE o.status = 'PAID';老实说，

 QUERY PLAN
--------------------------------------------------------------
Hash Join Hash Cond:
-> Seq Scan on customers c
-> Hash
-> Seq Scan on orders o Filter:

第一行 Hash Join没有 Left。对比不写 WHERE 的版本是 Hash Left Join带着 Left。老实说，这个 Left 在不在就是判定指纹。修法——条件挪进 ON：

SELECT c.cust_name,o.order_no。o.status
FROM customers c
LEFT JOIN orders o
ON c.cust_id = o.cust_id AND o.status = 'PAID';

cust_name | order_no | status
-----------+----------+--------
张三 | | PAID
李四 | | PAID
王五 | |

王五回来了。过滤右表又想保左表的，条件放 ON。

三、有的外连接怎么都消不掉

WHERE 里只要碰右表就铁定被消？不是，关键在空值拒绝谓词——条件对某列来讲，只要列为 NULL 就绝不可能成立。话说回来，

CREATE TABLE t1;CREATE TABLE t2;INSERT INTO t1 VALUES,;INSERT INTO t2 VALUES,;其实，

WHERE 条件	列为 NULL 时结果	空值拒绝？
t2.name2 = 'cc'	UNKNOWN → FILTERED OUT	true
t2.name2 IS NULL	true → 保留	false

空值拒绝的 WHERE 会触发消除，EXPLAIN 显示 Hash Join

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE t2.name2 = 'cc';

Hash Join -- 没 Left，降级成内连接
Hash Cond:
-> Seq Scan on t1
-> Hash
-> Seq Scan on t2 Filter:

而 不是空值拒绝。调整器保持外连接：

EXPLAIN SELECT * FROM t1 LEFT JOIN t2 ON t1.id1 = t2.id2 WHERE t2.name2 IS NULL;

`

Hash Left Join -- Left 在外连接保留
Hash Cond:
Filter的观点是，-> Seq Scan on t1
-> Hash
-> Seq Scan on t2

Left 在不在就是判定指纹。专门找空值的 放心写 WHERE .

四、隐式类型转换把索引废了

查询莫名变慢或数据匹配不上，SQL 看着又没毛病——十有八九是隐式类型转换导致索引失效。

CREATE TABLE t_user (
user_id INT PRIMARY KEY,phone_no VARCHAR
);CREATE INDEX idx_t_user_phone ON t_user;INSERT INTO t_user VALUES,;-- 索引废了
SELECT user_id,phone_no FROM t_user WHERE phone_no =;-- 正常走索引
SELECT user_id,phone_no FROM t_user WHERE phone_no = ''::character varying;

执行计划对比这方面，

Seq Scan on public.t_user
再看Filter，::bigint = ) -- 列被强转。索引废

Index Scan using idxtuserphone on public.tuser Index Cond:

The rule is simple: once you see a function or cast applied to an indexed column inside a FILTER clause,etc.),index will be ignored.

KES 对类型检查更严格；在 MySQL 上宽容的 “INT 与 VARCHAR 自动匹配” 在 KES 必须显式转换：

-- MySQL 宽容写法
SELECT * FROM a JOIN b ON a.id = b.code;

五、NULL 和三值逻辑陷阱

If a column contains null ,using `<`, `>` 或 `!=` 会把包含 null 的行排除，因为比较结果是 UNKNOWN。

sql CREATE TABLE t_user_status ( user_id INT,status VARCHAR );INSERT INTO t_user_status VALUES

sql -- 想找“未被删除”的使用者。却遗漏了 status 为 null 的记录 SELECT user_id,status FROM t_user_status WHERE status <> 'deleted';

text user_id | status --------+-------- 101 | active

Pain point:NULL 行被无声丢弃导致报表统计偏差。

Correct patterns

sql SELECT user_id,status FROM t_user_status WHERE status <> 'deleted' OR status IS NULL;不过，

or

聚合注意 COUNT counts every row;COUNT skips rows where col is NULL. When combined with LEFT JOIN。keep filter predicates inside ON clause so that outer join is preserved:

sql SELECT c.cust_name,COUNT AS paid_cnt FROM customers c LEFT JOIN orders o ON c.cust_id=o.cust_id AND o.status='PAID' GROUP BY c.cust_name;

Result keeps customers without paid orders . Moving condition to WHERE would turn join into an inner join and break count.

六、时灵时不灵的查询

The same SQL may return results in test environments but produce empty sets in production because of execution‑order dependence.

sql -- 假设 pkg_abc.set_id 有副作用，而 pkg_abc.get_id 用到该变量 SELECT * FROM my_table WHERE id1 = pkg_abc.get_id AND pkg_abc.set_id =;

The optimizer can reorder se predicates arbitrarily;if it evaluates pkg_abc.set_id after equality check fails early,side‑effect never happens—leading to nondeterministic behavior.

How to diagnose

sql EXPLAIN SELECT * FROM my_table WHERE id1=pkg_abc.get_id AND pkg_abc.set_id=;老实说，

Check “Filter” line to see how many times each function was called.

Best practice

Separate side‑effect logic from pure queries:

sql -- Step 1: set session state SELECT pkgabc.setid;

-- Step ​​​​​​​​​​​​​​​​ ​ ​ ​ ​ ​ ​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​ ​​​​ ​ ​  ←  单独执行 -- Step ​                      ←  确保副作用已经完成

-- Step �?，?，老实说，

Select * from mytable where id1=pkgabc.get_ ​ ...

Any function that mutates state should never appear in WHERE。JOIN…ON,or SELECT clauses of pure read queries.

七、MySQL迁过来的字符串差异

• 大小写敏感是常见坑：MySQL 默认 **case‑insensitive**。而 KES 默认 **case‑sensitive**。
• 空串 vs. NULL 差别也会导致查询不到预期数据。
• 字符集/排序规则不同也可能影响 LIKE/ILIKE 行为。

从示例来看，

-- 在 MySQL 能匹配到 ‘paid’。但 KES 因大小写敏感返回空集： SELECT * FROM demo_status WHERE status='paid';

Result这方面，

text id | status ---+--------- -- KES 找不到记录

Fixes

• Use explicit lower/upper conversion:

sql SELECT * FROM demo_status WHERE lower='paid';话说回来，

• Or use case‑insensitive operator ILIKE :

---

空串 VS NULL

MySQL 常把“未填”存为 ''。而在 KES 必须区分：

sql CREATE TABLE notedemo ( id INT,remark VARCHAR );INSERT INTO notedemo VALUES，;怎么说呢，

-- Only finds rows where remark IS NULL → misses empty string row: SELECT * FROM note_demo WHERE remark IS NULL;

Result only returns ``.

Combined check

sql SELECT * FROM note_demo WHERE remark IS NULL OR remark='';

Now both missing and empty values are captured.

八、最终说几句

• 这些陷阱的共同点是：老库上“凑合能跑”，迁到 KES 才暴露出来。怎么说呢，
• 根本原因往往是 **把声明式语言当过程式写** 、依赖 **隐式副作用** 、还有 **测试环境掩盖问题**。
• 排查问题时先跑一次 `EXPLAIN`/`EXPLAIN ANALYZE` 看看调整器到底怎么计划执行，比盲目猜测数据库是否有 bug 更可靠。
• 养成「先看执行计划，再看实际结果」的习惯。让业务异常快速定位到 SQL 本身，而不是硬件或数据库实现缺陷。

---