How to do select from where x is equal to multiple values?

Question

I am debugging some code and have encountered the following SQL query (simplified version):

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id = 2
OR ads.county_id = 5
OR ads.county_id = 7
OR ads.county_id = 9

I'm getting very strange results from the query and I think its because the first OR is negating the AND operators that are found before it.

This results in getting results back for ads of all types and not just for the type 13.

Each time the query is called there may be a differnt amount of county entities that need to be looked up.

Any help on the correct way to go about this would be appreciated.

score 163 · Accepted Answer · edited Aug 18 '16 at 17:59

163

Put parentheses around the "OR"s:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND
(
    ads.county_id = 2
    OR ads.county_id = 5
    OR ads.county_id = 7
    OR ads.county_id = 9
)

Or even better, use IN:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id IN (2, 5, 7, 9)

edited Aug 18 '16 at 17:59

PBwebD

778
11
33

answered Nov 04 '08 at 12:49

Greg

316,276
54
369
333

11

I think the IN version is easier to read and less likely to return unexpected results. – DOK Nov 04 '08 at 12:52
5

it's also ***substantially*** faster, at least on MySQL – warren Nov 04 '08 at 13:21
1

In my case the values in the `IN` are coming from the result of another select query, how do I handle that? – Parag Kadam Dec 01 '17 at 10:50
what about "not equal to" for multiple properties separated by OR operator? – Vivek Jul 09 '18 at 10:58

score 26 · Answer 2 · edited Jul 24 '11 at 21:43

26

You can try using parentheses around the OR expressions to make sure your query is interpreted correctly, or more concisely, use IN:

SELECT ads.*, location.county 
FROM ads
LEFT JOIN location ON location.county = ads.county_id
WHERE ads.published = 1 
AND ads.type = 13
AND ads.county_id IN (2,5,7,9)

edited Jul 24 '11 at 21:43

Kurru

14,180
18
64
84

answered Nov 04 '08 at 12:50

Ned Batchelder

364,293
75
561
662

score 15 · Answer 3 · answered Nov 04 '08 at 12:51

15

And even simpler using IN:

SELECT ads.*, location.county 
  FROM ads
  LEFT JOIN location ON location.county = ads.county_id
  WHERE ads.published = 1 
        AND ads.type = 13
        AND ads.county_id IN (2,5,7,9)

answered Nov 04 '08 at 12:51

Ruben

9,056
6
34
44

How to do select from where x is equal to multiple values?

3 Answers3

Linked