MySQL RIGHT JOIN Explained

RIGHT JOIN is a type of join operation in MySQL that returns all the records from the right table and matching records from the left table. 

If there are records in the right table that do not have a match in the left table, these records will still be returned. 

What is in our Store table:

mysql> SELECT * FROM Stores;
+------+--------+------------+-----------------+-----------+-------+
| s_id | city   | store_name | product         | available | price |
+------+--------+------------+-----------------+-----------+-------+
|    1 | London | London_1   | Gold PSU        |       153 |   100 |
|    2 | London | London_2   | Gold PSU        |        75 |   100 |
|    3 | Berlin | Berlin_1   | Green PSU       |        50 |   120 |
|    4 | Berlin | Berlin_2   | XYZ Motherboard |         5 |    75 |
|    5 | Moscow | Moscow_1   | Extension Cable |        50 |    25 |
|    6 | Moscow | Moscow_2   | LPT Cables      |       500 |    10 |
|    7 | Miami  | Miami_1    | COM Cables      |      1450 |     5 |
|    8 | Paris  | Paris_1    | NIC             |       350 |    15 |
+------+--------+------------+-----------------+-----------+-------+
8 rows in set (0.00 sec)

mysql>

What is in Documents:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
+------+-----------------+------+
4 rows in set (0.00 sec)

mysql>

We need more inserts, for testing purposes:

mysql> INSERT INTO Documents (d_id, internal_number)
    -> VALUES (NULL, 6584568);
Query OK, 1 row affected (0.08 sec)

mysql> INSERT INTO Documents (d_id, internal_number)
    -> VALUES (NULL, 9988559);
Query OK, 1 row affected (0.17 sec)

mysql>

In both statements, the NULL value for d_id indicates that the database should automatically generate a unique identifier for the new row.

Documents now:

mysql> SELECT * FROM Documents;
+------+-----------------+------+
| d_id | internal_number | s_id |
+------+-----------------+------+
|    1 |         1235547 |    1 |
|    2 |         2223371 |    3 |
|    3 |         6687451 |    4 |
|    4 |         6548529 |    2 |
|    5 |         6584568 | NULL |
|    6 |         9988559 | NULL |
+------+-----------------+------+
6 rows in set (0.00 sec)

mysql>

RIGHT JOIN example:

mysql> SELECT stores.city, stores.store_name, documents.internal_number
    -> FROM Stores
    -> RIGHT JOIN Documents
    -> ON stores.s_id = documents.s_id;
+--------+------------+-----------------+
| city   | store_name | internal_number |
+--------+------------+-----------------+
| London | London_1   |         1235547 |
| Berlin | Berlin_1   |         2223371 |
| Berlin | Berlin_2   |         6687451 |
| London | London_2   |         6548529 |
| NULL   | NULL       |         6584568 |
| NULL   | NULL       |         9988559 |
+--------+------------+-----------------+
6 rows in set (0.00 sec)

mysql>

The SELECT statement selects the columns stores.city, stores.store_name, and documents.internal_number.

The FROM clause specifies that data should be selected from the Stores table and a RIGHT JOIN should be performed with the Documents table.

A RIGHT JOIN returns all rows from the right table (Documents) and matching rows from the left table (Stores). In this case, it returns all the rows from the Documents table and only the matching rows from the Stores table.

The ON clause specifies the condition for the join, which is that the s_id column in the Stores table should match the s_id column in the Documents table.

There are also two rows with NULL values for the city and store_name columns, which correspond to the new documents that were inserted without an associated store ID.

What is s_id ?

s_id is a column in the Stores table, which is used as a foreign key in the Documents table to establish a relationship between the two tables. 

The s_id column in the Documents table references the primary key of the Stores table, which is usually called id or store_id. By using a foreign key constraint, we can ensure that the values in the s_id column of the Documents table always refer to an existing record in the Stores table. 

What are foreign keys ?

A foreign key is a column or a combination of columns in a relational database table that provides a link between data in two tables. It refers to the primary key of another table.

The purpose of foreign keys is to maintain referential integrity in a database.

This means that data in one table must match data in another table. When data is inserted or updated in a table, the foreign key ensures that the corresponding data is present in the referenced table.

If there is no matching data in the referenced table, the foreign key constraint will prevent the operation from being completed, ensuring the integrity of the database.

Foreign keys are an important part of relational database design, as they help to establish and maintain relationships between tables.