How can I join two tables with different number of rows in MySQL?
database, join, mysql, sql
Solution
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")
Problem
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?