mysql两个表按行连接,如何在MySQL中连接两个具有不同行数的表？

我有两个表要连接.

TABLE_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 |

+-----------+-----------+---------+

表-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 |

+-----------+-----------+-----------+------------+

我试着用这样的东西：

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;

但是,结果仅包括TABLE_A中存在row_id列的行.

有没有办法连接TABLE_A和TABLE_B以产生RESULT_TABLE中显示的结果？

解决方法:

如果您想要所有结果,则需要外连接,而不是内连接. (Inner仅返回匹配的行; outer返回所有行,匹配的行“拼接在一起”)

标签：mysql,database,join,sql

来源： https://codeday.me/bug/20190715/1463974.html