MySQL LEFT JOIN

Last Updated: 25 June, 2023

In MySQL, the LEFT JOIN keyword is used to combine rows from two or more tables based on a related column between them. It returns all rows from the left table and the matching rows from the right table. If there are no matching rows in the right table, it will still return the rows from the left table with NULL values for the columns of the right table.

The syntax for a LEFT JOIN in MySQL is as follows:

 SELECT column1, column2, ...
 FROM table1
 LEFT JOIN table2 ON table1.column = table2.column;

Here, table1 and table2 are the names of the tables we want to join, and column is the column that relates the two tables. The SELECT statement specifies the columns we want to retrieve from the joined tables.

Here's a simple example to illustrate the usage of LEFT JOIN

Suppose we have two tables, customers and orders, with the following structures:

Table: customers

  +----+--------------+
  | id | customer_name|
  +----+--------------+
  |  1 | John         |
  |  2 | Emily        |
  |  3 | James        |
  +----+--------------+

Table: orders

  +-----+---------+------------+
  | id  | order   | customer_id|
  +-----+---------+------------+
  | 101 | Item1   | 1          |
  | 102 | Item2   | 1          |
  | 103 | Item3   | 3          |
  +-----+---------+------------+

To get a result set that combines the customer names with their respective orders (if any), you can use the following query:

 SELECT customers.customer_name, orders.order
 FROM customers
 LEFT JOIN orders ON customers.id = orders.customer_id;

This would produce the following result:

  +--------------+---------+
  | customer_name| order   |
  +--------------+---------+
  | John         | Item1   |
  | John         | Item2   |
  | Emily        | NULL    |
  | James        | Item3   |
  +--------------+---------+

As we can see, the LEFT JOIN operation includes all rows from the customers table, and the matching rows from the orders table. In this case, Emily has no orders, so the corresponding order column contains NULL.