SQL LEFT JOIN – Tutorial With Examples

SQL LEFT Join is a type of join in SQL (Structured Query Language) that combines rows from two or more tables based on a related column between them. The LEFT Join returns all the rows from the left table (the first table in the query), and matching rows from the right table (the second table in the query). If there is no match in the right table, the result will contain NULL values for the columns from the right table.

A LEFT Join is useful when you want to return all the rows from one table, and the matching rows from another table. It is also used to return all the rows from one table, even if there is no matching record in the second table.

SQL LEFT Join Syntax

The basic syntax of the SQL LEFT Join is as follows:

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

In this syntax:

  • SELECT clause specifies the columns to be returned in the result set.
  • FROM clause specifies the left table (table1) in the join.
  • LEFT JOIN clause specifies the right table (table2) in the join and the join condition using the ON clause.
  • ON clause specifies the join condition that relates the columns from both tables.

SQL LEFT Join Example

Consider two tables: “employees” and “departments”. The “employees” table has the following data:

EmployeeID EmployeeName DepartmentID
E001 John D001
E002 Jane D002
E003 Bob D003
E004 Alice D004
E005 Eve D005

And the “departments” table has the following data:

DepartmentID DepartmentName
D001 IT
D002 Marketing
D003 Sales

If we want to get the names of all employees and their respective departments, we can use the following SQL LEFT Join statement:

SELECT employees.EmployeeName, departments.DepartmentName
FROM employees
LEFT JOIN departments
ON employees.DepartmentID = departments.DepartmentID;

The result of the above query will be:

EmployeeName DepartmentName
John IT
Jane Marketing
Bob Sales
Alice NULL
Eve NULL

In the result, all employees are displayed, and the departments that don’t match with any employee are displayed as NULL values in the “DepartmentName” column.

Conclusion

SQL LEFT Join is a type of join in SQL that returns all the rows from the left table and matching rows from the right table. If there is no match in the right table, the result will contain NULL values for the columns from the right table. This type of join is useful when you want to return all the rows from one table, and the matching rows from another table, or when you want to return all the rows from one table, even if there is no matching record in the second table.

Leave a Reply

Your email address will not be published. Required fields are marked *