MySQL LEFT JOIN

In databases, data is stored in multiple tables and it is often necessary sometimes to combine two or more tables to fetch the required data. In MySQL, Joins enable the merging of multiple tables based on the common columns. 

In this article, we are going to explore MySQL LEFT JOIN Keyword which is a type of Outer Join in MySQL. We will cover everything about the LEFT JOIN in detail and also perform some queries in examples for better understanding.

Prerequisites: Before understanding the Left JOIN make sure that your concepts about the INNER JOIN are clear.

LEFT JOIN Keyword

MySQL LEFT JOIN is a type of outer join that returns a table that contains all the records from the left table and the matched records from the right table.

In simple words, the data of the 1st table is returned along with the data that is common between the first and the second table. If there is no matched data found then only the data of the left table is returned. This Join can also be called a Left Outer Join clause.

Check the visual representation of LEFT JOIN and understand its workings.

For example, Consider an example where we have two tables customer and order.

A LEFT JOIN will allow you to combine these tables ensuring that all the customer’s data appears in the final result of the query even if they haven’t placed any orders yet. The left table is the main priority of the LEFT JOIN along with the matched data between the two tables.

Syntax

Select column1, column2, ...
FROM table1
LEFT JOIN table2 
ON table1.column_name = table2.column_name;

LEFT JOIN Examples

To understand the LEFT JOIN in more depth manner, we need a table on which we will perform various operations. So here we take 3 tables called books, authors, and book_authors.

Query to create authors Table :

CREATE TABLE authors (
    author_id INT PRIMARY KEY,
    author_name VARCHAR(255)
);

Query to create books table:

CREATE TABLE books (
    book_id INT PRIMARY KEY,
    title VARCHAR(255),
    publication_year INT
);

Query to create book_authors Table:

CREATE TABLE book_authors (
    book_id INT,
    author_id INT,
    FOREIGN KEY (book_id) REFERENCES books(book_id),
    FOREIGN KEY (author_id) REFERENCES authors(author_id)
);

Here, book_id and author_id which is the PRIMARY KEY of books and authors table acts as a FOREIGN KEY for book_authors table.

After inserting some data into tables, tables look like:

books Table:

books Table

authors Table:

authors Table

book authors table

book authors table

Relation Between Tables:

The below image shows the relations between all three tables.

Relation Between the tables

Example 1: MySQL LEFT JOIN with USING Clause

We can use the USING clause, to specify the column/field on which the JOIN operation should be performed.

Query:

SELECT *
FROM books
LEFT JOIN book_authors USING (book_id);

Output:

Output

Explanation: The following query gets all the records from the left table books and the matching records from the right table book_authors. The query uses the USING Clause which performs the left join based on a column that is common to both the tables and has matching records.

Example 2: MySQL LEFT JOIN with GROUP BY Clause

We can use the GROUP BY clause with the LEFT JOIN keyword to view organized results based on specific columns.

Query:

SELECT authors.author_name, COUNT(books.book_id) as book_count
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
LEFT JOIN books ON book_authors.book_id = books.book_id
GROUP BY authors.author_name;

Output:

Output

Explanation: GROUP BY is used in MYSQL to group the rows that have similar values. This query returns the frequency of books written by each author. It uses LEFT JOIN to join the author table and the book_author table and then Aggregate the records using the GROUP BY Clause to calculate the count of books written by each author. The Aggregate COUNT function is used to calculate the frequency of authors once it is grouped.

Example 3: LEFT JOIN with WHERE Clause

We can use the WHERE clause with LEFT JOIN to view the records that fulfill a specific request. this allows the filtering of data from tables.

SELECT authors.author_name, books.title
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
LEFT JOIN books ON book_authors.book_id = books.book_id
WHERE books.publication_year < 2015;

Output:

Output

Explanation: WHERE Clause is used in MySQL to filter the result set based on a specific condition. This query performs a LEFT JOIN between the table authors and books to return the name of the author and the book he/she has written but only those authors who have published a book before 2015.

Example 4: MySQL LEFT JOIN to Get Unmatched Records

Query:

SELECT authors.author_name
FROM authors
LEFT JOIN book_authors ON authors.author_id = book_authors.author_id
WHERE book_authors.author_id IS NULL;

Output:

Output

Explanation: The above query is used to get the unmatched records within the tables For example consider an example where you have two tables customers and orders and you want to find out customers who have never placed an order. In such situations, we can use this query to get the unmatched results. It uses a LEFT JOIN and a WHERE Clause to filter out authors with unmatched entries in book_authors. We can see that in our case we don’t have any resultant rows after executing this query which means that in our database every author has published at least one book which is why the result set is NULL.

Applications of LEFT JOIN in MySQL

• Combines data from multiple tables based on the matched conditions. 
• Retrieves all records from the left table: The primary purpose of a LEFT JOIN is to retrieve all records from the left (or first) table, regardless of whether there are matching records in the right (or second) table. This ensures that no data is left out in the result set. For example, if we have two table employees and projects and we perform a LEFT JOIN between them then we can find out which are the employees who are not assigned to a project till now.
• Aggregate Data and Fetch Results: Using the GROUP BY Clause along with the LEFT JOIN we can combine data from multiple tables and then fetch the required results. We can calculate the count, sum, and average of the grouped data formed through the LEFT JOIN.

Difference Between WHERE & ON Clause in MySQL LEFT JOIN

Although we can achieve the same results using the WHERE and ON Clause in MySQL, but we should use them differently as they are designed for different purposes.

• The main purpose of the ON Clause is to specify the join conditions. The ON Clause defines how two or more tables should be joined. The ON Clause defines the common attributes between the two tables which must be matched to perform a join.
• On the other hand, the main purpose of the WHERE Clause is to filter the resultant table. It defines which rows must be included in the resultant set of the query. It is used to fetch only those records which fulfill the specified condition by the user.

Conclusion

In this article, we have learned about MySQL LEFT JOIN, its syntax, and various Clauses to apply with LEFT JOIN. You can use LEFT JOIN whenever the result of the first table is a topmost priority along with the matching records of both the tables. However always use Clauses carefully to filter out the results when applying the LEFT JOIN between the tables. Always use the ON Clause to specify join conditions and the WHERE Clause to filter out the result.