Is there a way to get MySQL 5.7/MariaDB to return 0 or 1 for rows that exist in a where IN() query

user_13611099 0
Viewed 182

Is there a way to get MySQL 5.7/MariaDB to return 0 or 1 for rows that exist in a where IN() query?

like maybe SUM/IF/complex subquery SQL trickery? It could be the id or just a simple 1/0 to know which exists or not.

I thought if I could just add IFNULL(id,0) to a empty row it would return 4 results with 0, but it only shows 3:

Imagine a simple id table: 


SELECT * FROM tbl;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+
3 rows in set (0.00 sec)

EXAMPLE QUERY:


SELECT IFNULL(id,0) FROM tbl WHERE id in (1,2,3,4);

EXPECTED RESULTS


+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  0 |
------

4 results

ACTUAL


+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
+----+

3 rows in set (0.01 sec)
--------

Basically what I'm trying to do is to return both the existing id and empty ids and not use [temporary] tables, it could be a complex subquery using IN() or FIND_IN_SET or combination of where IN() and EXISTS or NOT EXISTS, etc, etceven a stored procedure, or possibly JSON trickery, etc.

mariadb mysql
2 Answers

Your IN criteria needs to exist as rows for the result to be rows. So:

SELECT coalesce(id, 0)
FROM
  (SELECT 1 AS e
   UNION SELECT 2
   UNION SELECT 3
   UNION SELECT 4) t2
LEFT JOIN tbl ON id=e

fiddle

If its always a sequence, mariadb's in-build sequence engine creates magic tables of sequences:

SELECT coalesce(id, 0)
FROM seq_1_to_4 t2
LEFT JOIN tbl ON id=seq

fiddle

user_101951530

If the ids are actually all some other table, select from that and left join tbl. If not, emulate such a table:

select in_ids.id, tbl.id is not null as id_exists
from (select 0 id union all select 1 union all select 2 union all select 3) in_ids
left join tbl on tbl.id=in_ids.id

If there is some rule (all integers in a range, for instance) in MySQL 8 or mariadb 10.2, you would use a recursive common table expression instead:

with recursive in_ids as (
    select 1 as id
    union all
    select id+1 from in_ids
    where id < 4
)
select in_ids.id, tbl.id is not null as id_exists
from in_ids
left join tbl on tbl.id=in_ids.id

You can also generate a sequence large enough to cover your possible range and then keep your IN to only look at particular values (here, from 1 to 1024):

select sequence.n, tbl.id is not null as id_exists
from (
    select 1+i+4*(j+4*(k+4*(l+4*m))) n
    from (select 0 i union all select 1 union all select 2 union all select 3) i
    cross join (select 0 j union all select 1 union all select 2 union all select 3) j
    cross join (select 0 k union all select 1 union all select 2 union all select 3) k
    cross join (select 0 l union all select 1 union all select 2 union all select 3) l
    cross join (select 0 m union all select 1 union all select 2 union all select 3) m
) sequence
left join tbl on tbl.id=sequence.n
where sequence.n in (1,2,3,4);

or with CTEs:

with recursive sequence as (
    select 1 as n
    union all
    select n+1 from sequence
    where n < 1024
)
left join tbl on tbl.id=sequence.n
where sequence.n in (1,2,3,4);
ysth_173890
Related