Alexey Petrushin Alexey Petrushin - 7 months ago 13
SQL Question

Select values in two sets

Let's suppose I would like to fetch two users from database

Sarah Connor
and
Kyle Reese
. How to do that using both first and second names?

select *
from users
where
first_name in ('Sarah', 'Kyle') and
last_name in ('Connor', 'Reese')


But, this query is wrong, because it would fetch
Sarah Reese
too. How to fix it? And, the query should be also efficient in terms of performance.

Answer

In MySQL you could use this:

select * 
from users 
where 
  (first_name, last_name) in (('Sarah', 'Connor'), ('Kyle', 'Reese'))

Otherwise you can use:

select * 
from users 
where 
   (first_name = 'Sarah' and last_name = 'Connor') or
   (first_name = 'Kyle' and last_name = 'Reese')
Comments