If there are no rows, return all rows

ghz 8months ago ⋅ 105 views

2

SELECT u.id FROM user u WHERE u.id IN (7, 11)

if I have no results for the ids specified, I want to return all users, similar to this query :

SELECT u.id FROM user u

the difficulty and I want to keep the logical operator IN

to schematize my needs :

SELECT u.id FROM user u WHERE IF u.id IN (7, 11) = null THEN IN(*) ELSE IF u.id IN (7, 11) <> null THEN u.id IN (7, 11)

if possible avoid offering me a subquery, in the IN(*) part

Answers

You can achieve this requirement by using a combination of the CASE statement and the COALESCE function. Here's how you can modify your query to achieve the desired result:

SELECT u.id 
FROM user u 
WHERE u.id IN (7, 11)
   OR u.id IN (SELECT COALESCE(NULLIF(id, id), id) FROM user);

Explanation:

  • The NULLIF function compares two expressions and returns NULL if they are equal, otherwise it returns the first expression. Here, we are comparing id with itself, so it will always return NULL.
  • The COALESCE function returns the first non-null expression among its arguments. So, when NULLIF(id, id) returns NULL, COALESCE will return id.
  • In the subquery, we are selecting either NULL or id. When there are no results for the specified ids, it will return NULL.
  • By using COALESCE, if the result of the subquery is NULL, it will be replaced with id, effectively returning all users.

This way, you can achieve the desired behavior while still using the IN operator.

To achieve the desired behavior without using a subquery, you can use a combination of the COALESCE function and a conditional expression. Here's how you can modify your query:

SELECT u.id FROM user u 
WHERE COALESCE(u.id IN (7, 11), true);

In this query:

  • COALESCE(u.id IN (7, 11), true) evaluates the expression u.id IN (7, 11). If there are no results for the specified IDs (i.e., u.id is not in (7, 11)), it returns NULL. The COALESCE function replaces NULL with true, effectively returning all users.
  • If u.id IN (7, 11) returns true, it returns true, indicating that the user ID is in the specified list (7, 11), and the row is included in the result.

This approach allows you to maintain the logical operator IN and achieve the desired behavior without using a subquery.

#sql