Oracle Outer JOIN example

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.

Outer Joins

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