Solved

what is (+)

Posted on 2011-09-14
11
335 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 50 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 15

Accepted Solution

by:
Devinder Singh Virdi earned 120 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 20 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 100 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 120 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 56

Assisted Solution

by:HainKurt
HainKurt earned 60 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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses

630 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