LEFT JOIN in SQL

In Relational databases, it is often essential to get all records from one table, even if there is no matching data in the other table. This is mainly used in situations where we need to analyse the report, audit, or find incomplete data. LEFT JOIN will allow you to capture important information from your primary table while extracting related information. Working with LEFT JOIN is a practical way to find gaps or create summaries. In this blog, let’s explore the LEFT JOIN in SQL in detail with examples.

Table of Contents:

• What is a LEFT JOIN in SQL?
• Syntax and Example of LEFT JOIN in SQL
• When do we need to use LEFT JOIN?
• Using LEFT JOIN with GROUP BY Clause in MySQL
• Using LEFT JOIN with WHERE Clause in MySQL
• Using LEFT JOIN with the ON Clause in MySQL
• Difference between LEFT JOIN with ON Clause and LEFT JOIN with WHERE Clause
• Using Multiple LEFT JOINS in SQL
• JOINS vs SUBQUERIES in SQL
• Performance Comparison of JOINS in SQL
• Real-world Examples
• Common Mistakes and Best Practices
• Conclusion

What is a LEFT JOIN in SQL?

A SQL LEFT JOIN is a category of join in which you are joining columns from two or more tables based on a related column. A left join will always return all rows from the left (first) table, regardless of whether there are any matches in the right (second) table. If there are no matches in the second table, the result will show NULL in the second table’s column.

Before getting started with the syntax and examples of LEFT JOIN, let us create some tables for better understanding.

Let’s create a Learner’s table.

CREATE TABLE Learners (
    lea_id INT PRIMARY KEY,
    f_name VARCHAR(100),
    mail VARCHAR(100)
);

INSERT INTO Learners (lea_id, f_name, mail) VALUES
(1, 'Amit', '[email protected]'),
(2, 'Neha', '[email protected]'),
(3, 'Rajeev', '[email protected]'),
(4, 'Sonal', '[email protected]');

SELECT * FROM Learners;

This is how the Learners table looks once it is created and inserted with values.

Master LEFT JOIN in SQL – Unlock Advanced SQL Skills Today!

Enroll now and transform your future!

Explore Program

Now, let’s create a Modules Table and insert some values into it:

CREATE TABLE Modules (
    m_id INT PRIMARY KEY,
    m_ttl VARCHAR(100),
    trainer VARCHAR(100)
);

INSERT INTO Modules (m_id, m_ttl, trainer) VALUES
(201, 'Intro to Databases', 'Kiran Rao'),
(202, 'SQL Deep Dive', 'Rajeev Nair'),
(203, 'Machine Learning Basics', 'Meena Kapoor'),
(204, 'Cloud Fundamentals', 'Anil Mehta');

SELECT * FROM Modules;

This is how the Modules table looks once it is created and inserted with the values.

Finally, let’s create a Registration table:

CREATE TABLE Reg (
    reg_id INT PRIMARY KEY,
    lea_id INT,
    mod_id INT,
    mode VARCHAR(50),
    FOREIGN KEY (lea_id) REFERENCES Learners(lea_id),
    FOREIGN KEY (mod_id) REFERENCES Modules(m_id)
);

INSERT INTO Reg (reg_id, lea_id, mod_id, mode) VALUES
(1, 1, 201, 'Online'),
(2, 1, 202, 'Offline'),
(3, 2, 203, 'Online'),
(4, 3, 201, 'Offline'),
(5, 3, 204, 'Online'),
(6, 4, 202, 'Online');

SELECT * FROM Reg;

This is how the Registration table looks once it is created and inserted with values.

Syntax and Example of LEFT JOIN in SQL

Syntax:

SELECT cols
FROM tab1
LEFT JOIN tab2
ON tab1.col_name = tab2.col_name;

Example:

SELECT 
    L.lea_id,
    L.f_name,
    R.mod_id,
    R.mode
FROM 
    Learners L
LEFT JOIN Reg R ON L.lea_id = R.lea_id;

Output:

Explanation: Here, this LEFT JOIN extracts all learners who are in the Learners table and their registration details from the Reg table. If a learner has not registered for any module, mod_id and mode will be NULL.

When do we need to use LEFT JOIN?

• When you want all records from the left table, regardless of whether any records in the right table match or not.
• To discover unmatched or missing records in the right table, such as customers who do not have any orders.
• To run an analysis that contains related data as optional details, such as listing users and their latest login data if it exists.
• When creating reports that require all foundational entities, along with any available related data.
• For lookups in a safe manner, eliminate the risk of losing data from the primary table because of missing foreign key values.

Using LEFT JOIN with GROUP BY Clause in MySQL

LEFT JOIN with GROUP BY can be used to aggregate data from the left table while keeping all matched and unmatched records from the right table.

Syntax:

SELECT col, agg_fun(col)
FROM tab1
LEFT JOIN tab2 ON tab1.com_col = tab2.com_col
GROUP BY col;

Example:

SELECT l.f_name, COUNT(r.mod_id) AS total_modules
FROM Learners l
LEFT JOIN Reg r ON l.lea_id = r.lea_id
GROUP BY l.f_name;

Output:

