-1

I have one SQL query in which they have used Left-Join feature and now there is a requirement to convert it into operator (+) syntax. Please guide me how to do it, The query is as written below :

select (some field names) 
from
ldcs2.component comp
LEFT JOIN ldcs2.component_detail cd
   ON cd.component_id = comp.component_id
LEFT JOIN ldcs2.component_item_breakdown cib
   ON cib.component_item_id = cd.component_item_id

So please guide me what does Left-Join specify here and how can we write it into (+) expression.
Also guide me as they have mentioned second mapping table (ldcs2.component_detail) at first in ON condition, whether it would work differently if we write at first in that condition or not?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
prashant chhabra
  • 1

2 Answers2

0

This is what you could do, but I have to note that personally I prefer the ANSI way.

There are two sides of a join condition. When you use ANSI syntax and code A left join B, you imply that for a record in A, there is no need to be a match on B.

When you put (+) on a specific side of the join condition you imply something like "The field on this side of the condition need not to be matched."

select (some field names) 
from
    ldcs2.component comp,
    ldcs2.component_detail cd,
    ldcs2.component_item_breakdown cib 
where
    cd.component_id (+) = comp.component_id
    and cib.component_item_id (+) = cd.component_item_id
Erkan Haspulat
  • 12,032
  • 6
  • 39
  • 45
  • please find the full code at [http://stackoverflow.com/questions/28538400/left-join-vs-ansi-format-issue], as i am not getting the same result by using both type of queries – prashant chhabra Feb 16 '15 at 09:45
0

You could convert the ANSI/ISO Syntax to Oracle outer join syntax as follows -

SELECT (SOME field names)
FROM ldcs2.component comp,
  ldcs2.component_detail cd,
  ldcs2.component_item_breakdown cib
WHERE comp.component_id     = cd.component_id(+)
AND cd.component_item_id = cib.component_item_id(+)
/

So please guide me what does Left-Join specify here and how can we write it into (+) expression.

For a detailed understanding, see my previous answer on a similar question here https://stackoverflow.com/a/28499208/3989608

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • please find the full code at [http://stackoverflow.com/questions/28538400/left-join-vs-ansi-format-issue], as i am not getting the same result by using both type of queries – prashant chhabra Feb 16 '15 at 09:43
  • In your other question, the two SQLs are different since you have an additional join. – Lalit Kumar B Feb 16 '15 at 09:55
  • my dear friend, both the queries are the exact match, but when i tried with ANSI expression, i am unable to fetch the records when dealing with Outer Joining multiple tables, do you know something about this – prashant chhabra Feb 16 '15 at 10:10