How to Use LEFT JOIN in SQL

The LEFT JOIN clause in SQL, also known as LEFT OUTER JOIN, is used to combine rows from two or more tables based on a related column between them. Unlike the INNER JOIN, a LEFT JOIN returns all the rows from the left table (the first table mentioned in the query) and the matching rows from the right table (the second table). If no match is found, NULL values are returned for columns from the right table.

Syntax of LEFT JOIN

SELECT column1, column2, ... FROM table1 LEFT JOIN table2 ON table1.common_column = table2.common_column;
  • table1: The first table in the join operation (the left table).
  • table2: The second table in the join operation (the right table).
  • common_column: The column that exists in both tables and is used to match the rows.

Example of LEFT JOIN

Consider two tables: employees and departments

Table 1: employees

employee_id first_name last_name department_id
1 John Doe 101
2 Jane Smith 102
3 Jim Brown NULL
4 Jill White 103

Table 2: departments

department_id department_name
101 HR
102 IT

SQL Query

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

Result:

first_name last_name department_name
John Doe HR
Jane Smith IT
Jim Brown NULL
Jill White NULL

Explanation of the Query

The query selects the first_name, last_name and departments_name columns.

It performs a LEFT JOIN between the employees table and the departments table.

The ON keyword specifies the condition for the join, which is that the departments_id column in employees must match the departments_id in departments.

Since Jim Brown and Jill White do not have a department_id that matches any departments_id in the departments table, their rows will still be included in the result set, but with NULL values for the departments_name

Key Points:

  • A LEFT JOIN includes all rows from the left table and matching rows from the right table.
  • If no match is found in the right table, NULL values are returned for the columns from the right table.
  • The LEFT JOIN is useful when you want to include all records from one table and retrieve matching records from another table, even when no matches exist.

Use Cases for LEFT JOIN:

  • Incorporating Missing Data: Use LEFT JOIN when you want to include all records from the left table, even if there is no corresponding match in the right table.
  • Combining Data with Optional Relationships: Use LEFT JOIN when one table has optional relationships with another (e.g., employees may or may not belong to a department).

Conclusion

The LEFT JOIN is a powerful SQL operation for combining data from two tables, ensuring that all records from the left table are returned, regardless of whether matching rows exist in the right table. It is particularly useful for including data with missing or optional relationships.

line

Copyrights © 2024 letsupdateskills All rights reserved