postgres syntax question for OR?

Question

The first one below works, but, just wanted to see if there's a better way...

If I'm trying to find all records that start with 'C' with either a flag of 2 or a status of 9, do I need to incorporate the 'C' criteria twice?

i.e.,

"SELECT * FROM mytable WHERE name like 'C%' AND flag = 2 OR name like 'C%' AND status = 9"

Or, is there a way quicker way to write it so that I only need to set 'C%' once?

kennytm · Accepted Answer · 2010-05-30 14:52:33Z

1

Logically, AND and OR are distributive to each other, i.e.

a & (b | c) = (a & b) | (a & c)

that means your condition can be rewritten as

name LIKE 'C%' AND (flag = 2 OR status = 9)

answered May 30, 2010 at 14:52

kennytm

526k111 gold badges1.1k silver badges1k bronze badges

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

Comments

Peter Tillemans · Accepted Answer · 2010-05-30 14:54:24Z

1

OR has lower priority than AND so you need parentheses.

WHERE name LIKE "C%" AND (flag = 2 OR flag = 9)

you can also check for membership of a set of values

... AND flag IN (1, 9)

answered May 30, 2010 at 14:54

Peter Tillemans

35.4k11 gold badges85 silver badges117 bronze badges