SQL Left Outer Join vs Left Join

In SQL, both “LEFT JOIN” and “LEFT OUTER JOIN” are used to combine data from two or more tables based on a related column, but they are essentially the same operation, and there is no practical difference between them. The keyword “OUTER” is optional in most database systems, including popular ones like MySQL, PostgreSQL, and SQL Server.

Left Join or Left Outer Join

Left Join

A SQL operation that combines two tables, showing all rows from the left table and matching rows from the right table. If there is no match in the right table, it displays NULL values.

Syntax

SELECT * FROM Table1

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

Left Outer Join

Another SQL operation that is conceptually identical to LEFT JOIN. It also combines tables, showing all rows from the left table and matching rows from the right table. If there is no match in the right table, it displays NULL values.

Syntax

SELECT * FROM Table1

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

“LEFT JOIN” and “LEFT OUTER JOIN” are essentially the same thing in SQL. They both combine data from two tables, showing all rows from the left table and matching rows from the right table. If there’s no match in the right table, they display NULL values. The only difference is terminology: “LEFT JOIN” is used in some databases like MySQL, while “LEFT OUTER JOIN” is used in others like SQL Server. In more deeper way “System.out.print()” used in java, at same time “print” used in python but both reflects the same output. So, it’s just a matter of which term your database system prefers, but the outcome is identical.

Before diving 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:

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

Id	Name	AtheleteNO	ID	Score	AtheleteID
1	Abisheik	27	1	270	1
2	Niyas	27	2	297	2
3	Joseph	29	3	210	3
4	Juan	24	4	180	4

Example:

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

Query

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

Output

Id	Name	AtheleteNO	ID	Score	AtheleteID
1	Abisheik	27	1	270	1
2	Niyas	27	2	297	2
3	Joseph	29	3	210	3
4	Juan	24	4	180	4

Conclusion

In SQL, “LEFT JOIN” and “LEFT OUTER JOIN” are essentially the same operation, differing only in terminology. Both operations combine data from two tables, displaying all rows from the left table and matching rows from the right table while showing NULL values for non-matching rows. Regardless of the term used in your database system, the outcome remains identical.

FAQs on Left Join vs Left Outer Join

Q.1: Is there any performance difference between LEFT JOIN and LEFT OUTER JOIN?

Answer:

No, there is no performance difference. These terms are interchangeable, and the database optimizer handles them in the same way.

Q.2: Can I use LEFT JOIN or LEFT OUTER JOIN with more than two tables?

Answer:

Yes, you can use these join operations with multiple tables by extending the syntax, specifying the join conditions for each table.

Q.3: Do all database systems support both LEFT JOIN and LEFT OUTER JOIN?

Answer:

Most widely used database systems, including MySQL, PostgreSQL, and SQL Server, support both LEFT JOIN and LEFT OUTER JOIN. However, it’s essential to check your specific database documentation for compatibility.

Q.4: What happens if I use LEFT JOIN or LEFT OUTER JOIN without specifying a join condition?

Answer:

Using these join operations without a join condition will result in a Cartesian product, where each row from the left table is combined with every row from the right table, potentially leading to a large and inefficient result set.

Q.5: Are there other types of SQL joins?

Answer:

Yes, SQL supports various types of joins, including INNER JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN, each serving different purposes in combining data from multiple tables.