Left Join (Left Outer Join) in SQL

Left Join (Left Outer Join) in SQL

Returning All Rows from the Left Table, with Matching Rows from the Right Table

A Left Join (also known as a Left Outer Join) is a type of SQL join that returns all rows from the left table and the matching rows from the right table. If there is no match between the tables, the result will still include all rows from the left table, but the columns from the right table will contain NULL values.

This type of join is particularly useful when you need to retain all the data from the left table and include additional information from the right table only where a match exists.

---

1. Understanding Left Join

In a Left Join, the focus is on the left table. The query will return all rows from the left table, regardless of whether there is a matching row in the right table. If a match is found, the corresponding data from the right table is included. If no match is found, the right table columns will show NULL values.

How It Works:

• All Rows from the Left Table: Every row from the left table is included in the result set.

• Matching Rows from the Right Table: Only rows from the right table that satisfy the join condition are included.

• NULL Values for Non-Matching Rows: If there is no match, the columns from the right table will contain NULL values in the result.

2. Syntax of Left Join

The syntax for a Left Join is similar to that of an inner join, with the only difference being the keyword LEFT JOIN.

Basic Syntax:

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

• table1 (Left Table): The table from which all rows are returned.

• table2 (Right Table): The table from which only the matching rows are returned, along with NULL values for non-matches.

• ON table1.column = table2.column: The condition that defines the relationship between the two tables.

3. Example of a Left Join

Let's continue with the example of the employees and departments tables. We want to retrieve the first name, last name, and department name of each employee, including those who are not assigned to any department.

Example Query:

SELECT employees.first_name, employees.last_name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id;

In this example:

• The LEFT JOIN connects the employees table (left table) with the departments table (right table) based on the department_id column.

• The query returns all rows from the employees table, including those who are not associated with any department.

Result:

first_name	last_name	department_name
Amit	Sharma	Sales
Neha	Singh	Marketing
Ravi	Verma	IT
Suresh	Mehta	NULL

In this output:

• The result set includes all employees. Suresh Mehta is included even though he is not assigned to any department, and the department_name column shows NULL for him.

4. Real-World Scenario of Left Join

Consider a database with two tables:

• customers: Contains customer details.

• orders: Contains order details.

You want to generate a report that shows all customers, along with the orders they have placed. Even if a customer has not placed any orders, they should still appear in the report.

Example Query:

SELECT customers.customer_id, customers.customer_name, orders.order_id, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

In this example:

• The LEFT JOIN connects the customers table (left table) with the orders table (right table) based on the customer_id column.

• The query returns all rows from the customers table, including those who have not placed any orders.

Result:

customer_id	customer_name	order_id	order_date
1	Rajesh Kumar	101	2024-08-15
2	Anjali Patel	102	2024-08-16
3	Suresh Mehta	NULL	NULL

In this output:

• The result set includes all customers. Suresh Mehta is included even though he has not placed any orders, and the order_id and order_date columns show NULL for him.

5. Handling NULL Values in Left Join

When performing a Left Join, NULL values in the result set indicate that the corresponding row from the right table does not have a matching entry. This can be used to identify rows in the left table that do not have related data in the right table.

Example:

SELECT first_name, last_name, department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.department_id
WHERE departments.department_id IS NULL;

In this example:

• The query retrieves all employees who do not have a matching department in the departments table.

• The WHERE clause filters the result set to include only rows where the department_id in the departments table is NULL.

Result:

first_name	last_name	department_name
Suresh	Mehta	NULL

In this output:

• The result set includes only those employees who are not assigned to any department.

6. Left Join with Multiple Tables

You can perform a Left Join with more than two tables, just like with other types of joins. This is useful when you need to retrieve data from multiple related tables.

Example:

Consider a scenario where you want to retrieve a list of customers, the orders they placed, and the products they ordered. However, even customers who have not placed any orders should be included.

SELECT customers.customer_name, orders.order_id, products.product_name
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id
LEFT JOIN products
ON orders.product_id = products.product_id;

In this example:

• The first LEFT JOIN connects the customers table with the orders table based on the customer_id.

• The second LEFT JOIN connects the orders table with the products table based on the product_id.

Result:

customer_name	order_id	product_name
Rajesh Kumar	101	Laptop
Anjali Patel	102	Smartphone
Suresh Mehta	NULL	NULL

In this output:

• All customers are included, even if they haven't placed any orders. If a customer hasn't placed an order, the order_id and product_name columns will show NULL.

7. Advantages of Left Join

• Inclusion of All Data from the Left Table: Left joins ensure that no data from the left table is lost, even if there is no corresponding data in the right table.

• Versatility: Left joins are useful in scenarios where you need to retain all records from one table while still bringing in related data from another table.

8. Limitations of Left Join

• Handling NULL Values: The presence of NULL values in the result set requires careful handling, especially when performing further calculations or filtering.

• Potential for Large Result Sets: Since left joins include all rows from the left table, the result set can become large, especially if the left table contains many rows without matches in the right table.

---

Conclusion

A Left Join (Left Outer Join) is a powerful tool in SQL that allows you to retrieve all rows from the left table and the matching rows from the right table. If there is no match, the result will still include all rows from the left table, with NULL values in the columns from the right table. This type of join is particularly useful when you need to retain all the data from one table while still incorporating related data from another table. By understanding how to use left joins effectively, you can create more comprehensive queries that provide a complete view of your data.

This tutorial is brought to you by codes with pankaj.