I can never remember where to put the (+) symbols in an Oracle SQL query to make it have an outer join. I rarely need to write a query with an outer join and when I do I have to build myself a test case to remember how it works. I did this just today so I thought I’d blog it.
So, I setup two tables with one matching row and one non-matching. They have one column x which I will join on.
create table a (x number); create table b (x number); insert into a values (1); insert into a values (2); insert into b values (1); insert into b values (3); commit;
So then I join these putting the (+) outer join operator in different places until I find a couple that don’t have syntax errors. Here is the result:
SQL> select a.x,b.x 2 from a,b 3 where a.x=b.x(+); X X ---------- ---------- 1 1 2 SQL> select a.x,b.x 2 from a,b 3 where a.x(+)=b.x; X X ---------- ---------- 1 1 3
So, when I see this I think about the real query I’m building and I ask myself “which table gets the (+)?” So, I think that the side with the (+) is the side that doesn’t have to match. In the first case table b only matches where x=1 but you see both of a’s records x=1 and 2. The second query reverses it. Or, another way to say this is that the side of the equals sign without the outer join symbol has all of its rows returned and the other side only has the matching rows.
The other thing that confuses me is constants. Do we need the outer join symbol with a where clause condition on a constant, i.e. on a condition that doesn’t join the two tables?
SQL> select a.x,b.x 2 from a,b 3 where a.x(+)=b.x 4 and a.x=2; no rows selected SQL> select a.x,b.x 2 from a,b 3 where a.x(+)=b.x 4 and a.x(+)=2; X X ---------- ---------- 3 1
I would think that the first query would return the same results as the second but it doesn’t. I guess the meaning of the first query is that you only return rows where a.x=2. The column a.x is null on the row where a doesn’t match b. The a row that does match b has a.x=1. No neither null or 1 equals 2 so you get no rows returned.
The second query is weird because a is null for both of b’s values of x. But I think the way this works is that the condition a.x=2 is first applied leaving a with one row with x=2. Then the database treats both rows in b as non-matching because x <> 2 for both rows in b so a.x is null in the output.
So, maybe it is safe to say that without the (+) the database applies the condition after the join and with (+) the database applies the condition before it.
Not very profound but at least having this on my blog will be something I can refer to a month from now when I need this again.
– Bobby
P.S. I was looking at the plans of the two examples with constants and they show how the database processes the two cases. Here is a zip of the example script and its output: zip
Plan with a.x=2:
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN | |
|* 2 | TABLE ACCESS FULL| A |
|* 3 | TABLE ACCESS FULL| B |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."X"="B"."X")
2 - filter("A"."X"=2)
3 - filter("B"."X"=2)
Plan with a.x(+)=2:
-----------------------------------
| Id | Operation | Name |
-----------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | HASH JOIN OUTER | |
| 2 | TABLE ACCESS FULL| B |
|* 3 | TABLE ACCESS FULL| A |
-----------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."X"="B"."X")
3 - filter("A"."X"=2)
Notice that the first plan does not do an outer join at all. It filters out all the rows in A and B that don’t have x=2. Then it does a normal hash join. I guess the optimizer transforms the outer join into a standard hash join because it knows that only rows with x=2 from both tables will fulfill the conditions.
The second plan does the outer join but it filters out all rows in A except those that have x=2. Then it does the outer join with the rows in A that survived the filter.
P.P.S. ANSI join versions of the four (+) queries listed in main post:
SQL> select a.x,b.x
2 from a
3 left join b
4 on a.x=b.x;
X X
---------- ----------
1 1
2
SQL>
SQL> select a.x,b.x
2 from a
3 right join b
4 on a.x=b.x;
X X
---------- ----------
1 1
3
SQL>
SQL> select a.x,b.x
2 from a
3 right join b
4 on a.x=b.x
5 where a.x=2;
no rows selected
SQL>
SQL> select a.x,b.x
2 from a
3 right join b
4 on a.x=b.x
5 and a.x=2;
X X
---------- ----------
3
1
ANSI joins example script and log: zip
