Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

what is (+)

Posted on 2011-09-14
11
Medium Priority
?
339 Views
Last Modified: 2012-05-12
hi guys

I am trying to understand this query

        SELECT   gm.id
                ,gm.desc                
          FROM gac_master gm
              , gac_mgr_assign glma
              , lm_assign_status lmas
          WHERE gm.gac_cd = glma.gac_cd(+)
          and gm.gac_cd = lmas.gac_cd(+)
          and gm.region = 'PARIS'


what does (+) mean.
Doest it mean join?
Is this a proper way to write query?

thanks
0
Comment
Question by:royjayd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
  • +3
11 Comments
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 200 total points
ID: 36538304
It is the old Oracle syntax for a outer join.
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36538313
Everything you ever want to know about Oracle is in the online docs.

http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/queries006.htm#SQLRF52354
0
 
LVL 12

Expert Comment

by:viralypatel
ID: 36538322
it means nothing. It just got copied from the source where you copied this query from. The query wont work until you remove the (+); you'll get a syntax error.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 480 total points
ID: 36538326
This is called outer join.
Consider the situation. You have two tables departments and employees.
Management had created new department with department number 100, but there is no employee working on that department. In your report you want to show that department.
Now with equvi join, this department number (100) will not be reflected because there is no match on employee table, however if you use + then you will able to see department with no employee.

Note: use + sign where ever there is deficiency of records.

Same thing in your query gc_cd may not be be present in glma and imas tables, but you want to pull all records from gm even if there is no matching records from glma/imas.

This join is called outer join/.
0
 
LVL 12

Assisted Solution

by:viralypatel
viralypatel earned 80 total points
ID: 36538345
my bad, it mean outer join; ignore the previous post.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 36538359
it's the same as writing the query like this in ANSI syntax


SELECT gm.id, gm.DESC
  FROM gac_master gm
      LEFT OUTER JOIN gac_mgr_assign glma
          ON gm.gac_cd = glma.gac_cd
      LEFT OUTER JOIN lm_assign_status lmas
          ON gm.gac_cd = lmas.gac_cd
 WHERE gm.region = 'PARIS'
0
 

Author Comment

by:royjayd
ID: 36538479
>>>It just got copied from the source where you copied this query from
:-))

i knew it was some kind of join but how do you figure its LEFT OUTER JOIN and not right outer join?

thx all
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 400 total points
ID: 36538506
the (+) goes on the table that might be missing rows from a join

your glma and lmas tables were both indicated by (+)  so those were the ones needing "filled in"  that made gm the driving table

Since it was the first table in your list, the others were left joined to it.

I could have written it like this too for the same effect


SELECT gm.id, gm.DESC
  FROM gac_mgr_assign glma
      RIGHT OUTER JOIN gac_master gm
          ON gm.gac_cd = glma.gac_cd
      LEFT OUTER JOIN lm_assign_status lmas
          ON gm.gac_cd = lmas.gac_cd
 WHERE gm.region = 'PARIS'
0
 
LVL 15

Assisted Solution

by:Devinder Singh Virdi
Devinder Singh Virdi earned 480 total points
ID: 36538524
When you use (+) that means there is deficiency on that table,
when you use left outer join, that means you want all records from left side. In order words right side has deficiency.
Similarly there is Right outer join and full outer join exists.
0
 
LVL 59

Assisted Solution

by:HainKurt
HainKurt earned 240 total points
ID: 36538535
if your oracle version is less than 9, it does not support left/right joins... (+) is the way to go...
0
 

Author Comment

by:royjayd
ID: 36538859
using the actual keyword like 'LEFT OUTER JOIN'  is so easy to understand. I am surprised oracle doesnt have that prior to ver 9. understanding (+) is more work, just sayin.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question