Left Outer Join Using + Sign in Oracle 11G

Left Outer Join using + sign in Oracle 11g

TableA LEFT OUTER JOIN TableB is equivalent to TableB RIGHT OUTER JOIN Table A.

In Oracle, (+) denotes the "optional" table in the JOIN. So in your first query, it's a P LEFT OUTER JOIN S. In your second query, it's S RIGHT OUTER JOIN P. They're functionally equivalent.

In the terminology, RIGHT or LEFT specify which side of the join always has a record, and the other side might be null. So in a P LEFT OUTER JOIN S, P will always have a record because it's on the LEFT, but S could be null.

See this example from java2s.com for additional explanation.

---

To clarify, I guess I'm saying that terminology doesn't matter, as it's only there to help visualize. What matters is that you understand the concept of how it works.

---

RIGHT vs LEFT

I've seen some confusion about what matters in determining RIGHT vs LEFT in implicit join syntax.

LEFT OUTER JOIN

SELECT *
FROM A, B
WHERE A.column = B.column(+)

RIGHT OUTER JOIN

SELECT *
FROM A, B
WHERE B.column(+) = A.column

All I did is swap sides of the terms in the WHERE clause, but they're still functionally equivalent. (See higher up in my answer for more info about that.) The placement of the (+) determines RIGHT or LEFT. (Specifically, if the (+) is on the right, it's a LEFT JOIN. If (+) is on the left, it's a RIGHT JOIN.)

---

Types of JOIN

The two styles of JOIN are implicit JOINs and explicit JOINs. They are different styles of writing JOINs, but they are functionally equivalent.

See this SO question.

Implicit JOINs simply list all tables together. The join conditions are specified in a WHERE clause.

Implicit JOIN

SELECT *
FROM A, B
WHERE A.column = B.column(+)

Explicit JOINs associate join conditions with a specific table's inclusion instead of in a WHERE clause.

Explicit JOIN

SELECT *
FROM A
LEFT OUTER JOIN B ON A.column = B.column

These
Implicit JOINs can be more difficult to read and comprehend, and they also have a few limitations since the join conditions are mixed in other WHERE conditions. As such, implicit JOINs are generally recommended against in favor of explicit syntax.

Oracle using (+) for left outer join

Need to include the (+) Operator on the Condition with the Literal

The older outer join approach requires that you include the (+) operator for the condition on the literal.

Just put your "retro" mental cap on and return back to the 90's. :)

Here is how it should look with this approach:

SELECT
    c
.DATE,
 u.user_id
FROM
    order_detail_trans c,
    order_detail_trans u
WHERE
    u.trans_id (+) = c.trans_id
    AND   u.trans_type (+) = 'UPDATE DETAIL'

Here is an example with the tables, emp and dept:

SCOTT@db>SELECT
  2      d.deptno,
  3      e.job
  4  FROM
  5      dept d
  6      LEFT OUTER JOIN emp e ON d.deptno = e.deptno
  7      and e.job = 'CLERK'
  8  GROUP BY
  9      d.deptno,
 10      e.job
 11  ORDER BY
 12      1,
 13      2;
  DEPTNO JOB     
      10 CLERK   
      20 CLERK   
      30 CLERK   
      40         


SCOTT@db>SELECT
  2      d.deptno,
  3      e.job
  4  FROM
  5      dept d,
  6      emp e
  7  WHERE
  8      d.deptno = e.deptno (+)
  9   AND e.job (+) = 'CLERK'
 10  GROUP BY
 11      d.deptno,
 12      e.job;
  DEPTNO JOB     
      20 CLERK   
      30 CLERK   
      10 CLERK   
      40    

Believe it or not, I believe most Oracle Applications shops just use this older outer join approach.

How to do LEFT JOIN with double condition in Oracle syntax?

That would be

SQL> select owner.name, owner.value,
  2    coalesce (insur.name, 'insurance') in_name,
  3    insur.value, count(*)
  4  from car, param owner, param insur
  5  where car.id = owner.car_id
  6    and car.id  = insur.car_id (+)
  7    and insur.name (+) = 'insur'
  8    and owner.name = 'owner'
  9  group by owner.name, owner.value, insur.name, insur.value
 10  having count(*) > 1;

