As you know, an outer join 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 and a right outer joinsimply identify which table in the FROM clause is the one WHERE rows may contain no matches.
An inner join 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."
left outer join and
right outer join
are never used within Oracle's documentation:
both left outer joins and 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
The following MouseOver shows an example query with important features highlighted.
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.
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.
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.
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.
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.