如何在 MySQL 中连接两个行数不同的表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1792850/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How can I join two tables with different number of rows in MySQL?
提问by BlueMark
I have two tables which I want to connect.
我有两个要连接的表。
TABLE_A:
表_A：
+-----------+-----------+---------+
| row_id | category | val_1 |
+-----------+-----------+---------+
| 1067 | cat1 | 6.5 |
| 2666 | test | 6.5 |
| 2710 | cat1 | 2.1 |
| 2710 | test | 7.1 |
| 2767 | test | 3 |
| 71142 | cat1 | 5 |
| 50666 | other | 6.5 |
| 71142 | other | 1 |
| 345342 | cat1 | 6.5 |
| 345342 | test | 2.8 |
+-----------+-----------+---------+
TABLE_B:
表_B：
+-----------+-----------+
| row_id | val_2 |
+-----------+-----------+
| 1067 | 2.0 |
| 2666 | 9 |
| 2701 | 2.2 |
| 2708 | 1 |
| 2709 | 6.5 |
| 2710 | 5.2 |
| 2765 | 6.5 |
| 2766 | 15 |
| 2767 | 8 |
| 71142 | 5 |
| 2783 | 4.5 |
| 50666 | 6.5 |
| 101588 | 9 |
| 101588 | 3 |
| 3452 | 8.0 |
| 23422 | 5 |
| 345342 | 6.5 |
+-----------+-----------+
RESULT_TABLE:
结果表：
+-----------+-----------+-----------+------------+
| row_id | val_2 | val_1 | category |
+-----------+-----------+-----------+------------+
| 1067 | 2.0 | 6.5 | cat1 |
| 2666 | 9 | 6.5 | test |
| 2701 | 2.2 | 2.2 | NULL |
| 2708 | 1 | 1 | NULL |
| 2709 | 6.5 | 1 | NULL |
| 2710 | 5.2 | 2.1 | cat1 |
| 2710 | 5.2 | 7.1 | test |
| 2765 | 6.5 | 1 | NULL |
| 2766 | 15 | 1 | NULL |
| 2767 | 8 | 3 | test |
| 71142 | 5 | 5 | cat1 |
| 71142 | 5 | 1 | other |
| 2783 | 4.5 | 1 | NULL |
| 50666 | 6.5 | 6.5 | other |
| 101588 | 9 | 1 | NULL |
| 101588 | 3 | 1 | NULL |
| 3452 | 8.0 | 1 | NULL |
| 23422 | 5 | 1 | NULL |
| 345342 | 6.5 | 6.5 | cat1 |
| 345342 | 6.5 | 2.8 | test |
+-----------+-----------+-----------+------------+
I tried to use something like this:
我试图使用这样的东西：
SELECT TABLE_A.row_id, TABLE_A.category, TABLE_A.val_1, TABLE_B.val_2
FROM TABLE_A
INNER JOIN TABLE_B ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY row_id;
However, the result included only rows where the row_idcolumn exists in TABLE_A.
但是，结果仅包括row_id列存在于 TABLE_A 中的行。
Is there way to connect TABLE_A and TABLE_B to produce the result shown in RESULT_TABLE?
有没有办法连接 TABLE_A 和 TABLE_B 以产生 RESULT_TABLE 中显示的结果？
回答by Jason Williams
If you want all the results, you need an outerjoin, not an innerone. (Inner only returns the rows where there is a match; outer returns all rows, with the matching rows "stitched together")
如果您想要所有结果，则需要外连接，而不是内连接。（内部只返回匹配的行；外部返回所有行，匹配的行“缝合在一起”）
回答by baleful
Try an outer join.
尝试外部联接。
SELECT TABLE_A.row_id, TABLE_A.category, TABLE_A.val_1, TABLE_B.val_2
FROM TABLE_B
LEFT OUTER JOIN TABLE_A ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY row_id;
回答by Welbog
This ought to do it:
这应该这样做：
SELECT
TABLE_B.row_id row_id,
TABLE_A.category category,
COALESCE(TABLE_A.val_1,1) val_1,
TABLE_B.val_2 val_2
FROM TABLE_A
RIGHT OUTER JOIN TABLE_B
ON TABLE_B.row_id = TABLE_A.row_id
ORDER BY TABLE_B.row_id;
The RIGHT OUTER JOINpulls all the records from Table_B even if they don't exist in Table_A, and the COALESCEstatement is a function that returns its first non-NULL parameter. In this case, if there is no value in Table_A, it will return 1, which is what your example result lists as the desired output.
将RIGHT OUTER JOIN提取所有从表-B的记录，即使他们不表-A存在，而COALESCE语句是返回其第一个非NULL参数的函数。在这种情况下，如果 Table_A 中没有值，它将返回1，这就是您的示例结果列为所需输出的内容。
