Solved

what is (+)

Posted on 2011-09-14
11
303 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
  • 2
  • 2
  • 2
  • +3
11 Comments
 
LVL 76

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 76

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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 73

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 73

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 51

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
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…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

760 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now