SQL LEFT JOIN vs. LEFT OUTER JOIN

SQL is a robust device for interacting with relational databases. When working with tables in SQL, you typically want to mix knowledge from a number of tables. That is the place JOIN operations assist. LEFT JOIN and LEFT OUTER JOIN are two generally used instructions. Though they appear completely different, they really carry out the identical operate. Let’s perceive the working and distinction between SQL LEFT JOIN vs. LEFT OUTER JOIN.

What’s a LEFT JOIN?

A LEFT JOIN is a kind of SQL JOIN operation that mixes rows from two tables primarily based on a associated column. The important thing function of a LEFT JOIN is that it returns all rows from the left desk and the matched rows from the appropriate desk. If there’s no match, the outcome will embody NULL values for columns from the appropriate desk.

Syntax

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

Within the above syntax:

• left_table: The first desk from which all rows are returned.
• right_table: The secondary desk from which matched rows are returned.
• column_name: The column used to affix the 2 tables.

Instance of LEFT JOIN

SELECT workers.identify, departments.department_name
FROM workers
LEFT JOIN departments
ON workers.department_id = departments.id;

This question retrieves all workers and their corresponding division names. If an worker is just not assigned to any division, the outcome will present NULL for the division identify.

Additionally Learn: Joins In SQL – Internal, Left, Proper and Full Joins Defined

What’s a LEFT OUTER JOIN?

The LEFT OUTER JOIN operates precisely just like the LEFT JOIN. It returns all rows from the left desk and the matched rows from the appropriate desk. If there’s no match, it returns NULL for columns from the appropriate desk. The time period “OUTER” is optionally available and doesn’t change the conduct of the JOIN. It’s typically used for readability in some SQL dialects.

Syntax

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

Utilizing the identical instance as above, we may rewrite our question as follows:

As you possibly can see, the syntax is equivalent to the LEFT JOIN. The one distinction is the inclusion of the phrase OUTER.

Instance of LEFT OUTER JOIN

SELECT workers.identify, departments.department_name
FROM workers
LEFT OUTER JOIN departments
ON workers.department_id = departments.id;

This question additionally retrieves all workers and their corresponding division names, identical to the LEFT JOIN. If an worker is just not assigned to any division, the outcome will present NULL for the division identify.

Additionally Learn: SQL Information from Fundamentals to Advance Stage

SQL LEFT JOIN vs. LEFT OUTER JOIN

Whereas the phrases LEFT JOIN and LEFT OUTER JOIN could seem completely different, they’re functionally equivalent in SQL. The one distinction lies within the syntax:

• LEFT JOIN: Shorter and extra generally used.
• LEFT OUTER JOIN: Consists of the optionally available “OUTER” key phrase for readability.

Each instructions return the identical outcomes, so the selection between them is a matter of private or organizational desire.

Sensible Examples: LEFT JOIN and LEFT OUTER JOIN

To create a pattern database with workers and departments tables, after which use the LEFT JOIN and RIGHT JOIN examples, you need to use the next SQL instructions.

Step 1: Create Database

First, create the database the place the tables will reside.

CREATE DATABASE company_db;
USE company_db;

Step 2: Create Tables

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

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

-- Create the 'workers' desk
CREATE TABLE workers (
    id INT PRIMARY KEY,
    identify VARCHAR(100) NOT NULL,
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(id)
);

Step 3: Insert Knowledge into Tables

Now, insert some pattern knowledge into each tables:

-- Insert knowledge into 'departments' desk
INSERT INTO departments (id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance'),
(4, 'Advertising');

Output:

-- Insert knowledge into 'workers' desk
INSERT INTO workers (id, identify, 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 knowledge, you possibly can run the LEFT JOIN and RIGHT JOIN queries.

LEFT JOIN Question

SELECT workers.identify, departments.department_name
FROM workers
LEFT JOIN departments
ON workers.department_id = departments.id;

Output:

RIGHT JOIN Question

SELECT workers.identify, departments.department_name
FROM workers
RIGHT JOIN departments
ON workers.department_id = departments.id;

Output:

When to Use LEFT JOIN or LEFT OUTER JOIN

• Knowledge Retrieval: Use a LEFT JOIN while you want all data from the left desk, even when there aren’t any matching data in the appropriate desk.
• Reporting: Best for producing experiences the place you wish to embody all gadgets (e.g., merchandise, workers) no matter their relationships.
• Knowledge Evaluation: Helps determine gaps or lacking relationships between datasets.

Conclusion

In abstract, each LEFT JOIN and LEFT OUTER JOIN are synonymous phrases in SQL that serve the identical function of mixing knowledge from two tables whereas guaranteeing that each one data from the left desk are included within the outcome set. The selection between utilizing one time period over the opposite typically comes down to non-public or organizational desire. Understanding this could improve your effectivity when writing SQL queries and stop confusion throughout knowledge manipulation duties.

Put your SQL information to check with these SQL Initiatives!

Often Requested Questions

Hello, I’m Janvi, a passionate knowledge science fanatic at the moment working at Analytics Vidhya. My journey into the world of information started with a deep curiosity about how we are able to extract significant insights from advanced datasets.