Oracle Outer JOIN example

Give an example of an Outer Join 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