SQL LEFT JOIN vs. LEFT OUTER JOIN

SQL is a powerful tool for interacting with relational databases. When working with tables in SQL, you often need to combine data from multiple tables. This is where JOIN operations help. LEFT JOIN and LEFT OUTER JOIN are two commonly used commands. Although they seem different, they actually perform the same function. Let’s understand the working and difference between SQL LEFT JOIN vs. LEFT OUTER JOIN.

What is a LEFT JOIN?

A LEFT JOIN is a type of SQL JOIN operation that combines rows from two tables based on a related column. The key feature of a LEFT JOIN is that it returns all rows from the left table and the matched rows from the right table. If there’s no match, the result will include NULL values for columns from the right table.

Syntax

SELECT columns
FROM left_table
LEFT JOIN right_table
ON left_table.column_name = right_table.column_name;

In the above syntax:

• left_table: The primary table from which all rows are returned.
• right_table: The secondary table from which matched rows are returned.
• column_name: The column used to join the two tables.

Example of LEFT JOIN

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

This query retrieves all employees and their corresponding department names. If an employee is not assigned to any department, the result will show NULL for the department name.

Also Read: Joins In SQL – Inner, Left, Right and Full Joins Explained

What is a LEFT OUTER JOIN?

The LEFT OUTER JOIN operates exactly like the LEFT JOIN. It returns all rows from the left table and the matched rows from the right table. If there’s no match, it returns NULL for columns from the right table. The term “OUTER” is optional and does not change the behavior of the JOIN. It’s often used for clarity in some SQL dialects.

Syntax

SELECT columns
FROM left_table
LEFT OUTER JOIN right_table
ON left_table.column_name = right_table.column_name;

Using the same example as above, we could rewrite our query as follows:

As you can see, the syntax is identical to the LEFT JOIN. The only difference is the inclusion of the word OUTER.

Example of LEFT OUTER JOIN

SELECT employees.name, departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON employees.department_id = departments.id;

This query also retrieves all employees and their corresponding department names, just like the LEFT JOIN. If an employee is not assigned to any department, the result will show NULL for the department name.

Also Read: SQL Guide from Basics to Advance Level

SQL LEFT JOIN vs. LEFT OUTER JOIN

While the terms LEFT JOIN and LEFT OUTER JOIN may appear different, they are functionally identical in SQL. The only difference lies in the syntax:

• LEFT JOIN: Shorter and more commonly used.
• LEFT OUTER JOIN: Includes the optional “OUTER” keyword for clarity.

Both commands return the same results, so the choice between them is a matter of personal or organizational preference.

Practical Examples: LEFT JOIN and LEFT OUTER JOIN

To create a sample database with employees and departments tables, and then use the LEFT JOIN and RIGHT JOIN examples, you can use the following SQL commands.

Step 1: Create Database

First, create the database where the tables will reside.

CREATE DATABASE company_db;
USE company_db;

Step 2: Create Tables

Now, let’s create the employees and departments tables.

-- Create the 'departments' table
CREATE TABLE departments (
    id INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

-- Create the 'employees' table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Step 3: Insert Data into Tables

Now, insert some sample data into both tables:

-- Insert data into 'departments' table
INSERT INTO departments (id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance'),
(4, 'Marketing');

Output:

-- Insert data into 'employees' table
INSERT INTO employees (id, name, department_id) VALUES
(1, 'Alice', 2),
(2, 'Bob', 3),
(3, 'Charlie', NULL),
(4, 'David', 1);

Output:

Step 4: Run the Queries

Now that the database and tables are created and populated with data, you can run the LEFT JOIN and RIGHT JOIN queries.

LEFT JOIN Query

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments
ON employees.department_id = departments.id;

Output:

RIGHT JOIN Query

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments
ON employees.department_id = departments.id;

Output:

When to Use LEFT JOIN or LEFT OUTER JOIN

• Data Retrieval: Use a LEFT JOIN when you need all records from the left table, even if there are no matching records in the right table.
• Reporting: Ideal for generating reports where you want to include all items (e.g., products, employees) regardless of their relationships.
• Data Analysis: Helps identify gaps or missing relationships between datasets.

Conclusion

In summary, both LEFT JOIN and LEFT OUTER JOIN are synonymous terms in SQL that serve the same purpose of combining data from two tables while ensuring that all records from the left table are included in the result set. The choice between using one term over the other often comes down to personal or organizational preference. Understanding this can enhance your efficiency when writing SQL queries and prevent confusion during data manipulation tasks.

Put your SQL knowledge to test with these SQL Projects!

Frequently Asked Questions

Janvi Kumari

Hi, I am Janvi, a passionate data science enthusiast currently working at Analytics Vidhya. My journey into the world of data began with a deep curiosity about how we can extract meaningful insights from complex datasets.