Skip site navigation (1) Skip section navigation (2)

Re: EXISTS vs IN vs OUTER JOINS

From: jasiek(at)klaster(dot)net
To: Josh Berkus <josh(at)agliodbs(dot)com>
Cc: jasiek(at)klaster(dot)net,PgSQL Performance ML <pgsql-performance(at)postgresql(dot)org>
Subject: Re: EXISTS vs IN vs OUTER JOINS
Date: 2002-12-19 19:28:30
Message-ID: 20021219192830.GA8336@serwer (view raw or flat)
Thread:
Lists: 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

In response to

Responses

pgsql-performance by date

Next:From: Ron JohnsonDate: 2002-12-19 19:36:36
Subject: Re: 4G row table?
Previous:From: Charles H. WoloszynskiDate: 2002-12-19 19:27:25
Subject: Re: 4G row table?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group