MySQL multiple right joins

Question

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 does the following query return my blank tag but not my blank 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)

Answer 1

There is no row associating product id 4 with a tag. You need to add a row to the product_tag table like the following:

+------------+--------+
| product_id | tag_id |
+------------+--------+
|          4 |      7 | 
+------------+--------+

Answer 2

You are using right join. In your query tags ids are the base where MySQL will start matching. The right join is evaluated from right to left. If you break your query into two parts. 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 see there is not product_id that matches with the blank tag. The explains why joining this result with the product table will give you the result you saw.

If you use left join instead you'll get this result:

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