MySQL WHERE Comparing Multiple Values IN Multple Values

Question

Is there a way to achieve this in MySQL (using pseudocode):

 SELECT .... WHERE (1,4,3,6) IN (SELECT id FROM atable);

whereby subquery "SELECT ... atable" returns row ids 3,9,8,1

The desired effect is to do something where the 2 sets of field overlap i.e. 3 and 1 in the above example.

Reason? I have a need to compare (from PHP) a field with comma separated values and perform an action where the records are applicable. However the option of doing this in PHP is not possible in my use case for some other reason too convoluted to explain here.

I've been searching on the 'net but not stumbled on anything like it.

That syntax is a "row constructor", which is not what you need. — Rick James
– Rick James, Commented Jun 17, 2021 at 18:36

200_success · Accepted Answer · 2015-08-15 12:28:10Z

2

I think you want

SELECT id FROM atable WHERE id IN (1,4,3,6);

If the id doesn't exist in atable, it's not going to show up in the results. If an id is not among the (1,4,3,6) list, it's also not going to show up. What you get then is the intersection.

answered Aug 15, 2015 at 12:28

200_success

7,5901 gold badge45 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL WHERE Comparing Multiple Values IN Multple Values

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related