Search code examples
sqlpostgresqlquery-optimizationconditional-statements

PostgreSQL don't filter if results empty


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?


Solution

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