SELECT * FROM employee_expense;
EMP_ID YEAR MONTH EXPENSE_CLAIM APPROVED_AMT PAID_DATE
---------- ---------- ---------- ------------- ------------ ---------
7369 2002 2 3072.43 3072.43 03-MAR-02
7369 2002 4 30 30 01-JUN-02
7369 2002 5 235.03 35.03 01-JUN-02
7369 2002 9 5095.98 5095.08 31-OCT-02
7369 2002 12 1001.01 1001.01 01-FEB-03
7782 2002 1 111.09 111.09 01-FEB-02
7782 2002 3 9.85 9.85 01-APR-02
7782 2002 7 3987.32 3987.32 01-AUG-02
7782 2002 9 1200 1200 01-OCT-02
SELECT * FROM months WHERE year = 2002; YEAR MONTH
---------- ----------
2002 1
2002 2
2002 3
2002 4
2002 5
2002 6
2002 7
2002 8
2002 9
2002 10
2002 11
2002 12
SELECT ee.emp_id, m.year, m.month, NVL(ee.expense_claim,0) FROM (SELECT * FROM months WHERE year = 2002) m LEFT OUTER JOIN employee_expense ee PARTITION BY (ee.emp_id) ON m.year = ee.year AND m.month = ee.month ORDER BY ee.emp_id, m.month; EMP_ID YEAR MONTH NVL(EE.EXPENSE_CLAIM,0)
---------- ---------- ---------- -----------------------
7369 2002 1 0
7369 2002 2 3072.43
7369 2002 3 0
7369 2002 4 30
7369 2002 5 235.03
7369 2002 6 0
7369 2002 7 0
7369 2002 8 0
7369 2002 9 5095.98
7369 2002 10 0
7369 2002 11 0
7369 2002 12 1001.01
7782 2002 1 111.09
7782 2002 2 0
7782 2002 3 9.85
7782 2002 4 0
7782 2002 5 0
7782 2002 6 0
7782 2002 7 3987.32
7782 2002 8 0
7782 2002 9 1200
7782 2002 10 0
7782 2002 11 0
7782 2002 12 0
Notice the PARTITION BY clause in this query. That clause is new in Oracle Database 10g, and in this example it causes the database engine to conceptually perform the following steps:
-
Divide the rows from employee_expense into groups based on their emp_id values, one group per value.
-
Outer join each group to the months table as a separate operation.
The key here is that rather than one outer join, you are getting the equivalent of many outer joins, but with a much simpler syntax, and from one query. The preceding query is logically equivalent to the following UNION ALL query:
SELECT NVL(ee.emp_id, 7369), m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN (SELECT *
FROM employee_expense
WHERE emp_id = 7369) ee
ON m.year = ee.year AND m.month = ee.month
ORDER BY m.month
UNION ALL
SELECT NVL(ee.emp_id, 7782), m.year, m.month, NVL(ee.expense_claim,0)
FROM (SELECT * FROM months WHERE year = 2002) m
LEFT OUTER JOIN (SELECT *
FROM employee_expense
WHERE emp_id = 7782) ee
ON m.year = ee.year AND m.month = ee.month
ORDER BY m.month;