SQL where in clause with array of array

Question

I know that the WHERE ... IN ... clause allows to select all values of a field that exists in a given array. How do I expand this idea to a group of fields? Something like:

SELECT * FROM table_name
WHERE (First, Last) IN ((Adam, Scott), (Betty, Johnson), (Cathy, Wyatt))

Or any other method that allows the same result.

Martin Smith · Accepted Answer · 2015-09-23 23:20:47Z

2

The syntax in the question is valid in some other RDBMSs but not SQL Server. If on 2008+ you can use

SELECT *
FROM   table_name t
WHERE  EXISTS(SELECT *
              FROM   (VALUES ('Adam', 'Scott'),
                             ('Betty','Johnson'),
                             ('Cathy','Wyatt')
                              ) v(first, last)
              WHERE t.first = v.first AND t.last = f.last

edited Sep 23, 2015 at 23:20

answered Sep 23, 2015 at 23:02

Martin Smith

457k97 gold badges777 silver badges886 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Malk · Accepted Answer · 2015-09-23 23:00:19Z

1

It depends on what you are after really. If you just want to combine 2 fields then you can use something like this:

SELECT * FROM table_name
WHERE First+','+Last IN ('Adam, Scott', 'Betty, Johnson', 'Cathy, Wyatt')

answered Sep 23, 2015 at 23:00

Malk

12k4 gold badges35 silver badges34 bronze badges

4 Comments

Martin Smith Over a year ago

@NickyThai it should do. Though relies on commas not being valid in the columns being concatenated.

Malk Over a year ago

What do you mean? If your table_name has string type columns named First and Last then it will work fine.

Nam Thai Over a year ago

@MartinSmith, @Malk I got the error Invalid column name ',' I tried both single quote and double quote.

Martin Smith Over a year ago

Double quotes it will be treated as an object identifier under default settings and give you that error. Single quotes should work though...

PeonProgrammer · Accepted Answer · 2015-09-23 22:59:43Z

-3

SELECT * FROM Table_name WHERE First IN ('Adam', 'Betty', 'Cathy') AND Last IN ('Scott', 'Johnson', 'Wyatt')

answered Sep 23, 2015 at 22:59

PeonProgrammer

1,5352 gold badges15 silver badges28 bronze badges

2 Comments

Martin Smith Over a year ago

This will return rows with names matching any of the 9 permutations of those names. Not just the three desired.

PeonProgrammer Over a year ago

@MartinSmith That's true, but I don't see where the question says that wasn't a desirable outcome. He or she could have been more specific that they want to return only rows where the first name and the last name were matched.

Collectives™ on Stack Overflow

SQL where in clause with array of array

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

4 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related