SQL LEFT JOIN vs. LEFT OUTER JOIN

SQL is a robust instrument 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 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 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 fitting desk. If there’s no match, the end result will embody 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.title, 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 end result will present NULL for the division title.

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 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 might rewrite our question as follows:

As you may 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.title, 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 isn’t assigned to any division, the end result will present NULL for the division title.

Additionally Learn: SQL Information from Fundamentals to Advance Degree

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 non-public 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 workers 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,
    title 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 and marketing');

Output:

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

LEFT JOIN Question

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

Output:

RIGHT JOIN Question

SELECT workers.title, 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 once you want all information from the left desk, even when there are not any matching information in the fitting desk.
• Reporting: Superb for producing studies the place you need to embody all objects (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 goal of mixing knowledge from two tables whereas making certain that every one information from the left desk are included within the end result set. The selection between utilizing one time period over the opposite typically comes down to private or organizational choice. Understanding this will improve your effectivity when writing SQL queries and forestall confusion throughout knowledge manipulation duties.

Put your SQL data to check with these SQL Initiatives!

Regularly Requested Questions