How can I join two tables with different number of rows in MySQL?

Question

I have two tables which I want to connect.

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

TABLE_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_id column exists in TABLE_A.

Is there way to connect TABLE_A and TABLE_B to produce the result shown in RESULT_TABLE?

Answer 1

If you want all the results, you need an outer join, not an inner one. (Inner only returns the rows where there is a match; outer returns all rows, with the matching rows "stitched together")

Answer 2

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;