Converting Oracle's join syntax using the plus sign (+) to standard join syntax

oracle, oracle10g, oracle11g, sql

Solution

Something like this:

select p1.product_id, p1.product_name, p2.product_code,
    (decode(p3.product_type, null, 'N/A', p3.product_type) as product_type
from products p1
join product_descriptions p2
    on p1.product_id = p2.product_id
left join product_descriptions p3
    on p3.product_age = p2.product_age
    and p3.product_type = p2.product_type
    and p3.status = 'VALID';

The `where p1.product_id=p2.product_id` is a normal inner join between `p1` and `p2`. The others are outer joins; the way it's written looks like a mix of left and right outer joins, but since `and p2.product_age=p3.product_age(+)` is the same as `and p3.product_age(+)=p2.product_age` then it isn't really; it's a fairly straightforward left outer join between the product of the `p1`/`p2` join and `p3`.

As an aside, I'm not a fan of aliases like `p1`, `p2` and `p3` as they are not descriptive, and it's easy to get lost when you do this in more complicated queries. I'm not alone.

I'm not sure you even need the outer join, but it rather depends on the data. If `product_age` and `product_type` is unique then you could `case` the `p2.status`; if it isn't then you're probably assuming that only one `product_age`/`product_type` row can be `VALID`, otherwise you'd get duplicates. Just musing about what you're trying to do...

Problem

I am trying to understand Oracle join syntax of using (+) to join two tables. Could someon show me how would this query would look if it was converted to use the standard join syntax? ``` select p1.product_id, p1.product_name, p2.product_code, (decode(p3.product_type, null, 'N/A',p3.product_type) as product_type from products p1, product_descriptions p2, product_descriptions p3 where p1.product_id=p2.product_id and p2.product_age=p3.product_age(+) and p2.product_type=p3.product_type(+) and p3.status(+)='VALID' ```

Original source