Re: EXISTS vs IN vs OUTER JOINS - Mailing list pgsql-performance

From jasiek@klaster.net
Subject Re: EXISTS vs IN vs OUTER JOINS
Date
Msg-id 20021219192830.GA8336@serwer
Whole thread Raw
In response to Re: EXISTS vs IN vs OUTER JOINS  (Josh Berkus <josh@agliodbs.com>)
Responses Re: EXISTS vs IN vs OUTER JOINS
List pgsql-performance
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

pgsql-performance by date:

Previous
From: Ron Johnson
Date:
Subject: Re: 4G row table?
Next
From: Tom Lane
Date:
Subject: Re: EXISTS vs IN vs OUTER JOINS