Left join vs. left outer join.

While going through the types of Oracle joins, we come to the point where we ask what is the difference between different types of Oracle joins or simply the most asked question, what is the difference between left join and left outer join?

In this article, we will prove that there is no significant difference between left join and left outer join. To make it clear to all of you, let’s go through the given discussion.

Firstly, the database looks into the second or right table columns and matches them with the left table. If a left table column matches with the right table, then a row is added to the left table.

Even during matching, a column of the right table doesn’t match that of a left table; even then, a row is kept in the left table and put NULL in the corresponding column of the right table.

Left join vs left outer Join difference

A left join is one of the types of outer join. Putting exterior in its name would be no different. OUTER is just an optional keyword for left join in some databases. The results of the left join are exactly the same as the left outer join. 

Left join returns what inner join would return on the same table, including the unmatched columns from one table corresponding to no column in the other table.

Similarly, left join works by returning all matched values of left and suitable tables with all unmatched values of the left table and null fields where right table columns do not match with columns of the left table.

Pictorial representation

Describing the left join using an example and its Venn diagram would make it easy to understand the outer join and its working. 

The given example has two tables. Table A has the players’ names and IDs, and table B has the games and the player’s ID who like that game.

IIzkcQ7 PajKOjW1ZedZ37psd dUYXPC8aUfjduBAoDyjxcMpf P2zSktTpmYtjioQbNSBKKR3bBtW89znlH F - Left join vs. left outer join.

So, if we apply the left join on this data, the results in the form of table and Venn Diagram will be as follows:

image 48 - Left join vs. left outer join.
Left Join
PlayersGame
HarryNULL
JohnBadminton
JohnSwimming
MonaBasketball
LisaNULL
BellaCricket

Left outer join gives us all the values of Table A in the resultant table; the values of Table A which do not satisfy the join condition of Table B will have a value of NULL for the attributes of Table B.

Left join/left outer join in relational algebra

Relational algebra receives inputs and gives outputs based on results. Its product is a new relation from one or more works. The left outer join is represented as A B in relational algebra. Left Join’s symbol is almost the same as the symbol of natural join, but it has two dashes on the top and bottom left side. 

Features of left join/left outer join.

The basic features of the left join/left outer join are following:

  1. It consists of all the rows from the left table.
  2. It is an inner join + unmatched columns of the left table.
  3. The data not matching the right table is lost.

Syntax of the left join/left outer join

The syntax of the left join is as follows:

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name=table2.column_name;

The syntax of the left outer join is as follows:

SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2 ON table1.column_name =table2.column_name;

The only difference is the OUTER keyword which is optional to use. Both queries have the same results when applied.

The syntax is explained in detail in the following steps.

  1. SELECT – start by making a list of all the columns you need in the output as a result of all the tables in the input.
  2. FROM – the left table is put in the from clause where all the records are to be kept.
  3. LEFT JOIN write the name of the second table, i.e., the right table.
  4. ON – this keyword is used to indicate the columns in the two tables that are combined and listed, i.e., the column with matching values.
  5. OUTER – it is an optional keyword used in some databases.

Join Clause 

A join clause works by gathering the rows from two or more tables set up in a relational database or materialized views to create a new streamlined table to serve database users.

Oracle databases, on receiving data in the form of multiple tables coming up in the form clause of the query, perform join. The select list retrieves data from any of these tables and creates a new table.

Types of joins

There are two primary and six secondary types of SQL join operations.

Outer join

As we know, left join is a type of outer join, so let’s have a quick review of an outer join. An outer join gives all of the values in either the left table or the right table.

For example, an outer join of the table of clients and the table of complaints may return all clients, with and without complaints.

Conclusion

So during left join vs. left outer join analysis, we came to know that there is typically no difference between the two.

Instead, it would be better to mention that the left join is also called the outer join, but that depends on choice alone that what you want to call.

Now it has become clear that there is no difference between the two terms, but instead, they are two names of the same term.

Don’t forget to share your precious thoughts about this post with us

Scroll to Top