SQL Left Outer Join vs Left Join

In SQL, LEFT JOIN and LEFT OUTER JOIN are among the most commonly used join operations to combine data from multiple tables. These terms are interchangeable, as both retrieve all rows from the left table and the matching rows from the right table, with unmatched rows in the right table appearing as NULL.

In this article, we will cover the syntax and practical applications of LEFT JOIN and LEFT OUTER JOIN, highlighting their key differences and providing detailed, real-world examples to demonstrate their usage effectively.

What is a Left Join?

A LEFT JOIN combines rows from two tables, showing all rows from the left table and matching rows from the right table. If no match is found in the right table, the result includes NULL values for the right table's columns.

Syntax:

SELECT * FROM Table1

LEFT JOIN Table2 ON Table1.columnName = Table2.columnName;

What is a Left Outer Join?

A LEFT OUTER JOIN is conceptually identical to a LEFT JOIN. It combines rows from two tables in the same manner. Showing all rows from the left table and matching rows from the right table. If no match is found in the right table, NULL values are returned. The only difference is that LEFT OUTER JOIN explicitly includes the keyword "OUTER," but this does not affect functionality or performance.

Syntax:

SELECT * FROM Table1

LEFT OUTER JOIN Table2 ON Table1.columnName = Table2.columnName;

LEFT JOIN vs LEFT OUTER JOIN

Note: Whether you use "LEFT JOIN" or "LEFT OUTER JOIN," the results will be the same. The difference is purely in terminology.

Example Tables for LEFT JOIN and LEFT OUTER JOIN

Before go into the examples let's create the data which required to perform the Left outer join and left join.

Step 1: Create a Database

create database geeksforgeeks;
use geeksforgeeks;

Step 2: Create a Table named "athelete"

create table atheletes(Id int, Name varchar(20), 
AtheleteNO int,
primary key(Id)
);

Step 3: Insert values into the table "athelete"

INSERT INTO atheletes (Id, Name, AtheleteNO)
VALUES (1, 'abisheik', 27);
INSERT INTO atheletes (Id, Name, AtheleteNO)
VALUES (2, 'Niyas', 27);
INSERT INTO atheletes (Id, Name, AtheleteNO)
VALUES (3, 'Joseph', 29);
INSERT INTO atheletes (Id, Name, AtheleteNO)
VALUES (4, 'Juan', 24);

Step 4: Create a table named "Points"

CREATE TABLE Points(ID INT, Score INT,AtheleteID INT, 
 PRIMARY KEY (ID)
);

Step 5: Insert values into the table "Points"

INSERT INTO Points (ID, Score, AtheleteID)
VALUES (1, 270, 1);
INSERT INTO Points (ID, Score, AtheleteID)
VALUES (2, 297, 2);
INSERT INTO Points (ID, Score, AtheleteID)
VALUES (3, 210, 3);
INSERT INTO Points (ID, Score, AtheleteID)
VALUES (4, 180, 4);

Now , we have succesfully created the table "athelete" and "Points" for the joins operation.

Example: Using LEFT OUTER JOIN

By using the Left Outer Join, the query will be executed.

Query:

SELECT * FROM atheletes 
 LEFT OUTER JOIN Points ON
 atheletes.ID = Points.AtheleteID;

Output:

Example: Using LEFT JOIN

In this query, we perform a LEFT JOIN on the same tables:

Query:

SELECT * FROM atheletes 
 LEFT JOIN Points ON
 atheletes.ID = Points.AtheleteID;

Output:

Conclusion

In SQL, LEFT JOIN and LEFT OUTER JOIN are two terms for the same operation. Both combine data from two tables, displaying all rows from the left table and matching rows from the right table, with NULL values for non-matching rows. The keyword "OUTER" is optional and has no impact on performance or the query results