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
NIce
SELECT a.x,b.x FROM a,b WHERE a.x(+)=b.x AND a.x(+)=2;
BUT….
For this situation I prefer ANSI outer join notation instead of plus notation
SELECT a.x,b.x FROM a right outer join b on (a.x=b.x AND a.x=2)
Also look at the next example for get some ideas
create table aa (x varchar2(10));
create table bb (x varchar2(10));
insert into aa values (‘qwe’);
insert into aa values (‘asd’);
insert into bb values (‘a’);
insert into bb values (‘z’);
I want something like substr(aa.x,1,1)(+)=bb.x
First
SELECT aa.x,bb.x FROM aa,bb WHERE substr(aa.x,1,1)=bb.x
give
X X
———- ———-
asd a
1 rows selected
but what about value ‘z’ from bb
Second
SELECT aa.x,bb.x FROM aa,bb WHERE substr(aa.x,1,1)(+)=bb.x
give
Error starting at line 1 in command:
SELECT aa.x,bb.x FROM aa,bb WHERE substr(aa.x,1,1)(+)=bb.x
Error at Command Line:1 Column:52
Error report:
SQL Error: ORA-00936: missing expression
00936. 00000 – “missing expression”
*Cause:
*Action:
Third
SELECT aa.x,bb.x FROM aa right outer join bb on (substr(aa.x,1,1)=bb.x)
give
X X
———- ———-
asd a
z
2 rows selected
So ….. “Food for thought”
Many thanks,
Alex
Pretty cool. I vaguely remember that there is more you can do with the ANSI syntax than the old (+) way but I’ve forgotten the details. This seems like a good example – using a function in the outer join condition. I know I should abandon the old syntax but I’ve been resisting.
gt;I’ve been resisting
i too
but we must 😉
easy ? :
SELECT aa.x,bb.x FROM aa,bb WHERE substr(aa.x(+),1,1)=bb.x
I find the (+)-Syntax very intuitive.
Don’t like the ANSI-style ( never can remember where left and right are 🙂
Only use it for full outer joins
see also
http://www.sqlfail.com/2012/08/24/for-some-things-you-have-to-get-ansi/
Thanks for your comment.
Oracle syntax is easy – you put the plus on the table where you’re adding a blank row that matches anything.
ANSI syntax is counter-intuitive – you say LEFT outer if the right hand table is missing a row, and RIGHT outer if the left hand table is missing a row.
(At least, that’s my excuse for thinking Oracle is easier to remember than ANSI.)
😉
Seriously, though, wouldn’t it have been easier if the terminology were:
“left preserved” join, “right preserved” join and “full(y) preserved” join.
Jonathan,
Thanks for your comments. I have to think about it every time I see an outer join – either ANSI or Oracle. Kind of like “its” or “it’s” – sadly, I have to stop and think if I have it right.
– Bobby
Good stuff.
Glad I’m not the only one having a hard time remembering the outer join syntax.
My notes and graphics are here
https://sites.google.com/site/youvisualize/sql-joins/outer-joins
Kyle,
Thanks for your comment and the link with the helpful illustrations of the different types of outer joins.
– Bobby
Kyle,
good stuff, but there are missing examples for “partition outer join” and “cross apply”
Best regards,
Sayan Malakshinov
Maybe anti-join can help to remember:
is equal to
Best regards,
Sayan Malakshinov
Thanks for your comment. I edited the tags to work with this blog software’s comment formatting.
Pingback: Kyle Hailey » Outer Joins : which side does the (+) go on
===================================== To display Class,Group,Subgroup using UNION ALL==============================
select distinct x.vc_reason_desc,x.vc_group_desc,x.vc_sub_group_desc from(SELECT
r.vc_reason_desc,g.vc_group_desc,s.vc_sub_group_desc
from mst_reason r,mst_group g,mst_sub_group s
where r.vc_comp_code=g.vc_comp_code
AND r.vc_reason_code=g.vc_reason_code
AND g.vc_comp_code=s.vc_comp_code
AND g.vc_group_code=s.vc_group_code
UNION ALL
select r.vc_reason_desc,g.vc_group_desc,to_char(null) vc_sub_group_desc
from mst_reason r,mst_group g
where r.vc_comp_code=g.vc_comp_code
AND r.vc_reason_code=g.vc_reason_code
union all
select r.vc_reason_desc ,to_char(null) vc_group_desc,to_char(null) vc_sub_group_desc
from mst_reason r) x
==============================================AND Same COde using Join===========================================
select r.vc_reason_desc,g.vc_group_desc, s.vc_sub_group_desc
from mst_reason r,mst_group g,mst_sub_group s
where r.vc_comp_code=g.vc_comp_code(+)
AND r.vc_reason_code=g.vc_reason_code(+)
AND g.vc_comp_code=s.vc_comp_code(+)
AND g.vc_group_code=s.vc_group_code(+)
I have doubt in union all code. Both the codes are not giving same output.
Please help.
I think that your union all version is creating rows with nulls in situations where the outer join version would not. The last sub select of your union all query has rows with vc_reason_desc,null,null for every row in the mst_reason table. But your outer join query will only have vc_reason_desc,null,null rows when there is no row in mst_group that matches the row in mst_reason.
Pingback: Blog second anniversary | Bobby Durrett's DBA Blog
Hello,
With Oracle 12C the (+) operator is to avoid:
Could you please help converting this query: how to replace (+) operator by outher join:
SELECT h.cdcode, d.cdcode, f.cdcode
FROM zyda a,
zyes b,
zypo c,
zd00 d,
zdmx e,
zd00 f,
zdmx g,
zd00 h,
zdmx i
WHERE a.nudoss(+) = b.nudoss
AND c.nudoss = b.nudoss
AND b.nudoss = 159169
and d.nudoss(+) = e.nudoss
and f.nudoss(+) = g.nudoss
and h.nudoss(+) = i.nudoss
AND d.cdstco(+) = ‘HID’
AND d.cdregl(+) = ‘FR6’
AND f.cdstco(+) = ‘HID’
AND f.cdregl(+) = ‘FR6’
AND h.cdstco(+) = ‘HID’
AND h.cdregl(+) = ‘FR6’
and e.codsor(+) = b.codsor
AND e.codsor(+) ‘ ‘
and g.codpos(+) = c.sitcod
AND i.codabs(+) = a.motabs
AND i.temabs(+) = CASE WHEN a.numtra = ‘1’ THEN ‘P’ WHEN a.numtra = ‘2’ THEN ‘D’ WHEN a.numtra = ‘3’ OR a.numtra = ‘0’ THEN ‘S’ ELSE a.numtra END
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN c.dateff AND (c.datxxx – 1)
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN a.datdeb(+) AND a.datfin(+)
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN b.datent AND b.datsor
Thank you
Regards
Why do you need to change the SQL statement to ANSI join syntax? If it works as is why change it?
If you were moving to a non-Oracle database that doesn’t support Oracle’s (+) syntax it would make sense. But, if you are running the query on an 12c Oracle database why not leave it alone?
Here is an example script that might help you convert to ANSI joins if you need to. I’m not sure that your between clause with the outer joins really act like outer joins. Here is my script and its output.
sql
output
Bobby
Hello,
Thank you for your reply.
The need to change the (+) by ANSI Sytax is due to a release update from JAVA 1.6 to 1.8 and Oracle from 11 to 12C.
In all queries with (+) we have the error below:
SqlController – ERROR – Unexpected error
org.apache.calcite.sql.parser.SqlParseException: Encountered “+)” at line 1, column 551.
when i changed the (+) by left outer join in other query the result is OK.
But in this query, the changes are still not OK:
This is the last try that i made :
Ansi Join Syntax:
SELECT h.cdcode, d.cdcode, f.cdcode
FROM zyda a right outer join zyes b on (a.nudoss= b.nudoss) AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN a.datdeb AND a.datfin,
zd00 d right join zdmx using(nudoss) ,
zdmx e right outer join zyes k on (e.codsor=k.codsor) and e.codsor ‘ ‘,
zd00 f right join zdmx using(nudoss) ,
zdmx g right outer join zypo c on g.codpos = c.sitcod,
zd00 h right join zdmx using(nudoss) ,
zdmx i right outer join zyda a on i.codabs = a.motabs and i.temabs = CASE WHEN a.numtra = ‘1’ THEN ‘P’ WHEN a.numtra = ‘2’ THEN ‘D’ WHEN a.numtra = ‘3’ OR a.numtra = ‘0’ THEN ‘S’ ELSE a.numtra END
WHERE
c.nudoss = b.nudoss
and b.nudoss = 159169
and d.cdstco =’HID’ AND d.cdregl = ‘FR6′
and f.cdstco =’HID’ AND f.cdregl = ‘FR6′
and h.cdstco =’HID’ AND h.cdregl = ‘FR6′
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN c.dateff AND (c.datxxx – 1)
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN b.datent AND b.datsor
ERROR at line 2:
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
Old syntax:
SELECT h.cdcode, d.cdcode, f.cdcode
FROM zyda a,
zyes b,
zypo c,
zd00 d,
zdmx e,
zd00 f,
zdmx g,
zd00 h,
zdmx i
WHERE a.nudoss(+) = b.nudoss
AND c.nudoss = b.nudoss
AND b.nudoss = 159169
and d.nudoss(+) = e.nudoss
and f.nudoss(+) = g.nudoss
and h.nudoss(+) = i.nudoss
AND d.cdstco(+) = ‘HID’
AND d.cdregl(+) = ‘FR6’
AND f.cdstco(+) = ‘HID’
AND f.cdregl(+) = ‘FR6’
AND h.cdstco(+) = ‘HID’
AND h.cdregl(+) = ‘FR6’
and e.codsor(+) = b.codsor
AND e.codsor(+) ‘ ‘
and g.codpos(+) = c.sitcod
AND i.codabs(+) = a.motabs
AND i.temabs(+) = CASE WHEN a.numtra = ‘1’ THEN ‘P’ WHEN a.numtra = ‘2’ THEN ‘D’ WHEN a.numtra = ‘3’ OR a.numtra = ‘0’ THEN ‘S’ ELSE a.numtra END
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN c.dateff AND (c.datxxx – 1)
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN a.datdeb(+) AND a.datfin(+)
AND to_date(’01/10/1987′,’DD/MM/YYYY’) BETWEEN b.datent AND b.datsor
Thank you very much for your help.
Best Regards
Riadh
Maybe something like this?
FYI. I had to edit this on 6/15/18 to put more conditions in the left join ON clauses instead of the where clause. I also reordered the joins to get exactly the same plan as the oracle version.
Bobby
Zip of my testing for this discussion:
https://www.bobbydurrettdba.com/uploads/blogquestion06122018.zip
Thanks you very much Bobby, it works great: it gave exactly the same result as the old version: with the (+) Operator.
Best Regards.
Riadh
Great. I didn’t have any data to test against so I couldn’t be sure but I kept tweaking the new query until it got the same plan as the old one. It was a good learning experience for me because I don’t work with outer joins that much.
Dear Bobby,
I have a question please about the Oracle error ORA-01427:
UPDATE D_IFSUP SET ( C_POSADM , D_DEB_POSADM , D_FIN_POSADM ) = ( SELECT SUBSTR(ZYPO.SITCOD,1,5), ZYPO.DATEFF,
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
UPDATE D_IFSUP SET ( D_IFSUP.GRADE, D_IFSUP.ECHELON ) = ( select CASE
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
UPDATE D_IFSUP SET ( D_IFSUP.CORPS ) = ( select CASE
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
Here is a sample of the update query :
UPDATE D_IFSUP SET ( C_POSADM , D_DEB_POSADM , D_FIN_POSADM ) = ( SELECT SUBSTR(ZYPO.SITCOD,1,5), ZYPO.DATEFF,
DECODE(TO_CHAR(ZYPO.FINREE, ‘DD/MM/YYYY’),’01/01/0001′, ”, ZYPO.FINREE)
FROM ZYPO WHERE D_IFSUP.NUDOSS = ZYPO.NUDOSS
AND ZYPO.DATEFF = D_IFSUP.D_OBS
OR TO_CHAR(ZYPO.FINREE, ‘DD/MM/YYYY’) = ’01/01/0001′ )
);
Thanks you very much in advance.
Best Regards.
The ORA-01427 means that the subselect is returning more than one row to update the three columns of one row of the D_IFSUP table. The conditions in the where clause of the select statement need to limit the results to one row or you need some sort of aggregate function like SUM() or MAX() to get only one row. My guess is that you need parentheses around your date conditions like this: AND (ZYPO.DATEFF = D_IFSUP.D_OBS
OR TO_CHAR(ZYPO.FINREE, ‘DD/MM/YYYY’) = ’01/01/0001′ ))
That’s just a guess. When you have AND and OR conditions I think it is helpful to use parentheses to at least make it clear what you are ORing and ANDing.
Bobby