MySQL "ANY IN" query?

Question

Is there any short way to do something like this in MySQL:

WHERE s.loc1 IN ("A", "B", "C", "D") 
   OR s.loc2 IN ("A", "B", "C", "D") 
   OR s.loc3 IN ("A", "B", "C", "D");

without repeating the ("A", "B", "C", "D") bit (and preferably in such a way that this list is still suppliable via @param)? Sadly, the syntax of (s.loc1, s.loc2, s.loc3) IN (blah) only seems to work for AND's instead of OR's.

The ideal solution would look something like:

WHERE (s.loc1, s.loc2, s.loc3) ANY IN ("A", "B", "C", "D");

OR's should be working... Can you provide Minimally Complete and Verifable Example (MVCE) which shows the OR's not working and indicate what version of mySQL this is? Just a sample create table with loc1,2,3 and sample data, Perhaps you need to be using apostrophe instead of quote? — xQbert, Aug 31 '18 at 18:39
Short answer: there's no syntax for merging your three "in/or" clauses into a single , shoter clause. — paulsm4, Aug 31 '18 at 18:40
Oh I get it now.... eliminate the repetition of the in. Well you could but I think the maintenance is cleaner your way as it's easy to see what you want. `SELECT Distinct s.* FROM SO52121085 s CROSS JOIN (SELECT "A" x UNION ALL SELECT "B" x UNION ALL SELECT "C" x UNION ALL SELECT "D" x) Val WHERE s.loc1 = x or s.loc2 = x or S.LOC3 = x;` [DEMO](http://rextester.com/RDRR17263) now if A, B, C, D are in a table we can eliminate the unions and simply select where. — xQbert, Aug 31 '18 at 18:52
What does "like this" mean? Something involving not repeating expressions? — philipxy, Aug 31 '18 at 20:16

John Cummings · Answer 1 · 2018-08-31T19:29:19.123

You could query from a table generated (that is, a table subquery or a derived table) from a union of the values:

select
    *
from (
    (select loc1 as loc, s.* from s)
    union
    (select loc2 as loc, s.* from s)
    union
    (select loc3 as loc, s.* from s)
) t
where
    t.loc in ("A","B","C","D");

Running the above query yields:

+-----+------+------+------+
| loc | loc1 | loc2 | loc3 |
+-----+------+------+------+
| A   | A    | 2    | 3    |
| D   | D    | 2    | 3    |
| B   | 1    | B    | 3    |
| C   | 1    | 2    | C    |
+-----+------+------+------+

when run on this made up data:

drop table if exists s;
create table s (
    loc1 varchar(255) not null,
    loc2 varchar(255) not null,
    loc3 varchar(255) not null
);
insert into s values
("1","2","3"),
("A","2","3"),
("1","B","3"),
("1","2","C"),
("D","2","3");

MySQL "ANY IN" query?

1 Answers1