out join in query

[huchen]

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

 

[SantaMufasa]

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:

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:

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:

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

   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:

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

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?




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

 

[huchen]

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.

 

[SantaMufasa]

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.

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