WHERE ... IN with multiple columns

Question

I try to get some users which meet certain conditions:

SELECT * FROM users
WHERE lastname IN ('Pitt','Jolie','Costner') 
AND firstname IN ('Brad','Angelina','Kevin')

But this way, Kevin Jolie and Brad Costner will be shown, which is NOT what I need.

So I tried:

SELECT * FROM users
WHERE (lastname,firstname) IN ('Pitt','Brad'),('Jolie','Angelina'),('Costner','Kevin')

Which throws the error:

An expression of non-boolean type specified in a context where a condition is expected, near ','.

Do you know a query that will yield my expected result?

Attn: The two or more columns are not always varchar columns, they may be of any type.

score 3 · Answer 1 · answered Aug 05 '14 at 14:43

3

Try this please.

SELECT * FROM users
WHERE lastname+firstname IN ('PittBrad','JolieAngelina','CostnerKevin')

In case the columns have null then

SELECT * FROM users
WHERE ISNULL(lastname,'')+ISNULL(firstname,'') IN ('PittBrad','JolieAngelina','CostnerKevin')

answered Aug 05 '14 at 14:43

mohan111

8,633
4
28
55

2

No. Wrong and inefficient. – ypercubeᵀᴹ Aug 05 '14 at 18:05

score 1 · Accepted Answer · answered Aug 05 '14 at 14:42

I would use a query like this (but i'm using SQL-Server 2005, maybe there's something neater now):

SELECT * FROM users
WHERE 
    (firstname = 'Angelina' AND lastname = 'Jolie') 
OR
    (firstname = 'Brad' AND lastname = 'Pitt') 
OR
    (firstname = 'Kevin' AND lastname = 'Costner')

WHERE ... IN with multiple columns

2 Answers2