AW: Joins in 3.5

[Ivan Smahin]

Hello Fabian,

Monday, December 10, 2007, 8:35:13 PM, you wrote:


FE> ----- all right - returns 92 rows. Now the other left outer join.

So - 92 rows from "addresses left outer join address_categories".
(addresses contains at least 92 rows.)

FE> select ...
FE> from addresses
FE> LEFT OUTER JOIN address_categories on addresses.categoryid = address_categories.id
It might be exactly 92 rows here - right?

FE> LEFT OUTER JOIN communication AS commfon ON commfon.addressid = addresses.id
And it must be 92 or more rows here.

FE> AND commfon.type = 'fon'
This  condition is treated as filter which must be applied before
joining tables!

FE> where  addresses.categoryid = '200712101451563674321'
This condition is applied against join result.


FE> ------ returns only 55 rows. These are exactly the 55 rows that have a
FE> corresponding entry in table communication. But I think a left outer join
FE> should return all rows with NULL values for those, that have no
FE> corresponding entry in table communication.


create table t1 ( f1 long );
create table t2 ( f1 long, f2 long );
create table t3 ( f2 long, f3 long );

insert into t1 values (1);
insert into t1 values (2);

insert into t2 values (1, 1);
insert into t2 values (3, 1);

insert into t3 values (1, 10);
insert into t3 values (1, 20);


select t1.RecID, t2.RecID, t3.RecID
from
    t1 LEFT OUTER JOIN t2 ON t1.f1 = t2.f1
    LEFT OUTER JOIN t3 ON t2.f2 = t3.f2

t1.RecID      t2.RecID    t3.RecID
----------------------------------
1             1           1
1             1           2
2             NULL        NULL

Agree?

-------------------------
Now add filter condition:
-------------------------

-- Filtered condition (Before-join)
select t1.RecID, t2.RecID, t3.RecID
from
    t1 LEFT OUTER JOIN t2 ON t1.f1 = t2.f1
    LEFT OUTER JOIN t3 ON t2.f2 = t3.f2
    AND t3.f3 = 20
    
t1.RecID      t2.RecID    t3.RecID
----------------------------------
1             1           2
2             NULL        NULL


-------------------------
Move condition to where clause:
-------------------------

-- Condition in WHERE (Post-join)
select t1.RecID, t2.RecID, t3.RecID
from
    t1 LEFT OUTER JOIN t2 ON t1.f1 = t2.f1
    LEFT OUTER JOIN t3 ON t2.f2 = t3.f2
WHERE
     t3.f3 = 20
    
t1.RecID      t2.RecID    t3.RecID
----------------------------------
1             1           2


You  can  be  "impressed".
But  it  is  not so weird if you start
thinking of not only left/right but full outer joins also.


BTW, I have checked it against MS-SQL.


-- 
Best regards,
Ivan Smahin 
Senior Software Engineer
Paradigma Software, Inc
Valentina - The Ultra-Fast Database
http://www.valentina-db.com