CODEWITHSUNDEEP
mysqlsql
Zimano
Zimano

Reputation: 2299

How do I use the IN SQL operator when I have a set of values to check for of which any match will suffice?

I am running the following query where both tables consist of one integer column:

INSERT INTO otherNumbers(number)
VALUES(
    CASE
        WHEN (2202 in (select * from numbers)) THEN 2
        WHEN (2201 in (select * from numbers)) THEN 1
        ELSE 0
    END
);

I am preparing the above SQL query for a MySQL server instance and as part of that query I want to check if a certain value is contained within a set. I have achieved that with the following syntax:

2202 IN (SELECT * FROM numbers)

However, I also have a case where any match of a set of values suffices. In pseudo-SQL, I'd like to do something like this: (2202, 2203, 2204) ANY IN (SELECT * FROM numbers) which obviously will not work.

Due to the nature of the data, I want to avoid a logic explosion in the form of:

2202 IN (SELECT * FROM numbers) OR 2203 IN (SELECT * FROM numbers) OR 2204 IN (SELECT * FROM numbers)

Since the terminology regarding my question is pretty general (the keyword is IN), I could not find any questions describing my problem, even though I believe there to be.

What is the best practice way of achieving my desired result? One approach I can think of using an inner join on both sets and see if the rows returned are bigger than 0, but I can't think of a way to express this.

Upvotes: 0

Views: 171

Answers (5)

GMB
GMB

Reputation: 222572

You could use exists conditions, like so:

insert into othernumbers(number)
values(
    case
        when exists (
            select 1
            from numbers
            where num in (2202, 2203, 2204)
        ) then 2
        when exists (
            select 1
            from numbers
            where num = 2201
        ) then 1
        else 0
    end
);

Upvotes: 0

Ralph Ritoch
Ralph Ritoch

Reputation: 3440

... WHERE EXISTS (SELECT * as n FROM numbers HAVING n IN (2202, 2203, 2204))

Upvotes: 0

Z .
Z .

Reputation: 12837

Create a temporary table with all the values you need (2202, 2203, 2204, ...). Join to the other table.

Upvotes: 1

dkasipovic
dkasipovic

Reputation: 6120

Can you use FIND_IN_SET and a variable ?

Something like:

SET @user_ids = (SELECT GROUP_CONCAT(`id`) FROM `user` WHERE `user_type` = 'administrator');
SELECT * FROM `user` WHERE FIND_IN_SET(`id`, @user_ids);

https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_find-in-set

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133380

A In clause can be refactored as an inner join 

select  * 
from ( 
  select  2202 my_num
  UNION 
  select  2203 
  UNION 
  select  2204
) t1  
INNER JOIN  (
  select my_col 
  from my_table 
) t2 on t1.my_num = t2.my_col

Upvotes: 1

Related Questions