Outer join – where do I put the (+)?

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);


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

SQL> select a.x,b.x
  2  from a,b
  3  where a.x(+)=b.x;

         X          X
---------- ----------
         1          1

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
---------- ----------

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 no rows.  Then both rows in b are treated as non-matching so a.x is null.  So, maybe it is safe to say that without the (+) the condition is applied after the join and with (+) the condition is applied 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


About Bobby

I live in Chandler, Arizona with my wife and three daughters. I work for US Foods, the second largest food distribution company in the United States. I have worked in the Information Technology field since 1989. I have a passion for Oracle database performance tuning because I enjoy challenging technical problems that require an understanding of computer science. I enjoy communicating with people about my work.
This entry was posted in Uncategorized. Bookmark the permalink.

17 Responses to Outer join – where do I put the (+)?

  1. AlexAnd says:

    SELECT a.x,b.x FROM a,b WHERE a.x(+)=b.x AND a.x(+)=2;

    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

    SELECT aa.x,bb.x FROM aa,bb WHERE substr(aa.x,1,1)=bb.x
    X X
    ———- ———-
    asd a

    1 rows selected

    but what about value ‘z’ from bb

    SELECT aa.x,bb.x FROM aa,bb WHERE substr(aa.x,1,1)(+)=bb.x
    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”

    SELECT aa.x,bb.x FROM aa right outer join bb on (substr(aa.x,1,1)=bb.x)
    X X
    ———- ———-
    asd a

    2 rows selected

    So ….. “Food for thought”

    Many thanks,

    • Bobby says:

      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.

    • 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

      • Bobby says:

        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.

          • Bobby says:


            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

  2. Kyle Hailey says:

    Good stuff.
    Glad I’m not the only one having a hard time remembering the outer join syntax.
    My notes and graphics are here


  3. Maybe anti-join can help to remember:

    select a.x from a,b where a.x=b.x(+) and b.x is null

    is equal to

    select * from a where not exists 
    (select '+' from b where a.x=b.x)

    Best regards,
    Sayan Malakshinov

  4. Pingback: Kyle Hailey » Outer Joins : which side does the (+) go on

  5. Utkarsh Shukla says:

    ===================================== 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
    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
    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.

    • Bobby says:

      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.

  6. Pingback: Blog second anniversary | Bobby Durrett's DBA Blog

Leave a Reply