On Mon, Dec 8, 2008 at 7:06 PM, Chris <dmagick@xxxxxxxxx> wrote: > > Right.... >> >> So, how are these different: >> >> SELECT * FROM t1 WHERE id EXISTS (SELECT id FROM t2) >> > > If there are *any* results for the subselect, the exists returns true. > > It's the equivalent of: > > select * from t1 where id is true; > > ie > > select * from t1; > > If there are no results for the subselect, the exists returns false, ie: > > select * from t1 where false; > > which will return nothing. > > to >> SELECT * FROM t1 WHERE id IN (SELECT id FROM t2) >> > > this returns specific id's that match. > > According to my understanding of the documentation, these would have the >> same result. >> > > No, they aren't. > > create table t1(id int, name varchar(5)); > > insert into t1(id, name) values (1, 'one'); > insert into t1(id, name) values (2, 'two'); > insert into t1(id, name) values (3, 'three'); > insert into t1(id, name) values (4, 'four'); > insert into t1(id, name) values (5, 'five'); > > create table t2(id int, other_name varchar(5)); > > insert into t2(id, other_name) values (1, 'one'); > insert into t2(id, other_name) values (2, 'two'); > > this returns everything from t1: > SELECT * FROM t1 WHERE EXISTS (SELECT id FROM t2); > > this returns 2 rows that match: > SELECT * FROM t1 WHERE id IN (SELECT id FROM t2); Oh OK. Thanks for clearing that up. > > > -- > Postgresql & php tutorials > http://www.designmagick.com/ > > -- Tim-Hinnerk Heuer http://www.ihostnz.com -- Web Design, Hosting and free Linux Support
