Thread: Check for an empty result
Hi Ho! Is there a way to check whether or not a subquery returns an empty result set? Googling with the following keywords does not help: postgre check "empty result set" sql check empty result Thank you. Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computingdevices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445
Eus wrote: > Hi Ho! > > Is there a way to check whether or not a subquery returns an empty result set? "EXISTS" SELECT blah FROM blah WHERE EXISTS (SELECT 1 FROM tablename WHERE ...); > postgre check "empty result set" It's not "postgre". It's PostgreSQL, or "postgres". This matters when you're searching. -- Craig Ringer
In response to Eus : > Hi Ho! > > Is there a way to check whether or not a subquery returns an empty result set? You can use EXISTS for that: -- empty result test=*# select * from (select 1 where 1=2) foo; ?column? ---------- (0 rows) -- check if a result exists test=*# select exists(select * from (select 1 where 1=2) foo); ?column? ---------- f (1 row) test=*# select exists(select * from (select 1 where 1=1) foo); ?column? ---------- t (1 row) Regards, Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net
Hi Craig! --- On Fri, 2/13/09, Craig Ringer <craig@postnewspapers.com.au> wrote: > Eus wrote: > > Hi Ho! > > > > Is there a way to check whether or not a subquery > returns an empty result set? > > "EXISTS" > > SELECT blah FROM blah WHERE EXISTS (SELECT 1 FROM tablename > WHERE ...); Thank you. Previously I tried: "... WHERE (...) IS NULL;". Of course, it didn't work. > > postgre check "empty result set" > > It's not "postgre". It's PostgreSQL, or > "postgres". This matters when > you're searching. Oh, okay. Thanks for telling me. > -- > Craig Ringer Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computingdevices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445
Hi Andreas! --- On Fri, 2/13/09, A. Kretschmer <andreas.kretschmer@schollglas.com> wrote: > In response to Eus : > > Hi Ho! > > > > Is there a way to check whether or not a subquery > returns an empty result set? > > You can use EXISTS for that: > > -- empty result > test=*# select * from (select 1 where 1=2) foo; > ?column? > ---------- > (0 rows) That's good that it can be used in FROM phrase too besides WHERE phrase. > -- check if a result exists > test=*# select exists(select * from (select 1 where 1=2) > foo); > ?column? > ---------- > f > (1 row) > > test=*# select exists(select * from (select 1 where 1=1) > foo); > ?column? > ---------- > t > (1 row) Even in SELECT phrase? That's great! Thank you for the information. > Regards, Andreas > -- > Andreas Kretschmer > Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: > -> Header) > GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA > http://wwwkeys.de.pgp.net Best regards, Eus (FSF member #4445) In this digital era, where computing technology is pervasive, your freedom depends on the software controlling those computingdevices. Join free software movement today! It is free as in freedom, not as in free beer! Join: http://www.fsf.org/jf?referrer=4445