Oracle DB: Differences Between JOIN Joinings LEFT JOIN and LEFT OUTER JOIN

The difference between LEFT JOIN and LEFT OUTER JOIN remains unclear for many. And there are also many for whom the word outer is not far from optional.

Here is the problem. Knowing that in Oracle, a left join (LEFT JOIN) by default constitutes an outer join (OUTER JOIN) the use of the terms INNER (internal) and OUTER (external) appears strange. Indeed, it should be obvious that INNER associates itself only with JOIN or FULL JOIN (which logically is the same thing if internal) to provide all the correspondences present in two tables. And that OUTER associates with LEFT, RIGHT and FULL.

In fact, the joins in the Oracle DBMS (and especially LEFT JOIN and LEFT OUTER JOIN) can be very confusing, even more for beginners.

Let’s define each of them to answer other recurring questions about them.

Oracle has three types of OUTER JOIN: LEFT, RIGHT and FULL.

A left outer join (LEFT OUTER JOIN) contains all records in the “left” table, even if it has no match in the “right” table specified in the join. A right outer join (RIGHT OUTER JOIN) contains all records in the “right” table, even if it has no match in the “left” table. A full outer join (FULL OUTER JOIN) contains all the records in the left and right tables.

An outer join (OUTER JOIN) extends the result of a single join by returning all rows that satisfy the join condition, but also some or all of the rows from a given table, for which no row from the other table does not satisfy the join condition.

The keyword OUTER (external) is optional. However, in reality, it should be mandatory. It is also recommended to always include it in the outer joins of Oracle (OUTER JOIN) to remember that it is indeed an outer join.

Remember also that the keywords LEFT, RIGHT and FULL are mandatory, just like JOIN. The LEFT, RIGHT, and FULL OUTER JOIN types are the only types of OUTER JOIN.

The INNER JOIN inner join command means that all result rows were produced by matching to a condition between the two tables. An outer join command, OUTER JOIN, displays result rows where there is sometimes no match, but rows in one or both tables are returned unmatched.

Leave a Reply

Your email address will not be published. Required fields are marked *