MySQL - Check if multiple values in same IN clause

Question

Is there a way to check if multiple values exist in the same in clause, without rewriting the same IN clause?

Example

Employee Table:
+----+--------------+---------------+-------------+
| id |  first_name  |  middle_name  |  last_name  |
+----+--------------+---------------+-------------+
| 1  |     Ian      |    Daniel     | de Villiers |
| 2  |     Karien   |               | Tolmie      |
| 3  |     John     |    Peter      | Green       |
| 4  |     Daniel   |    Silie      | von Guns    |
| 5  |     Francois |    Roos       | Krans       |
+----+--------------+---------------+-------------+

Say I want all employees whos first, middle or last name is either Daniel or Peter - thus, I want ids 1, 3 and 4.

The only way I know how to do this is:

SELECT id 
FROM   employees 
WHERE  ( first_name IN ( "Daniel", "Peter" ) ) 
        OR ( middle_name IN ( "Daniel", "Peter" ) ) 
        OR ( last_name IN ( "Daniel", "Peter" ) )

This can be come quite long if I have multiple values to test or have excessive amounts of values in the IN clause.

I have tried

SELECT id 
FROM   employees 
WHERE  ( first_name OR middle_name OR last_name IN ( "Daniel", "Peter" ) )

but I think that will cause first_name to be interpreted as a boolean value.

You can turn it around where 'Daniel' in () or 'Peter' in () — juergen d
– juergen d, Commented Apr 21, 2016 at 9:30
@lan2thedv how about concatenating first_name, middle_name and last_name into fullname and then query for substrings on fullname, this would simplify the query? — Imran Ali
– Imran Ali, Commented Apr 21, 2016 at 9:38
@juergend yes, but will still require two in clauses, which is what I am trying to avoid — Ian2thedv
– Ian2thedv, Commented Apr 21, 2016 at 10:33
@ImranAli for simple cases this will work, but not for checking if daniel is contained in ('dan', 'ielle', 'green') as the concatenation will be daniellegreen, resulting in a false positive — Ian2thedv
– Ian2thedv, Commented Apr 21, 2016 at 10:35

Daerik · Accepted Answer · 2022-04-26 22:19:00Z

Starting with MySQL 8.0, you can use JSON_OVERLAPS() to compare two JSON documents created with JSON_ARRAY(). Doing so will return true if the two documents have any key-value pairs or array elements in common.

If both arguments are scalars, the function performs a simple equality test.

So your referenced code would look something similar to:

SELECT
    `id` 
FROM
    `employees` 
WHERE
    JSON_OVERLAPS (
        JSON_ARRAY( "Daniel", "Peter" ),
        JSON_ARRAY( `first_name`, `middle_name`, `last_name` )
    );

Specifically it states that when comparing two arrays, JSON_OVERLAPS() returns true if they share one or more array elements in common, and false if they do not:

mysql> SELECT JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Ian", "Daniel", "de Villiers"));
+---------------------------------------+
| JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Ian", "Daniel", "de Villiers")) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("John", "Peter", "Green"));
+---------------------------------------+
| JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("John", "Peter", "Green")) |
+---------------------------------------+
|                                     1 |
+---------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Francois", "Roos", "Krans"));
+---------------------------------------+
| JSON_OVERLAPS(JSON_ARRAY("Daniel", "Peter"), JSON_ARRAY("Francois", "Roos", "Krans")) |
+---------------------------------------+
|                                     0 |
+---------------------------------------+
1 row in set (0.00 sec)

Factory Girl · Accepted Answer · 2016-04-21 09:47:14Z

0

As i now it is no other way to execute it more fast. Anyways in worst case you have to compare each of first, middle and last names with your array of names.

You can also concatenate first, middle and last names as recommended Imran Ali, but.. 1. it is not safe. You can have match when, for example, end of first name and beginning of middle name is making some of your names. 2. it will execute longer. When you check equality, system breaks comparing chars after first non-equal, but when you check entrance, system must compare all following chars too.

answered Apr 21, 2016 at 9:47

Factory Girl

9074 gold badges18 silver badges29 bronze badges

Comments

Strawberry · Accepted Answer · 2016-04-21 10:56:53Z

0

SELECT * 
  FROM employee 
 WHERE 1 IN('daniel' IN (first_name,middle_name,last_name),'peter' IN (first_name,middle_name,last_name));

answered Apr 21, 2016 at 10:56

Strawberry

34k14 gold badges43 silver badges57 bronze badges

1 Comment

Phiter Over a year ago

Hey, @Strawberry. You have almost 16k reputation and your answer was tagged as low quality. You should know that it is a good thing to provide extra information on your answers, instead of a simple code :)

Collectives™ on Stack Overflow

MySQL - Check if multiple values in same IN clause

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related