双LEFT JOIN后过滤数据：获取关联异常终端的用户信息

问题背景 #

有三张数据表结构如下：

CREATE TABLE users (
    id serial NOT NULL,
    firstname varchar(64) NOT NULL,
    lastname varchar(64) NOT NULL,
    kid integer NOT NULL
);

CREATE TABLE keys (
    id serial NOT NULL,
    key varchar(1024) NOT NULL,
    tid integer NOT NULL
);

CREATE TABLE terminals (
    id serial NOT NULL,
    name varchar(64) NULL,
    ipaddr integer NOT NULL
);

已插入测试数据：

INSERT INTO users (firstname, lastname, kid) VALUES ('jas', 'wedrowniczek', 1);
INSERT INTO users (firstname, lastname, kid) VALUES ('mariusz', 'kolano', 2);
INSERT INTO users (firstname, lastname, kid) VALUES ('ziobro', 'zbigniew', 3);
INSERT INTO users (firstname, lastname, kid) VALUES ('kornel', 'makuszynski', 4);
INSERT INTO users (firstname, lastname, kid) VALUES ('henryk', 'sienkiewicz', 5);

INSERT INTO keys (key, tid) VALUES ('key1', 1);
INSERT INTO keys (key, tid) VALUES ('key2', 2);
INSERT INTO keys (key, tid) VALUES ('key3', 3);
INSERT INTO keys (key, tid) VALUES ('key4', 4);
INSERT INTO keys (key, tid) VALUES ('key4', 5);

INSERT INTO terminals (name, ipaddr) VALUES ('pokoj1', 180879367);
INSERT INTO terminals (name, ipaddr) VALUES ('pokoj2', 180879468);
INSERT INTO terminals (name, ipaddr) VALUES ('pokoj3', 288704395);
INSERT INTO terminals (name, ipaddr) VALUES (NULL, 288703396);
INSERT INTO terminals (name, ipaddr) VALUES ('pokoj5', 0);

需求是获取所有关联到无名称（name为NULL）或IP地址为0的终端的用户信息，预期结果：

id | firstname |   lastname   |  ipaddr   | name    |
----+-----------+--------------+-----------+----------
  4 | kornel    | makuszynski  | 288703396 |         |
  5 | henryk    | sienkiewicz  | 0         | pokoj5  |

尝试的SQL语句返回空结果：

SELECT u.id, u.firstname, u.lastname, t.ipaddr, t.name
FROM users u
LEFT JOIN keys k ON (u.kid = k.id)
LEFT JOIN terminals t ON (t.id = k.tid)
WHERE
    t.ipaddr = 0
    OR t.name IS NULL;

疑问：认为SQL执行顺序是先SELECT，再WHERE过滤，最后LEFT JOIN，想知道能否在双LEFT JOIN完成后用关联数据过滤。

问题分析与解决方案 #

1. 纠正SQL执行顺序的错误理解 #

SQL的实际执行顺序和你想的完全相反，正确流程是：

• 先执行FROM和JOIN操作，生成包含所有关联数据的中间结果集
• 再执行WHERE子句过滤中间结果
• 之后才是SELECT选择要输出的字段，最后是排序、分页等操作

所以完全可以在JOIN完成后用WHERE过滤关联后的数据，你的原语句返回空的核心问题是关联逻辑和过滤条件的搭配错误。

2. 错误原因 #

你使用了LEFT JOIN，但在WHERE中直接过滤t表的字段，这会把LEFT JOIN变相转换成INNER JOIN——如果某个用户没有关联的keys或terminals，t的字段会是NULL，而NULL = 0不成立，NULL IS NULL虽然成立，但你的目标用户是有有效关联的，用LEFT JOIN反而容易引入干扰，应该用INNER JOIN来匹配确实存在关联的用户。

3. 正确SQL语句 #

直接使用INNER JOIN关联所有表，再过滤符合条件的终端：

SELECT u.id, u.firstname, u.lastname, t.ipaddr, t.name
FROM users u
INNER JOIN keys k ON u.kid = k.id
INNER JOIN terminals t ON k.tid = t.id
WHERE t.name IS NULL OR t.ipaddr = 0;

如果一定要用LEFT JOIN（虽然没必要），可以把终端的过滤条件放到JOIN的ON子句中，再过滤掉无有效终端关联的行：

SELECT u.id, u.firstname, u.lastname, t.ipaddr, t.name
FROM users u
LEFT JOIN keys k ON u.kid = k.id
LEFT JOIN terminals t ON k.tid = t.id AND (t.name IS NULL OR t.ipaddr = 0)
WHERE t.id IS NOT NULL;

这两个语句都能得到你想要的预期结果。

内容的提问来源于stack exchange，提问作者Mario Bash