Re: EXISTS vs IN vs OUTER JOINS

On Thu, Dec 19, 2002 at 11:02:39AM -0800, Josh Berkus wrote:
>
> Tomasz,
> You're not listening.  I said that LEFT JOIN won't work.  At all.
>
> Please re-read the paragraph above, which explains why.
I read your mail once again, but I still don't understand what are you
talking about.
I'll write example - maybe it will help us to understand each other.


I have three tables: users, things and access_list
create table users(
user_id integer primary key,
username varchar
);
insert into users(1,'Tomasz');

create table things(
thing_id int4 primary key,
thingname varchar
);
insert into things(1,'thing1');
insert into things(2,'thing2');
insert into things(3,'thing3');
insert into things(4,'thing4');
insert into things(5,'thing5');

create table access_list(
user_id int4 not null references users,
thing_id int4 not null references things
);
insert into access_list(1,1);
insert into access_list(1,4);

SELECT u.username,t.thingname,al.thing_id
from users u cross join things t
left join access_list al on (s.user_id=al.user_id and
t.thing_id=al.thing_id)

Result:
username       thingname     thing_id
Tomasz           thing1         1
Tomasz           thing2
Tomasz           thing3
Tomasz           thing4         4
Tomasz           thing5         5

Now if we add "where al.user_id is null" we get:
Tomasz           thing2
Tomasz           thing3

Or if we add "where al.user_id is not null" we get:
(the same result we have when using inner join)
Tomasz           thing1         1
Tomasz           thing4         4
Tomasz           thing5         5

I know this method will fail if we have not unique pairs in table
access_list, but in other case it looks ok.
Tomasz Myrta