NAME     VALUE    IN_NAME              VALUE      COUNT(*)
-------- -------- -------------------- -------- ----------
owner    John     insurance                              2

SQL>

Though, why would you want to use the old Oracle outer-join syntax? When compared to ANSI joins, it has only drawbacks and no advantages (at least, I can't think of any). Actually, I know one - if you use outer join in old Forms & Reports 6i (or even older? I don't think that anyone uses those versions nowadays), their built-in PL/SQL engine might not speak ANSI outer joins so you're doomed to use the old (+) outer join operator. Other than that ... nope, no idea.

Oracle outer join(+) syntax

The columns that are joined on are just before the plus sign.

 ... FROM CustomerShip s LEFT OUTER JOIN (....) i ON s.ShipSeq = i.ShipSeq

left outer join in oracle issues

If I'm understanding your question correctly, you are negating your outer join with the where criteria. Move that criteria to the join instead:

select   b.sales_regn, b.sales_area,  b.terr_num, a.terr_num,  a.terr_name
from     kp_terr_region b
     left outer join  kap_terr  a on a.terr_num = b.terr_num
         and a.valid_to > sysdate
         and a.ptr_type = 'JPN'
         and a.status != 1
         and a.slr_num is null
where b.valid_to > sysdate

Left outer join and necessary subquery with date in Oracle

As MT0 suggested using partitioning and sorting by row number helped. Only had to include value_code in the partitioning for my purpose.

So this query finally did what I wanted:

SELECT *
FROM   CONTRACTS C
       LEFT JOIN
       ( SELECT *
         FROM   (
           SELECT V.*,
                  ROW_NUMBER() OVER ( PARTITION BY CUSTOMER_ID, VALUE_CODE ORDER BY BEGIN_DATE DESC )
                    AS rn
           FROM   VALUES V
         )
         WHERE rn = 1
       ) V
ON     ( C.CUSTOMER_ID = V.CUSTOMER_ID
         AND VALUE_CODE = 'VOLUME' )
WHERE C.STATUS = 'ACTIVE'

left outer join on table with conditional select of record Oracle SQL/PL/SQL

This is a classical greatest-n-per-group problem, but on 300m records. The canonical question on this topic has a number of options, all of which should, logically, work. However, most require performing additional table- or index-scans or performing a lot of sorting. So, I'd highly recommend going with either the FIRST or LAST aggregate functions (aka the most difficult thing to search for in Oracle's documentation). You may know these as the KEEP clause.

FIRST and LAST are Oracle-specific extensions to the SQL standard and their usefulness with large datasets comes from the fact that a SORT GROUP BY is immediately performed on the data-set, which can massively reduce the amount of records considered for the remainder of your query. I'd highly recommend reading Rob van Wijk's blog post for a fuller explanation.

You're correct that you need to provide a sort order, but you want to sort within the context of each group; to go through your logic:

SYS_CR_DT date closest to the ROLLUPDATE( a date that will be provided, without going over it). For the sake of the test case we can say '2012-12-12'

Assuming the maximum date is included in the ORDER BY clause you want to order by SYS_CR_DATE desc.

If two or more SYS_CR_DT dates exist that are the same, choose the record with a CURR_SSN_IND = 'Y'.

Then, by a statement that priorities a specific value. I'd normally implement this with a CASE statement: case when CURR_SSN_IND = 'Y' then 0 else 1 end asc (ascending is the default sort order but when doing complicated things it's clearer if you're explicit).

If a record cannot be selected based on CURR_SSN_IND = 'Y' ( in the case that they are both 'Y' or 'N') select the record where PSEUDO_SSN_IND = 'R'

This is the same logic as above, save with a different column and value; giving you: case when PSEUDO_SSN_IND = 'R' then 0 else 1 end asc

If a match still cannot be selected, choose record at random.

This is where you might run into problems. Oracle will pick a random record for you, there's no need to explicitly state randomness. However, because FIRST and LAST will sort randomly per column you need to explicitly provide an ORDER BY that removes randomness. I'd use the [ROWIDs]5 of the columns in the tables. This is not guaranteed to be consistent over time but will remain so for the duration of a transaction.

