MySQL LEFT JOIN

The Left Join in MySQL is used to query records from multiple tables. This clause is similar to the Inner Join clause that can be used with a SELECT statement immediately after the FROM keyword. When we use the Left Join clause, it will return all the records from the first (left-side) table, even no matching records found from the second (right side) table. If it will not find any matches record from the right side table, then returns null.

In other words, the Left Join clause returns all the rows from the left table and matched records from the right table or returns Null if no matching record found. This Join can also be called a Left Outer Join clause. So, Outer is the optional keyword to use with Left Join.

We can understand it with the following visual representation where Left Joins returns all records from the left-hand table and only the matching records from the right side table:

MySQL LEFT JOIN Syntax

The following syntax explains the Left Join clause to join the two or more tables:

SELECT columns  
FROM table1  
LEFT [OUTER] JOIN table2  
ON Join_Condition;

In the above syntax, table1 is the left-hand table, and table2 is the right-hand table. This clause returns all records from table1 and matched records from table2 based on the specified join condition.

MySQL LEFT JOIN Example

Let us take some examples to understand the working of Left Join or Left Outer Join clause:

LEFT JOIN clause for joining two tables

Here, we are going to create two tables "customers" and "orders" that contains the following data:

Table: customers

Table: orders

To select records from both tables, execute the following query:

SELECT customers.customer_id, cust_name, price, date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id;

After successful execution of the query, it will give the following output:

MySQL LEFT JOIN with USING Clause

The table customers and orders have the same column name, which is customer_id. In that case, MySQL Left Join can also be used with the USING clause to access the records. The following statement returns customer id, customer name, occupation, price, and date using the Left Join clause with the USING keyword.

SELECT customer_id, cust_name, occupation, price, date
FROM customers
LEFT JOIN orders USING(customer_id);

The above statement will give the following output:

MySQL LEFT JOIN with Group By Clause

The Left Join can also be used with the GROUP BY clause. The following statement returns customer id, customer name, qualification, price, and date using the Left Join clause with the GROUP BY clause.

SELECT customers.customer_id, cust_name, qualification, price, date
FROM customers
LEFT JOIN orders ON customers.customer_id = orders.customer_id
GROUP BY price;

The above statement will give the following output:

LEFT JOIN with WHERE Clause

The WHERE clause is used to return the filter result from the table. The following example illustrates this with the Left Join clause:

SELECT customer_id, cust_name, occupation, price, date
FROM customers
LEFT JOIN orders 
USING(customer_id) WHERE price>2500;

This statement gives the below result:

MySQL LEFT JOIN Multiple Tables

We have already created two tables named "customers" and "orders". Let us create one more table and name it as "contacts" that contains the following data:

Execute the following statement to join the three table customers, orders, and contacts:

SELECT customers.customer_id, cust_name, order_id, price, cellphone
FROM customers
LEFT JOIN contacts ON customer_id = contact_id
LEFT JOIN orders ON customers.customer_id = orders.customer_id ORDER BY income;

After successful execution of the above query, it will give the following output:

Use of LEFT JOIN clause to get unmatched records

The LEFT JOIN clause is also useful in such a case when we want to get records in the table that does not contain any matching rows of data from another table.

We can understand it with the following example that uses the LEFT JOIN clause to find a customer who has no cellphone number:

SELECT customer_id, cust_name, cellphone, homephone
FROM customers
LEFT JOIN contacts ON customer_id = contact_id
WHERE cellphone IS NULL ;

The above statement returns the following output:

Difference between WHERE and ON clause in MySQL LEFT JOIN

In the LEFT Join, the condition WHERE and ON gives a different result. We can see the following queries to understand their differences:

WHERE Clause

SELECT cust_name, occupation, order_id, price, date
FROM customers
LEFT JOIN orders 
USING(customer_id) WHERE price=2500;

It will give the following output that returns:

ON Clause

SELECT cust_name, occupation, order_id, price, date
FROM customers LEFT JOIN orders ON price=2500;

It will give the following output: