sql in operator on multiple columns

Question

I have a list of values , for example id= ('10','20','30') I want to check if these values exisit in either columnA, columnB or columnC

This is what I have and this works fine

  "select * from Table1 where ColumnA in", id

What I am trying to do is

  "select * from Table1 where (ColumnA or ColumnB or ColumnC) in", id

I am getting a bunch of errors, so need help with the syntax

As you don't key in the actual query source why it's so complicated to apply the same conditions multiple times within the source code? Btw, simply concatenating the SELECT allows SQL Injection. — dnoeth, Jun 04 '15 at 16:46

score 2 · Answer 1 · answered Jun 04 '15 at 16:13

2

Try this instead:

select * from Table1 where ColumnA in ('10','20','30') or
                           ColumnB in ('10','20','30') or
                           ColumnC in ('10','20','30')

answered Jun 04 '15 at 16:13

Giorgi Nakeuri

35,155
8
47
75

This makes me wish there was a short 1 liner for this check like maybe `(ColumnA, ColumnB, ColumnC) in ('10', '20', '30')`. – John Odom Jun 04 '15 at 16:14
@Giorgi, Thanks but I dont want hard code the value ('10','20','30') I want to pass these values through a variable (id) into the query – Ezra Polson Jun 04 '15 at 16:17
You could put ('10','20','30') into a temporary table, which may make the syntax slightly easier to read if there are lots of values. – Stuart Moore Jun 04 '15 at 16:17

score 0 · Answer 2 · answered Jun 04 '15 at 16:14

0

As far as I know there is no way to shorten this any further, you have to stick to

SELECT * 
FROM `yourtable` 
WHERE `ColumnA` IN ('10','20','30')
    OR `ColumnB` IN ('10','20','30') 
    OR `ColumnC` IN ('10','20','30');

answered Jun 04 '15 at 16:14

Bjoern

15,934
4
43
48

@Bjoem is it possible to pass `('10','20','30')` as a variable into the query ? – Ezra Polson Jun 04 '15 at 16:26
@EzraPolson Yes it is. Have a look at this SO question: http://stackoverflow.com/questions/342655/sql-select-in-value1-value2-with-passing-variable-of-values-into-gridvie . The procedure posted in the top answer shows how it is done. – Bjoern Jun 04 '15 at 17:36

score 0 · Answer 3 · answered Jun 04 '15 at 16:18

From what i could deduce from your question there, you want to find which of the given values exist in the given columns. This should be easy to get as below:

SELECT * FROM table1 WHERE ColumnA IN(10,20,30) AND ColumnB IN (10,20,30) AND ColumnC IN (10,20,30);

sql in operator on multiple columns

3 Answers3