---

Looking at your last query I assume that this hasn't been thought through completely as you're not selecting from both your tables and I'm a little confused about your logic with the dates. I'm also a little unsure about the LEFT OUTER JOIN. I'm assuming here that you want a table unique on STU_NO and STU_SEQ_NO that contains all of those present in ACCOUNT_TBL and it doesn't matter whether they're present in STU_SSN_TBL.

So, putting the logic together this is what you'd end up with:

select a.stu_no
     , a.stu_seq_no
     , max(s.curr_ssn_id) keep (
         dense_rank first order by sys_cr_date desc
                                 , case when curr_ssn_ind = 'Y' then 0 else 1 end asc                     
                                 , case when pseudo_ssn_ind = 'R' then 0 else 1 end asc
                                 , s.rowid asc
                                 , a.rowid asc
                                   ) as curr_ssn_id
     , max(s.no) keep (
         dense_rank first order by sys_cr_date desc
                                 , case when curr_ssn_ind = 'Y' then 0 else 1 end asc                     
                                 , case when pseudo_ssn_ind = 'R' then 0 else 1 end asc
                                 , s.rowid asc
                                 , a.rowid asc
                                   ) as no
     , -- ...
     , -- keep repeating for every column
     , -- ...
     , max(s.sys_cr_dt) keep (
         dense_rank first order by sys_cr_date desc
                                 , case when curr_ssn_ind = 'Y' then 0 else 1 end asc                     
                                 , case when pseudo_ssn_ind = 'R' then 0 else 1 end asc
                                 , s.rowid asc
                                 , a.rowid asc
                                   ) as sys_cr_date
     , -- keep repeating for every column
  from account_tbl a
  left outer join stu_ssn_tbl s
    on a.stu_no = s.stu_no
   and a.stu_seq_no = s.stu_seq_no
 where s.sys_cr_date < date '2012-12-12'
 group by a.stu_no
        , a.stu_seq_no

As you can see this is pretty... ugly. But, it should be the most efficient way of performing it.

Your next problem is the 300m rows. On any semi-decent box/disks I wouldn't expect any problems with this, even single-threaded. But, if you have Enterprise edition it'd be worth using the parallel hint and if you don't DBMS_PARALLEL_EXECUTE is more complicated but could be used to chunk ACCOUNT_TBL so that you can parallelise. Ensure that you do this via SQL so that you don't have STU_NO/STU_SEQ_NO combinations in multiple chunks.

You mention that you have a lot of disk space, which is good but if you can avoid the disks you're in a better position. You'll have to do to table accesses here, and one hash join, Oracle will be writing all of this to your temporary tablespace, and will then perform some sort operations. You want to avoid performing these joins and sorts more times than you need to.

---

Your next concern should be whether you need to reproduce this. If you do you have a 2 options:

1. If you need to keep all the data available then use your idea of a DO_NOT_USE flag. This is, I hope, a contradiction and you don't need to do this.
2. Create a new set of tables that has the information you need and archive off the rest - there's no point keeping huge amounts of completely unused data "hot" for use.

If you're going to be performing this sort of operation frequently look at sizing the [buffer cache][9] to be able to put the entire lot (and your normal operations) in RAM.

Oracle left-outer-join syntax shorthand notation (+) available in HQL?

Is there any way to specify a left outer join in HQL?

Yes, HQL does support left outer joins via explicit join syntax, and the syntax is identical to SQL's (with the implied navigation property as the join key):

from Cat as cat
    left join cat.kittens as kitten

outer is optional, viz left [outer] join

Source: HQL Query Reference

Is there a shorthand abbreviation for left outer join, like Oracle's (+)

No, AFAIK the HQL documentation makes no reference to a shorthand left outer join notation, and if HQL adheres to the trend in RDBMS to remove proprietary join syntax, as per @a_horse_with_no_name's comment, Oracle recommends against using the proprietary (+) syntax, and similarly *= in MSSql Server has been deprecated.

---

Related Topics