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
left outer join and
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.
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.
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.
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.
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.
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.
Outer Join using "employees" and "departments" in Oracle?
Suppose you have two tables: "employees" and "departments". The "employees" table contains information about each employee in a company, including their name, job title, salary, and department ID. The "departments" table contains information about each department in the company, including its name and ID.
If you want to list all employees and their department names, including those employees who are not yet assigned to any department, you can use an Outer Join.
Here's how the query would look like:
SELECT e.name, d.department_name
FROM employees e
LEFT OUTER JOIN departments d
ON e.department_id = d.department_id;
In this example, we're using a LEFT OUTER JOIN, which returns all rows from the "employees" table and only those rows from the "departments" table where the department ID matches. If an employee is not yet assigned to a department, the department name will be shown as NULL in the result set.
Diagram of Outer JOIN in Oracle
Sample query that contains an outer join
SELECT P.PRODUCT_NAME,
SUM(NVL(SI.SALE_AMOUNT,0)) DOLLARS
FROM PRODUCT P, SALE_ITEM SI
WHERE P.PRODUCT_ID = SI.PRODUCT_ID (+)
GROUP BY P.PRODUCT_NAME
ORDER BY P.PRODUCT_NAME
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.
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.
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.
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.
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.
Characteristic of Outer Joins in Oracle
With the
equality,
non-equality, and
self-joins
you have used so far, a row is returned only if a corresponding record in each table is queried.
These types of joins can be categorized as inner joins because records are listed in the results only if a match is found in each table. In fact, the default INNER keyword can be included with the JOIN keyword to specify that only records having a matching row in the corresponding table should be returned in the results.
However, suppose you want a list of all customers (not just ones who have placed an order) and order numbers for orders the customers have recently placed. Recall that the CUSTOMERS table lists all customers who have ever placed an order, but the ORDERS table lists just the current month's orders and unfilled orders from previous months. An inner join might not give you the exact results you want because some customers might not have placed a recent order.
The query shown below produces an equality join that returns all order numbers stored in the ORDERS table and the name of the customer placing the order.
SELECT c.lastname, c.firstname, o.order#
FROM customers c, order o
WHERE c.customer# = o.customer#
ORDER BY c.lastname, c.firstname
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.
