MySQL LEFT JOIN Explained

MySQL LEFT JOIN is a type of join operation in MySQL that returns all the rows from the left table (the table mentioned first in the JOIN statement) along with the matched rows from the right table (the table mentioned second in the JOIN statement).

If there are no matches in the right table, the result will still include all rows from the left table with NULL values for the columns from the right table.

LEFT JOIN same as INNER JOIN ?

No, LEFT JOIN and INNER JOIN are not the same.

INNER JOIN returns only the matched rows from both tables, based on the join condition, while LEFT JOIN returns all the rows from the left table and the matched rows from the right table, and if there is no match on the right table, the columns for the right table will be filled with NULL values.

So, if you want to include all the rows from the left table, even if there is no matching row in the right table, you can use LEFT JOIN. 

However, if you only want to retrieve the matched rows from both tables, then INNER JOIN is the appropriate option.

Table Stores:

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>

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

LEFT JOIN example:

mysql> SELECT stores.city, stores.store_name, documents.internal_number
    -> FROM Stores
    -> LEFT 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 |
| Moscow | Moscow_1   |            NULL |
| Moscow | Moscow_2   |            NULL |
| Miami  | Miami_1    |            NULL |
| Paris  | Paris_1    |            NULL |
+--------+------------+-----------------+
8 rows in set (0.00 sec)

mysql>

We are performing a LEFT JOIN between the Stores and Documents tables based on the s_id column. 

The resulting table shows all records from the Stores table, and any matching records from the Documents table.

Since this is a LEFT JOIN, if there are no matching records in the Documents table for a particular store, the value in the internal_number column will be NULL. 

This is why the last 4 rows of the resulting table show NULL for the internal_number column.