Join Queries  «Prev  Next»

Lesson 3 Oracle’s outer join
Objective Interpret an Outer JOIN using Oracle’s Syntax

Interpret Oracle's Outer JOIN using Syntax

As you know, an outer join[1] is a special variation of the kind of query that combines two or more tables into a single query. In an outer join, rows in one table, which contain no match with another table (based on the JOIN clause in the WHERE clause) are included in the result set returned FROM the query. A left outer join[2] and a right outer join[3]simply identify which table in the FROM clause is the one WHERE rows may contain no matches. An inner join[4] is a JOIN WHERE only the rows of one table that are matched with the rows of the other table are used in the query results. Oracle documentation usually refers to an inner JOIN as simply a "join."
The terms
  1. left outer join and
  2. right outer join
are never used within Oracle's documentation.
Both 1) left outer joins and 2) right outer joins are simply called "outer joins."

Syntax of an outer join

Here is the basic syntax of an outer join: 
SELECT col1, col2, ...FROM table1, table2
WHERE table1.col1(+) = table2.col2
AND ...

The following diagram shows an example query with important features highlighted.
Outer Join Diagram
  1. This column appears in the SELECT clause. It is set to null values whenever the join clause does not find a match. That is, when a product is not found in the SALE_ITEM table, the SALE_AMOUNT is null.
  2. Notice that the FROM clause is not any different than a regular join's FROM clause. There is no way to tell, directly from this clause, which table is the subject of the outer join.
  3. An outer join is indicated when you see this symbol added to a column in the WHERE clause. In this example, the SALE_ITEM table is the outer join table, meaning that a row in the PRODUCT table is returned even if it has no matching row in the SALE_ITEM table.
  4. This row is one that would not appear if this were a normal join. The SALE_AMOUNT column appears as a zero because the query uses the NVL function to convert the null value to a zero.
  5. These rows are ones that contain matching rows in the SALE_ITEM table and the SALE_AMOUNT values have been added up into a single sum, one sum for each product.

Oracle Outer Join - Example
Oracle uses the (+) symbol to indicate an outer join. To help you remember what this means, just imagine that the (+) symbol means that Oracle manufactures (adds) a row with every column containing null values if it cannot find a matching row in the table. This is essentially what Oracle does to execute the query. Of course, this "added" row is only a placeholder used in your query and never actually exists in the table.
In the next lesson, you will learn a truly powerful SQL query construction called the correlated sub-query.

Inner Join Outer Join - Quiz

Click the Quiz link below to test your knowledge of the material we have covered so far in this module.
Inner Join Outer Join - Quiz

[1] Outer join: A special variation of the kind of query that combines two or more tables into a single query. In an outer join, rows in one table that contain no match with the other table are included in the result set returned from the query.
[2] Left outer join: An outer join in which the left table in the From clause may contain rows that do not match with a row in the right table.
[3] Right outer join: An outer join in which the right table in the From clause may contain rows that do not match with a row in the left table.
[4] Inner join: A join where only the rows of one table that are matched with the rows of the other table are used in the query results.