SQL LEFT JOIN

SQL LEFT JOIN Statement

The SQL LEFT JOIN statement is used to return all records from the left table and the matched records from the right table. If there is no match, the result is NULL from the right table.

---

Syntax

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

The LEFT JOIN statement has the following components:

• column1, column2, ...: The columns to be retrieved.
• table1: The name of the first table (left table).
• table2: The name of the second table (right table).
• table1.column = table2.column: The condition to join the tables.

---

Example SQL LEFT JOIN Statement Queries

Let's look at some examples of SQL LEFT JOIN statement queries:

1. Basic LEFT JOIN Example

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
LEFT JOIN employees_2
ON employees_1.id = employees_2.id;

This query retrieves the id and first_name columns from the employees_1 table and the department_name column from the employees_2 table. It returns all records from the employees_1 table and the matched records from the employees_2 table. If there is no match, the result is NULL from the employees_2 table.

2. LEFT JOIN with WHERE Clause

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
LEFT JOIN employees_2
ON employees_1.id = employees_2.id
WHERE employees_2.department_name IS NOT NULL;

This query retrieves the id and first_name columns from the employees_1 table and the department_name column from the employees_2 table. It returns all records from the employees_1 table and the matched records from the employees_2 table where the department_name is not null.

---

Full Example

Let's go through a complete example that includes creating tables, inserting data, and querying the tables with the LEFT JOIN statement.

Step 1: Creating Tables

This step involves creating new tables named employees_1 and employees_2 to store employee data.

CREATE TABLE employees_1 (
    id INT PRIMARY KEY,
    first_name VARCHAR(50)
);

CREATE TABLE employees_2 (
    id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

In this example, we create two tables: employees_1 with columns for id and first_name, and employees_2 with columns for id and department_name.

Step 2: Inserting Data into the Tables

This step involves inserting some sample data into the employees_1 and employees_2 tables.

INSERT INTO employees_1 (id, first_name)
VALUES (1, 'John'), (2, 'Jane'), (3, 'Jim');

INSERT INTO employees_2 (id, department_name)
VALUES (1, 'Engineering'), (2, 'HR'), (4, 'Marketing');

Here, we insert data into the employees_1 and employees_2 tables.

Step 3: Querying the Tables with LEFT JOIN

This step involves selecting the data from the employees_1 and employees_2 tables and joining them using the LEFT JOIN statement.

SELECT employees_1.id, employees_1.first_name, employees_2.department_name
FROM employees_1
LEFT JOIN employees_2
ON employees_1.id = employees_2.id;

This query retrieves all the rows from the employees_1 table and the matched rows from the employees_2 table. The result will be:

id  first_name  department_name
--- ----------- ---------------
1   John        Engineering
2   Jane        HR
3   Jim         NULL

Conclusion

The SQL LEFT JOIN statement is a powerful tool for returning all records from the left table and the matched records from the right table. Understanding how to use the LEFT JOIN statement and its syntax is essential for effective data retrieval and analysis in SQL databases.