如何避免两个派生表的笛卡尔积，返回对应值配对行结果？

数据库结构（MySQL v8.0） #

CREATE TABLE users (
  userId INT
);

INSERT INTO users (userId) VALUES (1);

原查询问题 #

原查询代码如下：

SELECT userId, firstTable.name, secondTable.text 
FROM users
INNER JOIN (
    VALUES 
    ROW('FirstName'),
    ROW('secondName')
) firstTable(name)

INNER JOIN (
    VALUES 
    ROW('Some Text'),
    ROW('Some other text')
) secondTable(text)
 
WHERE userId = 1;

该查询返回了笛卡尔积结果：

但实际需要的是两个派生表的行按顺序配对的结果：

解决方案 #

要实现行配对，需要给每个派生表添加行号标识，通过行号进行关联，确保两行一一对应。修改后的查询如下：

SELECT u.userId, ft.name, st.text
FROM users u
INNER JOIN (
    SELECT name, ROW_NUMBER() OVER () AS rn
    FROM (
        VALUES 
        ROW('FirstName'),
        ROW('secondName')
    ) t(name)
) ft
INNER JOIN (
    SELECT text, ROW_NUMBER() OVER () AS rn
    FROM (
        VALUES 
        ROW('Some Text'),
        ROW('Some other text')
    ) t(text)
) st ON ft.rn = st.rn
WHERE u.userId = 1;

原理说明 #

• 利用ROW_NUMBER() OVER ()窗口函数为每个派生表的行生成唯一行号，行号会按照VALUES子句中定义的顺序分配。
• 通过两个派生表的行号rn进行关联，就能保证第一个派生表的第N行和第二个派生表的第N行对应，避免产生笛卡尔积。

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