Check for an empty result

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 computing devices.

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 computing devices.

Join free software movement today! It is free as in freedom, not as in free beer!

Join: http://www.fsf.org/jf?referrer=4445