Outer Join tables with Filter predicates in Oracle.

Most of us must be aware of Oracle Outer joins behaviour and its significance.

Using outer join, We can fetch all rows from one of table and only matched rows from other table with null for all non matched rows.

In current blog will discuss, implication of adding filter conditions on non driving table in an Outer join Conditions.

Let starts with standard emp and dept tables , with below SQL as Example.

SELECT emp.job ,
dept.dname
FROM emp ,
dept
WHERE emp.deptno = dept.deptno
and emp.job = 'MANAGER';

Output :

JOB DNAME
--------- --------------
MANAGER ACCOUNTING
MANAGER RESEARCH
MANAGER SALES

Total Rows : 3.

Emp is Equi join with Dept on Deptno columns, procuring all matched rows from set of tables involved.

Now lets add outer join flavour to our query.
Fetching all rows from dept table and only match once from Emp.

For current example, ignoring filter condition job = ‘MANAGER’ on Emp table.

SELECT emp.job ,
dept.dname
FROM emp ,
dept
WHERE emp.deptno(+) = dept.deptno;

Output :
JOB DNAME
--------- --------------
MANAGER ACCOUNTING
CLERK ACCOUNTING
PRESIDENT ACCOUNTING
ANALYST RESEARCH
ANALYST RESEARCH
MANAGER RESEARCH
CLERK RESEARCH
CLERK RESEARCH
SALESMAN SALES
SALESMAN SALES
SALESMAN SALES
CLERK SALES
SALESMAN SALES
MANAGER SALES
NULL OPERATIONS

All records from Dept tables is projected.
Only “OPERATIONS” Department does not have any employee associated, but still same got projected due to outer join.

Now lets add filter conditions on current query, “emp.job = MANAGER”.

SELECT emp.job ,
dept.dname
FROM emp ,
dept
WHERE emp.deptno(+) = dept.deptno
and emp.job = 'MANAGER';

Output :
JOB DNAME
--------- --------------
MANAGER ACCOUNTING
MANAGER RESEARCH
MANAGER SALES

Total Rows : 3.

Please check “OPERATIONS” Department is not projected in output, due to addition of filter conditions.
Before discussing on same, let add outer condition to filter predicate also.

SELECT emp.job ,
dept.dname
FROM emp ,
dept
WHERE emp.deptno(+) = dept.deptno
and emp.job(+) = 'MANAGER';

JOB DNAME
--------- --------------
MANAGER ACCOUNTING
MANAGER RESEARCH
MANAGER SALES
NULL OPERATIONS

Total Rows : 4.

Now though filter is added, but still all rows from department is projected.
Above SQL is equivalent to below ANSI Standard statement.

SELECT emp.job ,
dept.dname
FROM emp RIGHT JOIN dept
ON (emp.deptno = dept.deptno
AND emp.job = 'MANAGER');

Output data varying for both cases.
With (+), filter conditions is also treated as Outer Conditions and not applied as mandate conditions for projected rows as compared to previous query.

Now let observe underlying Execution plan of SQL to understand current behaviour.

1. With emp.job(+)='MANAGER' filter conditions

-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | 4 |
| 1 | NESTED LOOPS OUTER| | 4 |
| 2 | TABLE ACCESS FULL| DEPT | 4 |
|* 3 | TABLE ACCESS FULL| EMP | 1 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."JOB"(+)='MANAGER' AND
"EMP"."DEPTNO"(+)="DEPT"."DEPTNO")

2. With emp.job='MANAGER' filter conditions.

--------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 |
| 1 | NESTED LOOPS | | 3 |
| 2 | NESTED LOOPS | | 3 |
|* 3 | TABLE ACCESS FULL | EMP | 3 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 |
| 5 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("EMP"."JOB"='MANAGER')
4 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")

Observations on same :
1. With (+) in filter conditions, predicate is applied along with Outer Joining conditions.
As compared to vice versa, filter is applied before imposing join conditions.

2. Without (+) in filter conditions, in Explain plan join operations is transformed to normal join.

Main point to take away is
before adding filter conditions in outer join conditions, check we are not skipping required rows due to missing outer clause (+) on filter conditions.

About Deepak Mahto

Having 8+ Years of relevant/exciting/fruitful/challenging/learning in Oracle Technology. Currently working as Oracle Performance Consultant in an MNC. Hold Expertise in Performance Engineering, SQL Tuning, Database Tuning, SQL, PLSQL Development/Design Considerations and Oracle Internals. I enjoy reading on Oracle internals, posting some of my finding or observations and playing football.
This entry was posted in Oracle Internal and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s