[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 349
  • Last Modified:

what is (+)

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
royjayd
Asked:
royjayd
  • 2
  • 2
  • 2
  • +3
6 Solutions
 
slightwv (䄆 Netminder) Commented:
It is the old Oracle syntax for a outer join.
0
 
slightwv (䄆 Netminder) Commented:
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
 
viralypatelCommented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
viralypatelCommented:
my bad, it mean outer join; ignore the previous post.
0
 
sdstuberCommented:
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
 
royjaydAuthor Commented:
>>>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
 
sdstuberCommented:
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
 
Devinder Singh VirdiLead Oracle DBA TeamCommented:
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
 
HainKurtSr. System AnalystCommented:
if your oracle version is less than 9, it does not support left/right joins... (+) is the way to go...
0
 
royjaydAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now