In this lesson I will show you how to use a LEFT JOIN to retrieve related records from a MySQL database.
The MySQL LEFT JOIN is part of the SELECT statement.
In order to quickly review the format let us assume we have two tables as follows:
customers
orders
The first table lists the details of the customers of an online store. The orders table lists the orders that the customers place. It is possible that a single customer can place a single order, many orders or, indeed, no orders. We therefore need a method of linking these tables. This is usually done through an ID field.
A typical database table structure for the customer and order tables may be as follows:
The tables are linked via the customer_id field with a one-to-many relationship (one customer may have many orders).
If we need to retrieve all of the customers and their orders we can use a LEFT JOIN. Note that a LEFT JOIN will retreive all customers, including those who may not have placed orders.
It usually helps to see a Venn diagram of what is happening.
So, let us assume that we need to retrieve the customer name and address from the customer table and also the total of the order(s) placed from the orders table. We could use the following code:
The condition here is that the customers.customer_id must be equal to the orders.customer_id
RETRIEVING CUSTOMERS WHO HAVE ORDERS
If you wish to retrieve only those customers who have placed an order then you can use an INNER JOIN.
