如何用MySQL查询含无订单客户、无客户订单的全量关联数据

要返回包含已关联的客户订单、无对应客户的订单、无对应订单的客户的全量结果，由于MySQL不支持直接的FULL OUTER JOIN，可以通过LEFT JOIN结合UNION来实现，具体如下：

测试表结构与数据 #

-- 创建测试表
CREATE TABLE clients (
    clientid INT PRIMARY KEY,
    clientname VARCHAR(50)
);

CREATE TABLE orders (
    orderid INT PRIMARY KEY,
    ordername VARCHAR(50)
);

CREATE TABLE clientorder (
    clientid INT,
    orderid INT,
    PRIMARY KEY (clientid, orderid),
    FOREIGN KEY (clientid) REFERENCES clients(clientid),
    FOREIGN KEY (orderid) REFERENCES orders(orderid)
);

-- 插入测试数据
INSERT INTO clients VALUES (1, '客户A'), (2, '客户B'), (3, '客户C');
INSERT INTO orders VALUES (101, '订单X'), (102, '订单Y'), (103, '订单Z');
INSERT INTO clientorder VALUES (1, 101), (2, 102);

查询语句 #

SELECT c.clientid, c.clientname, o.orderid, o.ordername
FROM clients c
LEFT JOIN clientorder co ON c.clientid = co.clientid
LEFT JOIN orders o ON co.orderid = o.orderid
UNION
SELECT c.clientid, c.clientname, o.orderid, o.ordername
FROM orders o
LEFT JOIN clientorder co ON o.orderid = co.orderid
LEFT JOIN clients c ON co.clientid = c.clientid;

逻辑说明 #

• 第一部分查询：从clients表出发做两次左连接，确保所有客户都被返回，无订单的客户对应的订单字段会显示为NULL
• 第二部分查询：从orders表出发做两次左连接，确保所有订单都被返回，无对应客户的订单对应的客户字段会显示为NULL
• 使用UNION合并两组结果，自动去除重复的关联记录，得到完整的全量数据

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