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!

Answer 1

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.

Answer 2

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.