mysql IN() and LIKE together

Question

I know that I can rewrite this query

SELECT * FROM [table] WHERE 
(column1 = 'dog' AND column2 = 1) OR
(column1 = 'cat' AND column2 = 2) OR
(column1 = 'mouse' AND column3 = 3) OR
(column1 = 'wolf' AND column4 = 4);

in a better way:

SELECT  *
FROM    [table]
WHERE   (column1, column2) IN (('dog', 1), ('cat', 2), ('mouse', 3), 
('wolf', 4));

is there a similar way for this query:

SELECT * FROM [table] WHERE 
(column1 = 'dog' AND column2 like '%red%') OR
(column1 = 'cat' AND column2 like '%blue%') OR
(column1 = 'mouse' AND like '%red%') OR
(column1 = 'wolf' AND column2 like '%green%');

Because you use `[table]` with square brackets, I assume you're using Microsoft SQL Server. Please tag your question accurately, don't just tag mysql for any SQL question. Each brand of SQL database has their own extensions to the language, so the answer you need depends on which brand you use. — Bill Karwin, Dec 14 '17 at 17:49
Also possible duplicate: https://stackoverflow.com/questions/1865353/combining-like-and-in-for-sql-server — Bill Karwin, Dec 14 '17 at 17:50
Ok I removed my duplicate vote, since it's not appropriate to make it a duplicate of a SQL Server-specific solution. But the short answer is no, there's no support in the `IN()` predicate for `LIKE` pattern matching. — Bill Karwin, Dec 14 '17 at 18:36

score 0 · Answer 1 · answered Dec 14 '17 at 18:44

No. The tuples list form e.g.

(a,b) IN ( (2,3) , (5,7) , (11,13) )

is only used for equality comparisons.

But there are other ways to write the query, some of them may perform better than others, depending on availability of suitable indexes... e.g.

SELECT t1.* FROM mytable t1 WHERE t1.column1 = 'dog'   AND t1.column2 LIKE '%red%'
 UNION ALL
SELECT t2.* FROM mytable t2 WHERE t2.column1 = 'cat'   AND t2.column2 LIKE '%blue%'
 UNION ALL
SELECT t3.* FROM mytable t3 WHERE t3.column1 = 'mouse' AND t3.column2 LIKE '%red%'
 UNION ALL
SELECT t4.* FROM mytable t4 WHERE t4.column1 = 'wolf'  AND t4.column2 LIKE '%green%'

mysql IN() and LIKE together

1 Answers1