How can I join two tables with different number of rows in MySQL?
Solution 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")
Solution 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;
Solution 3
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 JOIN
pulls all the records from Table_B even if they don't exist in Table_A, and the COALESCE
statement 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.
BlueMark
“An expert is a person who has made all the mistakes that can be made in a very narrow field.” --Niels Bohr “By three methods we may learn wisdom: First, by reflection, which is noblest; Second, by imitation, which is easiest; and Third, by experience, which is the bitterest.” --Confucius “I have to keep going to find out ultimately what is the matter with it in the end.” --Richard P. Feynman “Men must be decent first and brilliant later, otherwise you're not helping people, just servicing the machine.” --Claire North “Everyone you will ever meet knows something you don't.” --Bill Nye “Everybody knows that something can't be done and then somebody turns up and he doesn't know it can't be done and he does it.” --Anonymous “First step in solving any problem is recognizing there is one.” --Aaron Sorkin “You can edit a bad page, but you can’t edit a blank page.” --Jodi Picoult
Updated on August 15, 2022Comments
-
BlueMark almost 2 years
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?
-
Michael Todd over 14 yearsTry a full join instead.
-