Outer Join Problem
shar...@my-deja.com
I've got a problem with outer joins in Sybase
11.x that I don't have an answer for. I am hoping
someone may have come across a similar situation
and can provide me with some guidance. I have
attached a sample script that outlines the problem.
create table t1 (c1 int, c2 money )
go
create table t2 (c1 int, c2 money )
go
insert into t1 values ( 1, 10.0 )
insert into t1 values ( 2, 10.0 )
insert into t1 values ( 3, 30.0 )
go
insert into t2 values ( 2, 10.0 )
insert into t2 values ( 3, 10.0 )
insert into t2 values ( 4, 40.0 )
go
select c1
into un1
from t1
union
select c1
from t2
go
/*
HERE IS WHERE THE PROBLEM LIES(See Statement below).
The results should contain all rows
that have a change other then 0.
Look at row 2 in the result set.
The query indicates that table t2 has
no data (NULL) for that row, when in
fact table t2 has values of 2 and 10.00
for columns c1 and c2 respectively.
Row 4 also has the same problem.
*/
select un1.c1 un1_c1,
t1.c1 t1_c1,
t1.c2 t1_c2,
t2.c1 t2_c1,
t2.c2 t2_c2,
((t1.c2-t2.c2)/t1.c2) change
from un1, t1, t2
where un1.c1 *= t1.c1
and un1.c1 *= t2.c1
and ((t1.c2-t2.c2)/t1.c2) <> 0
go
/*
NOTE1: When you change the order of the tables
in the FROM clause the results change.
The problem remains the same but the
result rows effected shift to rows 1 and
2. Specifically look at columns t1_c1 and
t1_c2 in both rows.
NOTE2: The problem always occurs in the last
table.
*/
select un1.c1 un1_c1,
t1.c1 t1_c1,
t1.c2 t1_c2,
t2.c1 t2_c1,
t2.c2 t2_c2,
((t1.c2-t2.c2)/t1.c2) change
from un1, t2, t1
where un1.c1 *= t2.c1
and un1.c1 *= t1.c1
and ((t1.c2-t2.c2)/t1.c2) <> 0
go
/*
NOTE1: When you remove the condition that checks
for change <> 0, the query return the
correct results.
NOTE2: The order in the FROM clause no longer
matters.
*/
select un1.c1 un1_c1,
t1.c1 t1_c1,
t1.c2 t1_c2,
t2.c1 t2_c1,
t2.c2 t2_c2,
((t1.c2-t2.c2)/t1.c2) change
from un1, t1, t2
where un1.c1 *= t2.c1
and un1.c1 *= t1.c1
go
Sent via Deja.com http://www.deja.com/
Before you buy.
Bret Halford
some oddities.
I would suggest you try using the new ANSI outer join syntax that ASE
12.0 supports.
-bret
Eugenio
select c1
into un1
from t1
union all
select c1
from t2
go
For the outer join the answers are correct.
The query
select un1.c1 un1_c1,
t1.c1 t1_c1,
t1.c2 t1_c2,
t2.c1 t2_c1,
t2.c2 t2_c2,
((t1.c2-t2.c2)/t1.c2) change
from un1, t1, t2
where un1.c1 *= t1.c1
and un1.c1 *= t2.c1
and ((t1.c2-t2.c2)/t1.c2) <> 0
go
means
select all the values from table un1
and
if you find 1 row in t1 and 1 row in t2, and
the rows respect the join on c1 and the condition
((t1.c2-t2.c2/t2.c2) <>0) then
put the right values of t1.c1, t1.c2, t2.c1, t2.c2,
(t1.c2-t2.c2/t2.c2)
else
if you find 1 row in one of the 2 tables t1-t2, and
that row respects the join condition on c1, and
you aren't able to find 1 row in the other
table that respects the join on c1 and ((t1.c2-t2.c2/t2.c2) <>0)
then
put the right values of <t1 or t2>.c1, <t1 or t2>.c2
and NULL values for the other table's columns and for
(t1.c2-t2.c2/t2.c2)
else
put NULL for t1.c1, t1.c2, t2.c1, t2.c2, (t1.c2-t2.c2/t2.c2)
The second if is evaluated with respect of tables order and optimizer
choices.
If you rewrite the query with different order probably you find different
results
(in the execution path there are some other "variables" involved).
If you rewrite without the (t1.c2-t2.c2/t2.c2)<>0 condition the query means
select all the values from table un1
and
if you find 1 row in t1 and 1 row in t2, and
the rows respect the join on c1 then
put the right values of t1.c1, t1.c2, t2.c1, t2.c2,
(t1.c2-t2.c2/t2.c2)
else
if you find 1 row in one of the 2 tables t1-t2, and
that row respects the join condition on c1, and
you aren't able to find 1 row in the other
table that respects the join on c1 then
put the right values of <t1 or t2>.c1, <t1 or t2>.c2
and NULL values for the other table's columns and for
(t1.c2-t2.c2/t2.c2)
else
put NULL for t1.c1, t1.c2, t2.c1, t2.c2, (t1.c2-t2.c2/t2.c2)
For the rows with c1=2,
in the first case you pass through the 2nd if, in the second case you don't.
I hope this helps. It's the maximum for my english !!!!
--
be happy
Eugenio
remove _nospam from reply address
Opinions are mine and do not necessarily reflect those of my company
=======================================================
shar...@my-deja.com wrote in message <8it0gq$uei$1...@nnrp1.deja.com>...
Eugenio
2nd try... :)))
-------------------
Joe Celko
>> I've got a problem with outer joins in Sybase 11.x that I don't have
an answer for. <<
Here is how OUTER JOINs work in SQL-92. Assume you are given:
Table1 Table2
a b a c
====== ======
1 w 1 r
2 x 2 s
3 y 3 t
4 z
and the outer join expression:
Table1
LEFT OUTER JOIN
Table2
ON Table1.a = Table2.a <== join condition
AND Table2.c = 't'; <== single table condition
We call Table1 the "preserved table" and Table2 the "unpreserved table"
in the query. What I am going to give you is a little different, but
equivalent to the ANSI/ISO standards.
1) We build the CROSS JOIN of the two tables. Scan each row in the
result set.
2) If the predicate tests TRUE for that row, then you keep it. You also
remove all rows derived from it from the CROSS JOIN
3) If the predicate tests FALSE or UNKNOWN for that row, then keep the
columns from the preserved table, convert all the columns from the
unpreserved table to NULLs and remove the duplicates.
So let us execute this by hand:
Let @ = passed the first predicate
Let * = passed the second predicate
Table1 CROSS JOIN Table2
a b a c
=========================
1 w 1 r @
1 w 2 s
1 w 3 t *
2 x 1 r
2 x 2 s @
2 x 3 t *
3 y 1 r
3 y 2 s
3 y 3 t @* <== the TRUE set
4 z 1 r
4 z 2 s
4 z 3 t *
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
3 y 3 t <= only TRUE row
-----------------------
1 w NULL NULL Sets of duplicates
1 w NULL NULL
1 w NULL NULL
-----------------------
2 x NULL NULL
2 x NULL NULL
2 x NULL NULL
3 y NULL NULL <== derived from the TRUE set - Remove
3 y NULL NULL
-----------------------
4 z NULL NULL
4 z NULL NULL
4 z NULL NULL
the final results:
Table1 LEFT OUTER JOIN Table2
a b a c
=========================
1 w NULL NULL
2 x NULL NULL
3 y 3 t
4 z NULL NULL
The basic rule is that every row in the preserved table is represented
in the results in at least one result row.
There are limitations and very serious problems with the extended
equality version of an outer join used in some diseased mutant
products. Consider the two Chris Date tables
Suppliers SupParts
supno supno partno qty
========= ==============
S1 S1 P1 100
S2 S1 P2 250
S3 S2 P1 100
S2 P2 250
and let's do an extended equality outer join like this:
SELECT *
FROM Supplier, SupParts
WHERE Supplier.supno *= SupParts.supno
AND qty < 200;
If I do the outer first, I get:
Suppliers LOJ SupParts
supno supno partno qty
=======================
S1 S1 P1 100
S1 S1 P2 250
S2 S2 P1 100
S2 S2 P2 250
S3 NULL NULL NULL
Then I apply the (qty < 200) predicate and get
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
Doing it in the opposite order
Suppliers LOJ SupParts
supno supno partno qty
===================
S1 S1 P1 100
S2 S2 P1 100
S3 NULL NULL NULL
Sybase does it one way, Oracle does it the other and Centura (nee
Gupta) lets you pick which one -- the worst of both non-standard
worlds! In SQL-92, you have a choice and can force the order of
execution. Either do the predicates after the join ...
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
WHERE qty < 200;
... or do it in the joining:
SELECT *
FROM Supplier
LEFT OUTER JOIN
SupParts
ON Supplier.supno = SupParts.supno
AND qty < 200;
Another problem is that you cannot show the same table as preserved and
unpreserved in the extended equality version, but it is easy in SQL-
92. For example to find the students who have taken Math 101 and might
have taken Math 102:
SELECT C1.student, C1.math, C2.math
FROM (SELECT * FROM Courses WHERE math = 101) AS C1
LEFT OUTER JOIN
(SELECT * FROM Courses WHERE math = 102) AS C2
ON C1.student = C2.student;
--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc)
which can be cut and pasted into Query Analyzer is appreciated.