Multiple MySQL Multiple Joins
mysql> select * from product;
+------------+---------------+
| product_id | name |
+------------+---------------+
| 1 | Car |
| 2 | House |
| 3 | Cat |
| 4 | Blank Product |
+------------+---------------+
4 rows in set (0.00 sec)
mysql> select * from tag;
+--------+-----------+
| tag_id | name |
+--------+-----------+
| 1 | Expensive |
| 2 | Fast |
| 3 | Mean |
| 4 | Large |
| 5 | Small |
| 6 | Alive |
| 7 | Blank Tag |
+--------+-----------+
7 rows in set (0.00 sec)
mysql> select * from product_tag;
+------------+--------+
| product_id | tag_id |
+------------+--------+
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 2 | 1 |
| 2 | 4 |
| 3 | 2 |
| 3 | 3 |
| 3 | 5 |
| 3 | 6 |
+------------+--------+
10 rows in set (0.00 sec)
Why is the following query returning my empty tag but not my empty product?
mysql> select * from product_tag right join product using (product_id)
right join tag using (tag_id);
+--------+-----------+------------+-------+
| tag_id | name | product_id | name |
+--------+-----------+------------+-------+
| 1 | Expensive | 1 | Car |
| 1 | Expensive | 2 | House |
| 2 | Fast | 1 | Car |
| 2 | Fast | 3 | Cat |
| 3 | Mean | 1 | Car |
| 3 | Mean | 3 | Cat |
| 4 | Large | 1 | Car |
| 4 | Large | 2 | House |
| 5 | Small | 3 | Cat |
| 6 | Alive | 3 | Cat |
| 7 | Blank Tag | NULL | NULL |
+--------+-----------+------------+-------+
11 rows in set (0.00 sec)
+1
s388787
a source
to share
3 answers
You are using the right connection. Tag IDs are the base where MySQL will start matching. Correct join is evaluated from right to left. If you split your request in two. The first one will be:
select * from product_tag right join tag using (tag_id);
+--------+-----------+------------+
| tag_id | name | product_id |
+--------+-----------+------------+
| 1 | expensive | 1 |
| 1 | expensive | 2 |
| 2 | fast | 1 |
| 2 | fast | 3 |
| 3 | mean | 1 |
| 3 | mean | 3 |
| 4 | larg | 1 |
| 4 | larg | 2 |
| 5 | small | 3 |
| 6 | alive | 3 |
| 7 | blank tag | NULL |
+--------+-----------+------------+
As you can see, there is no product_id that matches the empty tag. Explains why combining this result with the product table will give you the result you saw.
If you use left join you will get this output:
select * from product_tag left join product using (product_id) left join tag using (tag_id);
+--------+------------+-------+-----------+
| tag_id | product_id | name | name |
+--------+------------+-------+-----------+
| 1 | 1 | car | expensive |
| 2 | 1 | car | fast |
| 3 | 1 | car | mean |
| 4 | 1 | car | larg |
| 1 | 2 | house | expensive |
| 4 | 2 | house | larg |
| 2 | 3 | cat | fast |
| 3 | 3 | cat | mean |
| 5 | 3 | cat | small |
| 6 | 3 | cat | alive |
+--------+------------+-------+-----------+
+4
a source to share