Member Avatar for ppetree

This seems like it should be a simple query but I think I have my eyes in backwards...

Give me all the users living in Jefferson, Bingham, Butte, Bonneville and Madison counties in the state of Idaho.

SELECT * FROM table WHERE county='Jefferson' OR county='Bingham' OR county='Butte' OR county='Bonneville' OR county='Madison' AND state='ID'

What I get is all users living in EVERY Jefferson county in the U.S!!!! My state='ID' gets ignored and none of the other counties are searched (not even in the other states!).

I'm confused... LOL

(and I know this is gonna be one of those forehead slap moments!)

  • 2 Contributors
  • 2 Replies
  • 116 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by ppetree

Dani AI

Generated

Classic operator-precedence gotcha: in SQL the AND operator binds more tightly than OR. That means an expression with several OR terms and a trailing AND will apply the AND only to the last OR term unless the OR terms are grouped. As pointed out, grouping the county tests so the state filter applies to the whole set fixes the logic.

A clearer, more maintainable form is to test the state first and use IN for the counties. This avoids precedence surprises and reads as the intent — “rows where state is ID and county is one of these”:

SELECT * FROM your_table
WHERE state = 'ID'
  AND county IN ('Jefferson','Bingham','Butte','Bonneville','Madison');

Practical checks and performance tips:

  • Ensure columns are stored consistently (no unexpected leading/trailing spaces, consistent case). Use TRIM() or UPPER()/LOWER() in comparisons if needed.
  • Add an index that matches the filtering pattern so the planner can use it efficiently, for example:
CREATE INDEX idx_state_county ON your_table (state, county);
  • Run EXPLAIN on the query to confirm the index is used and the plan looks sensible.
  • If the query is built dynamically in application code, use prepared statements / parameterized queries to avoid injection and formatting bugs.

If results still look wrong, inspect the actual stored values with a quick diagnostic query like SELECT DISTINCT state, county FROM your_table to spot unexpected values or variations. This sequence (correct grouping or IN, data hygiene, index + EXPLAIN) resolves most cases of this symptom.

Recommended Answers

All 2 Replies

Member Avatar for pritaeas 
SELECT * FROM table WHERE (county='Jefferson' OR county='Bingham' OR county='Butte' OR county='Bonneville' OR county='Madison') AND state='ID'
Member Avatar for ppetree

<headslap>doh!</headslap>

Damn! I shoulda thought of that! Thanks!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.