limited time offer

What is LEFT JOIN (Or LEFT OUTER JOIN) in MySQL ?

Returns all rows from the left table, and the matching rows from the right table. Thus the result will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table.

LEFT JOIN Syntax

SELECT colunm_name 
FROM table1 LEFT JOIN table2 
ON table1.colunm_name = table2.colunm_name;

Example of LEFT JOIN

Customer Table

+------------+--------------+------------+------------+--------+------------+-----------+
| CustomerID | CustomerName | ContactNo  | Address    | CityID | PostalCode | CountryID |
+------------+--------------+------------+------------+--------+------------+-----------+
|          1 | Tarun        | 9999075499 | Madan Puri |    124 |     122001 |        91 |
|          2 | Ram          | 9650423377 | A-487      |     11 |     110085 |        91 |
|          3 | Sham         | 1111111111 | A-485      |     11 |     110085 |        91 |
|          4 | Mohan        | 1234567890 | 454        |    124 |     122002 |        91 |
+------------+--------------+------------+------------+--------+------------+-----------+

Order Table

+---------+------------+---------------------+-----------+
| OrderID | CustomerID | OrderDate           | ShipperID |
+---------+------------+---------------------+-----------+
|       1 |          1 | 2019-10-02 21:23:12 |        11 |
|       2 |          3 | 2019-10-02 21:23:27 |        12 |
|       3 |          5 | 2019-10-02 21:39:21 |        12 |
+---------+------------+---------------------+-----------+

LEFT JOIN Query

use Shopping;
SELECT C.CustomerName, O.OrderID
FROM Customer C
LEFT JOIN Orders O
ON C.CustomerID = O.CustomerID
ORDER BY C.CustomerName;

Output

+--------------+---------+
| CustomerName | OrderID |
+--------------+---------+
| Mohan        |    NULL |
| Ram          |    NULL |
| Sham         |       2 |
| Tarun        |       1 |
+--------------+---------+