Question 373418 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – If there are no rows, return all rows

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

Tags:

2

Answers


  1. 0

    In SQL Server you can actually use TOP (1) WITH TIES to do this very efficiently.

    SELECT TOP (1) WITH TIES
  *
FROM (
    SELECT u.id, 1 AS Ordering
    FROM user u
    WHERE u.id IN (7, 11)

    UNION ALL

    SELECT u.id, 2
    FROM user u
) u
ORDER BY
  Ordering;

    But for MySQL you will need something like this

    WITH cte AS (
    SELECT u.id
    FROM user u
    WHERE u.id IN (7, 11)
)
SELECT *
FROM cte

UNION ALL

SELECT *
FROM user u
WHERE NOT EXISTS (SELECT 1 FROM cte);

    Another option is to use window functions

    SELECT u.id
FROM (
    SELECT
      u.id,
      SUM(u.id IN (7, 11)) OVER () AS countMatches
    FROM user u
) u
WHERE countMatches = 0 OR u.id IN (7, 11);
    Login or Signup to reply.
  2. 0 
    SELECT u.id
FROM user u
WHERE u.id IN (7, 11)
   OR NOT EXISTS ( 
     SELECT u.id 
     FROM user u
     WHERE u.id IN (7, 11)
     );

    https://dbfiddle.uk/_yzWanfN

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search