Mysql Where ... In ... And where ... in ... should match only one index

Question

I have a request:

SELECT * FROM `users` WHERE (`firstname` LIKE 'Luke' AND `lastname` LIKE 'Skywalker') OR (`firstname` LIKE 'Foo' AND `lastname` LIKE 'Bar') OR (`firstname` LIKE 'Tom' AND `lastname` LIKE 'Turner'); 

But I would like to make it more readable by using ... I tried

 SELECT * FROM users WHERE `firstname` IN ('Luke','Foo','Tom') AND `lastname` IN ('Skywalker','Bar','Turner'); 

But, unfortunately, this will also match "Tom Skywalker" , "Foo Turner" and all the mixes you can come up with.

I need to select the first and last name (possibly more fields, such as DOB), since I get data from an external API, and I need to check if these names are on our system.

Answer 1

 SELECT * FROM users WHERE (firstname, lastname) IN ( ('Luke', 'Skywalker') , ('Foo' , 'Bar') , ('Tom' , 'Turner') ) ; 

Answer 2

Using the LIKE operator without wildcards does not make much sense. I think you should use = , especially if you need to check if those names are in our system .

 SELECT * FROM users WHERE (firstname = 'Luke' AND lastname = 'Skywalker') OR (firstname = 'Foo' AND lastname = 'Bar') OR (firstname = 'Tom' AND lastname = 'Turner') 

If you use the IN operator, as you said, different combinations will match. I think the previous example should be the fastest way to compare them.

Answer 3

You can combine them with concat :

 WHERE concat(`firstname`,'-',`lastname`) IN ('Luke-Skywalker', 'Foo-Bar', 'Tom-Turner');