In SQL, a LEFT JOIN (or LEFT OUTER JOIN) is used to retrieve all rows from the left table and the matched rows from the right table. If there is no match, the result will still include rows from the left table, but with NULL values for columns from the right table.

Basic Syntax

Here's the basic syntax for a LEFT JOIN:

Example Scenario

Suppose you have the following two tables:

1. Employees

   • EmployeeID
   • FirstName
   • LastName
   • DepartmentID

2. Departments

   • DepartmentID
   • DepartmentName

You want to get a list of all employees and include their department names if they are available. If an employee does not belong to any department, the department name should be shown as NULL.

Here's how you can achieve this using a LEFT JOIN:

Explanation

• Employees.FirstName, Employees.LastName, Departments.DepartmentName: These are the columns you want to retrieve.
• FROM Employees: This specifies the left table from which to retrieve data.
• LEFT JOIN Departments: This specifies that you are performing a left join with the Departments table.
• ON Employees.DepartmentID = Departments.DepartmentID: This specifies the condition for the join, which is that the DepartmentID column in both tables must match.

Key Points

1. All Rows from Left Table: All rows from the left table (Employees in this case) are included in the result set.
2. Matching Rows from Right Table: For each row in the left table, if there is a matching row in the right table (Departments), it is included. If there is no match, NULL is shown for columns from the right table.
3. Use Cases: LEFT JOIN is useful when you want to retain all records from the left table regardless of whether there is a corresponding record in the right table.

Example Result

Given the following data:

Employees

Departments

The result of the LEFT JOIN query would be:

In this result, all employees are listed, and the department names are shown where available. For Charlie, who does not have a department assigned, NULL is shown.