SQL LEFT JOIN vs. LEFT OUTER JOIN

SQL is a robust software for interacting with relational databases. When working with tables in SQL, you typically want to mix information 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 perform. 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 sort of SQL JOIN operation that mixes rows from two tables based mostly on a associated column. The important thing characteristic of a LEFT JOIN is that it returns all rows from the left desk and the matched rows from the fitting desk. If there’s no match, the consequence will embrace NULL values for columns from the fitting 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 isn’t assigned to any division, the consequence will present NULL for the division identify.

Additionally Learn: Joins In SQL – Interior, 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 fitting desk. If there’s no match, it returns NULL for columns from the fitting desk. The time period “OUTER” is elective 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 might 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, similar to the LEFT JOIN. If an worker isn’t assigned to any division, the consequence 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: Contains the elective “OUTER” key phrase for readability.

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

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 Information into Tables

Now, insert some pattern information into each tables:

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

Output:

-- Insert information 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 information, 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

• Information Retrieval: Use a LEFT JOIN if you want all data from the left desk, even when there are not any matching data in the fitting desk.
• Reporting: Superb for producing reviews the place you wish to embrace all gadgets (e.g., merchandise, workers) no matter their relationships.
• Information 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 goal of mixing information from two tables whereas guaranteeing that every one data from the left desk are included within the consequence set. The selection between utilizing one time period over the opposite typically comes down to private or organizational choice. Understanding this may improve your effectivity when writing SQL queries and forestall confusion throughout information manipulation duties.

Put your SQL information to check with these SQL Tasks!

Continuously Requested Questions

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