Explanation: Here, this query will return each learner, along with the total number of modules they have registered for. The LEFT JOIN will ensure that all learners are returned, even those with no registrations.

Using LEFT JOIN with WHERE Clause in MySQL

This WHERE Clause filters the rows after performing the join; The unmatched rows or NULL values from the right table can be removed.

Syntax:

SELECT colA
FROM A
LEFT JOIN B ON A.id = B.a_id
WHERE condt;

Example:

SELECT l.f_name, r.mode
FROM Learners l
LEFT JOIN Reg r ON l.lea_id = r.lea_id
WHERE r.mode = 'Online';

Output:

Explanation: Here, this query returns the name of the learner whose registration mode is ‘Online’ and removes the unmatched rows.

Using LEFT JOIN with the ON Clause in MySQL

LEFT JOIN with an ON clause, filters are applied during the JOIN. So all the rows from the left table are preserved.

Syntax:

SELECT colA
FROM A
LEFT JOIN B ON A.id = B.a_id

Example:

SELECT l.f_name, r.mode
FROM Learners l
LEFT JOIN Reg r 
  ON l.lea_id = r.lea_id AND r.mode = 'Offline';

Output:

Explanation: Here, the filter r.mode = ‘Offline’ is applied inside the ON clause. If the learner has mode = ‘Online’, it will be returned as NULL.

Difference between LEFT JOIN with ON Clause and LEFT JOIN with WHERE Clause

Using Multiple LEFT JOINS in SQL

In a single query, this allows us to combine data from multiple tables. It ensures that all records from the leftmost table are kept, even if there is no match in the other tables.

Syntax:

SELECT col
FROM main_tab
LEFT JOIN tab1 ON condt1
LEFT JOIN tab2 ON condt2

Example:

SELECT l.f_name, m.m_ttl, m.trainer
FROM Learners l
LEFT JOIN Reg r ON l.lea_id = r.lea_id
LEFT JOIN Modules m ON r.mod_id = m.m_id;

Output:

Explanation: Here, this query gives an output of the names of each learner, module title, and the trainer, based on the enrollments. It uses two LEFT JOINs to include all learners, even those who are not enrolled in any module.

JOINS vs SUBQUERIES in SQL

Performance Comparison of JOINS in SQL

Real-world Examples

1. HR System

 Consider an HR, who wants to list all the employees with their department names.

Example:

CREATE TABLE Employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT
);

CREATE TABLE Departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

INSERT INTO Employees VALUES (1, 'Rohit', 101), (2, 'Vinoth', 102), (3, 'Babu', 101),(4, 'David', 104);
INSERT INTO Departments VALUES (101, 'HR'), (102, 'IT'), (103, 'Finance');

SELECT e.employee_name, d.department_name  
FROM Employees e  
LEFT JOIN Departments d ON e.department_id = d.department_id;

Output:

Explanation: Here, this LEFT JOIN returns all employees, including employees who do not have an assigned department. David appears in the results with NULL for department_name because there is no department assigned to him.

2. Retail Store

An e-commerce site wants to display the customers along with the order details.

Example:

CREATE TABLE Customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(50)
);

CREATE TABLE Orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    order_date DATE,
    amount DECIMAL(10,2),
    FOREIGN KEY (customer_id) REFERENCES Customers(customer_id)
);

INSERT INTO Customers VALUES (1, 'John Doe'), (2, 'Jane Smith'), (3, 'Sam Wilson');
INSERT INTO Orders VALUES (101, 1, '2024-03-01', 250.50),  (102, 2, '2024-03-05', 100.00),  (103, 1, '2024-03-10', 300.00);

SELECT c.customer_name, o.order_id, o.order_date, o.amount  
FROM Customers c  
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

Output:

Explanation: Here, this LEFT JOIN gets all customers, even those who have not ordered anything. Sam Wilson is included with NULL values for the order details, as there is no corresponding row in the Orders table.

Common Mistakes and Best Practices

Common Mistakes:

• Avoiding the alias name makes the query more complex and increases the risk of column references.
• Assuming LEFT JOIN filters unmatched rows, and we need to use a WHERE condition to exclude the NULL values.
• Joining the table on unrelated columns leads to incorrect results.

Best Practices:

• To handle the NULL values, use COALESCE()

SELECT c.name, COALESCE(o.amnt, 0) AS total_amnt

• Always use an alias name for better clarity and readability.

SELECT c.name, o.o_id 
FROM Cust c 
LEFT JOIN Order o ON c.cust_id = o.cust_id;

• Test with data that covers all the edge cases, including with and without matches. 

Start Your SQL Adventure!

Start Your SQL Journey for Free Today

Explore Program

Conclusion

LEFT JOIN is one of the useful JOINS in SQL where the task requires you to return all records from one table, and any matching records from another table. In the real world, for example, like HR systems, e-commerce providers, or learning management systems, it is important to show unmatched records, and thus the LEFT JOIN is useful. Mastering LEFT JOIN and how to avoid mistakes, like not applying WHERE or JOIN filtering, can help you achieve accurate results. In this blog, you have gained knowledge on LEFT JOIN in detail.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions, prepared by industry experts.

SQL LEFT JOIN – FAQs