SQL LEFT JOIN

What is SQL LEFT JOIN?

SQL LEFT JOIN, also known as a LEFT OUTER JOIN, is a type of SQL JOIN operation that retrieves all records from the left table (table1) and the matching records from the right table (table2). If there are no matching records in the right table, NULL values are included for those columns.

When you would use it

You would use a SQL LEFT JOIN when you want to retrieve all the records from one table (the left table) and only the matching records from a related table (the right table). Common use cases for LEFT JOIN include:

1. Displaying all items: When you want to display all items from the left table, even if there are no corresponding items in the right table.

2. Handling missing data: When you need to handle situations where data might be missing or incomplete in one of the tables.

3. Optional relationships: In scenarios where relationships between tables are optional, and you still want to display data from the primary table.

4. Combining data: When you want to combine data from multiple tables while preserving all records from one of the tables.

Syntax

The syntax for a SQL LEFT JOIN operation is as follows:

SELECT column_list
FROM table1
LEFT JOIN table2 ON table1.column = table2.column;

• column_list: A list of columns to retrieve from the joined tables.
• table1 and table2: The names of the tables to be joined.
• column: The common column or key that relates the tables.

Parameter values

• column_list: A list of column names you want to select from the joined tables.
• table1 and table2: The names of the tables to be joined.
• column: The related column or key that connects the tables.

Example query

Suppose you have two tables, "employees" and "departments," and you want to retrieve a list of all employees along with their corresponding department names. You can use a SQL LEFT JOIN like this:

SELECT employees.employee_id, employees.employee_name, departments.department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;

Example table response

Assuming the "employees" and "departments" tables contain the following data:

employees:

| employee_id | employee_name | department_id |
| ----------- | ------------- | ------------- |
| 1           | John Smith    | 101           |
| 2           | Mary Johnson  | 102           |
| 3           | Sam Brown     | 103           |

departments:

| department_id | department_name |
| ------------- | --------------- |
| 101           | HR              |
| 102           | Finance         |

The query mentioned earlier would return the following result:

| employee_id | employee_name | department_name |
| ----------- | ------------- | --------------- |
| 1           | John Smith    | HR              |
| 2           | Mary Johnson  | Finance         |
| 3           | Sam Brown     | NULL            |

This result combines data from both tables, displaying all employees along with their department names. In this case, the employee with employee_id 3 has no corresponding department, so the department_name is NULL.

Use cases

• Retrieving all records from the left table and only matching records from the right table.
• Handling optional or missing data scenarios.
• Combining data while preserving all records from one table.

SQL languages this is available for

SQL LEFT JOIN is a standard SQL feature and is available in most relational database management systems (RDBMS), including but not limited to:

• MySQL
• PostgreSQL
• Oracle Database
• SQL Server
• SQLite
• IBM Db2
• MariaDB

The syntax and behavior of LEFT JOIN are consistent across these database systems.