×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

out join in query

out join in query

out join in query

(OP)
Hello,

Can you please help me understand this out join?

Here are 3 tables with columns:

t1(key1, c2, c3, exp_date)--this table has 2M rows
t2(key2, c2,c3,exp_date)--this table has 0.8M rows
t3(key1,key2, c3, c4, exp_date) -- (key1,key2) is primary key, has a little less row than t2

an query looks like this:

select t1.* from t1,t2,t3
where t2.key2(+) = NVL(t3.key2, 999999999) AND
t3.exp_date(+) is null;

Can you help me understand:
1   what does this do?
where t2.key2(+) = NVL(t3.key2, 999999999)

2 this line:
t3.exp_date(+) is null
when I take (+) away, I get different rownum.
what does (+) here do?

Thank you

RE: out join in query

Huchen,

Here are some important characteristics about JOINS:

On a standard join (i.e., non-OUTER join), the only rows that result are the ones where all of the WHERE clauses evaluate to TRUE.

To illustrate, here are two tables:

CODE

select * from z1;

COL1
----
   1
   2
   3
   4

select * from z2;

COL1
----
   1
   3
Here is a query that displays col1 from each of the two tables with a standard query:

CODE

select z1.col1 "Z1:col1",z2.col1 "Z1:col1"
from z1,z2
where z1.col1 = z2.col1;

   Z1:col1    Z1:col1
---------- ----------
         1          1
         3          3
Notice that rows from z1 with col1 values "2" and "4" did not display because there are no partner values in z2's col1.

Now, if you want to see all values from z1.col1, despite no matches in z2.col1, then we must change the rules:

Think of table JOINS as a "dance". The rules of the dance are that anyone admitted to the dance must come in with a partner. In the case above, z1.col1's "1" value partners with z2.col1's "1" value, so the pair is "admitted" to the dance.

z1.col1's "2" and "4" values, however, are "stag" (i.e., they have no matching partner with any z2.col1 values). Therefore, for them to be able to attend "The Dance", we must tell the bouncer at the door that "ghost" partners are legal. That is, if z1.col1 arrives at the door with no "real" partner (e.g. "2" and "4"), then they may "pair up" with a "ghost" partner to allow them to legally enter the dance.

The way that we specify that "ghost" partners are legal is with the symbol for a "ghost's headstone"...Remember in the cartoon show, "Casper, the Friendly Ghost"...at the beginning of the show, Casper rose up from underground beneath a marker that was curved at the top with a cross on it? Well, we use the same "headstone symbol", "(+)", to authorise the bouncer to allow "ghost partners" for the "real values" that exist in z1.col1:

CODE

select z1.col1 "Z1:col1",z2.col1 "Z2:col1"
from z1,z2
where z1.col1 = z2.col1(+);

   Z1:col1    Z2:col1
---------- ----------
         1          1
         2
         3          3
         4
Notice this time that z1.col1's "unpartnered" values, "2" and "4", are allowed to attend "The Dance" since they arrived with "Casper", the friendly ghost value. For the rows "2" and "4", the "value" for Z2.col1 is NULL.

Now for the issue:

CODE

where t2.key2(+) = NVL(t3.key2, 999999999)
Since t3.key2 can possibly be NULL, and since we want all t3 rows to display, the code specifies that if t3.key2 is NULL, then use '999999999' as its value instead. Then, if t2.key2 has no match to '999999999', display t3's row anyway.

The code

CODE

t3.exp_date(+) is null
means that the code wants to look at the "Casper" records, i.e., those rows where t3.exp_date is a generated NULL by virtue of the "(+)" invocation.

Did all of this make enough sense to give you the answers for which you are looking?


 

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

RE: out join in query

(OP)
Thank you Santa.
I should tell you that table t3 only has 3 columns:

t1(key1, c2, exp_date)--this table has 2M rows
t2(key2, c2,exp_date)--this table has 0.8M rows
t3(key1,key2, exp_date) -- (key1,key2) is primary key, foreign key on t1 and t2.

T3 is just to relate t1 and t2

I understand your "Casper, the Friendly Ghost" sample, As a matter of fact, I have the movie at home.
But I still do not understand my case. Can you create a case to explain this?

select t1.* from t1,t2,t3
where t2.key2(+) = NVL(t3.key2, 999999999) AND
t3.exp_date(+) is null;

Thank you.

RE: out join in query

Huchen,

Your code, above, frankly looks a bit like "SQL Double-Talk":

a) There is no WHERE-join against your "t1" table.
b) I would expect the code, "t3.exp_date(+) is null", to appear (only) if table "t3" was outer joined to one of the other two tables (instead of to NULL)
c) If, as you said above, "t3.key2" is a foreign key to "t2.key2" (a PRIMARY KEY component that should never be NULL), then "t3.key2" should not be NULL, so I cannot justify the "NVL(t3.key2, 999999999)"

So, although I can explain the syntax of your code, above, I cannot justify the logic.

santaMufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close