Select Syntax - is "something = ('this' or 'that' or 'other')" ok to do?

Question

I am using MySQL v5.5.27. I usually would write this SELECT statement like this:

SELECT DISTINCT * 
FROM table 
WHERE something = 'this' 
    OR something = 'that' 
    OR something = 'other' 
    AND thingamajig = 'one' 
    OR thingamajig = 'two'

But would this SELECT statement provide the exact same result?

SELECT DISTINCT * 
FROM table 
WHERE something = ('this' OR 'that' OR 'other') 
    AND thingamajig = ('one' OR 'two')

I have tried running this and it seems to be working. Just want to make sure this second way of doing things won't return errant data in some way that I can't think of.

Thanks for any insight, assistance!

Withou parens, your first query is probably wrong due to the order of precedence for AND and OR operations. — Joe Stefanelli, Sep 26 '12 at 15:11

score 6 · Answer 1 · edited May 23 '17 at 12:15

6

I would recommend to use parentheses to clearly group the order of your search criteria.

Your 2nd SELECT statement is much more clear than your first.

SELECT DISTINCT * 
FROM table 
WHERE something = ('this' OR 'that' OR 'other') 
AND thingamajig = ('one' OR 'two')

Remember, you can also use the IN operator when you have multiple values:

SELECT DISTINCT * 
FROM table 
WHERE something IN ('this', 'that', 'other') 
AND thingamajig IN ('one', 'two')

Additionally, using the IN operator performs much faster than using OR.

edited May 23 '17 at 12:15

Community

1
1

answered Sep 26 '12 at 15:10

Kermit

33,827
13
85
121

I would like to use IN but -- and I should have made this clear in my original post -- I need to be able to distinguish between ('this' OR 'that' OR 'other') and ('this' AND 'that' AND 'other'). This is because we get the this/that/other data from a search form. If someone checks off 5 things and selects "match any" - we need to run the query with OR in place. If they select "match all" we need to run with AND in place. – user1286755 Sep 26 '12 at 15:36

D'Arcy Rittich · Answer 2 · 2012-09-26T15:17:42.000

AND takes precedence over OR in SQL. This means your first query equates to this:

SELECT DISTINCT * 
FROM table 
WHERE something = 'this' 
    OR something = 'that' 
    OR (something = 'other' AND thingamajig = 'one') 
    OR thingamajig = 'two'

which is obviously not the same result as your second query, and is likely not your intended behavior.

In this case you could use IN, which would happen to fix your issue, but in general, it is good to understand operator precedence so that you know the result of various AND and OR combinations.

So, to be clear, you could rewrite your first query as:

SELECT DISTINCT *     
FROM table     
WHERE (something = 'this'         
        OR something = 'that'         
        OR something = 'other')         
    AND (thingamajig = 'one'         
        OR thingamajig = 'two')

Whether you then convert that to use IN statements will have no effect on output, and lilely no effect on performance either, so is more of a matter of coding style and elegance than anything else.

score 2 · Answer 3 · answered Sep 26 '12 at 15:09

2

Use IN instead of =.

SELECT DISTINCT * 
FROM table 
WHERE something IN ('this', 'that', 'other') AND 
      thingamajig IN ('one', 'two')

IN is similar to OR so performance is the same. Your second query produces syntax error. Because only comma is allowed in IN clause.

SELECT DISTINCT * 
FROM table 
WHERE something = ('this' OR 'that' OR 'other') 
    AND thingamajig = ('one' OR 'two')

answered Sep 26 '12 at 15:09

John Woo

258,903
69
498
492

1

Can you please expand on this? Is `IN` faster? Does it give different results? Are there differences between the two posted pieces of code? – nico Sep 26 '12 at 15:10
nico - Your second piece of code will not work because you need to use an IN clause instead of =. Your first piece of code won't work correctly because you are not using parenthesis around your "OR" conditions. – Tom Sep 26 '12 at 15:13

score 0 · Answer 4 · answered Sep 26 '12 at 18:04

You also can use UNIONs like this:

SELECT 
  DISTINCT * 
FROM 
   table 
WHERE 
    something = ('this', 'that', 'other') 
UNION
SELECT 
  * 
FROM 
   table 
WHERE 
   thingamajig IN ('one', 'two')

Your query can be more 'identified' using flag vars for each SELECT...

SELECT
  'FOO_CASE_1' as case
FROM
  DUAL
UNION
SELECT
  'FOO_CASE_2' as case
FROM
  DUAL
ORDER BY
  1

This way leaving your debug more easy ;)

Select Syntax - is "something = ('this' or 'that' or 'other')" ok to do?

4 Answers4