What does the (+) do in an Oracle Query? 2

[Nunina]

Hi guys!

What does the (+) do in a query? Where does it go? Before or after the criteria?

Thanks!!!

Nunina

 

[jimbopalmer]

if we have the query

Select a.spose, b.child from spose_table a, child_table b where a.ssn = b.ssn

we would get rows of folks who are married and have a child

Select a.spouse, b.child from spouse_table a, child_table b where a.ssn (+) = b.ssn

would get us folks with children, and list their spouse, if any

Select a.spouse, b.child from spouse_table a, child_table b where a.ssn = b.ssn (+)

would list folks spouse, and also their child, if any


(in real life, both spouses and children can be plural, but this is just an example)

I tried to remain child-like, all I acheived was childish.

 

[SantaMufasa]

Nunia,

Let's assume you have two tables, EMP and DEPT. Here are their respective data (Note that there are departments with no employees):

SQL> select last_name||', '||first_name emp,dept_id
  2  from emp;

Employee
Name                              DEPT_ID
------------------------------ ----------
Velasquez, Carmen                      50
Ngao, LaDoris                          41
Nagayama, Midori                       31
Quick-To-See, Mark                     10
Ropeburn, Audry                        50
Urguhart, Molly                        41
Menchu, Roberta                        42
SQL> select id,name from dept;

        ID NAME
---------- -------------------------
        10 Finance
        31 Sales
        32 Sales
        33 Sales
        34 Sales
        35 Sales
        41 Operations
        42 Operations
        43 Operations
        44 Operations
        45 Operations
        50 Administration
        51 Information Tech

Next, assume we wish to print a DEPARTMENT report that shows ALL Departments and their Employees (including Departments that have no Employees). Here is a standard query to produce a department report:

SQL> select d.id,name, decode(last_name,null,null,last_name||', '||first_name) Emp
  2  from emp e, dept d
  3  where e.dept_id = d.id
  4  /

                                     Employee
        ID NAME                      Name
---------- ------------------------- ------------------------------
        10 Finance                   Quick-To-See, Mark
        31 Sales                     Nagayama, Midori
        41 Operations                Ngao, LaDoris
        41 Operations                Urguhart, Molly
        42 Operations                Menchu, Roberta
        50 Administration            Velasquez, Carmen
        50 Administration            Ropeburn, Audry

Notice we see only rows "where dept_id = d.id". Since there are departments with no matching employees, we do not see those "empty" departments.

To get those rows to print out, we must tell Oracle that we have REAL departments for which we need GHOST employees. And what is the best symbol to represent ghosts? How about a tombstone with a cross on it -- "(+)". This symbol tells Oracle to "manufacture" a ghost employee wherever there is a department with no matching employees:

SQL> select d.id,name, decode(last_name,null,null,last_name||', '||first_name) Emp
  2  from emp e, dept d
  3  where e.dept_id(+) = d.id
  4  /

                                     Employee
        ID NAME                      Name
---------- ------------------------- ------------------------------
        10 Finance                   Quick-To-See, Mark
        31 Sales                     Nagayama, Midori
        32 Sales
        33 Sales
        34 Sales
        35 Sales
        41 Operations                Ngao, LaDoris
        41 Operations                Urguhart, Molly
        42 Operations                Menchu, Roberta
        43 Operations
        44 Operations
        45 Operations
        50 Administration            Velasquez, Carmen
        50 Administration            Ropeburn, Audry
        51 Information Tech
SQL>

Voila, the "(+)" tells Oracle to create ghost rows for departments that would otherwise be LEFT OUT of the query; so this is a "Join for Left-out rows"; a "Left-out join"; a "Left-outer join". This symbol, then, invokes Oracle's "Left-outer join" or "Outer join" capability.

Any questions?

Dave

 

[SantaMufasa]

Nunina,

Sorry I forgot to specifically answer your last two questions: "Where does it [(+)] go? Before or after the criteria?"

To always safely answer this question, I ask myself, "Which table has the full set of rows I want printed, and which table needs "ghost" records to match the full-set rows?" Whichever table needs "ghost" rows is the one that needs the (+) symbol in the WHERE statement behind the column(s) that you use to specify the equality with the repective column(s) from the full-set table.

Basically, put the (+) where you want the "ghost" rows.

Dave

 

[dsanchez2]

All the above is correct.

The (+) syntax implements an "Outer Join".

Oracle 9 implements the ANSI syntax for Outer Joins. So instead of writing:

SELECT d.department_id, e.last_name
FROM departments d, employees e
WHERE d.department_id = e.department_id(+)
ORDER BY d.department_id;

You would write:

SELECT d.department_id, e.last_name
FROM departments d LEFT OUTER JOIN employees e
ON d.department_id = e.department_id
ORDER BY d.department_id;

 

[Nunina]

Thanks a lot guys! Now I understand this better. I was doing queries without the (+), and I always got either more data than needed or less data than needed.

Now that I'm developing in Cognos, and reading to an Oracle Database, I need to be really specific about the information I want to show my users. You are lifesavers!

Thanks again!

Nunina