Solved

what is (+)

Posted on 2011-09-14
11
310 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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle SQL Select within a Where Clause 9 58
Oracle query output question 4 36
SQL Query 34 82
'G_F01' is not a procedure or is undefined 3 13
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…

863 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

24 Experts available now in Live!

Get 1:1 Help Now