PostgreSQL don't filter if results empty

Question

I'd like to know if it would be possible to get the following behavior with one query only.

Filter the results with WHERE but only apply the filter if the result isn't empty, like an optional WHERE.

For instance considering there is an users table and I want to retrieve all the users from Spain, but if there aren't any then I want all users (like the Where wouldn't be applied).

Instead of:

SELECT * FROM users WHERE country = sp

If result empty then

SELECT * FROM users

I wanted to do this behavior in one sql statement...is it possible?

Clodoaldo Neto · Accepted Answer · 2013-02-25 12:52:39Z

2

select *
from users
where
    country = sp
    or not exists (select 1 from users where country = sp)

edited Feb 25, 2013 at 12:52

answered Feb 25, 2013 at 12:46

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benjamin M Over a year ago

pretty elegant. i like that :)

Collectives™ on Stack Overflow

PostgreSQL don't filter if results empty

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related