CODEWITHSUNDEEP
mysqlin-clause
Ian2thedv
Ian2thedv

Reputation: 2701

MySQL - Check if multiple values in same IN clause

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.

Upvotes: 1

Views: 2418

Answers (3)

Daerik
Daerik

Reputation: 4277

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)

Upvotes: 1

Strawberry
Strawberry

Reputation: 33945

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

Upvotes: 0

Factory Girl
Factory Girl

Reputation: 907

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.

Upvotes: 0

Related Questions