Boolean Precision: WHERE, Precedence, and Pattern Matching

How a query returns confidently wrong rows without ever raising an error.

A query can be confidently wrong

The dangerous class of SQL bug isn't the one that errors — it's the one that returns a plausible row count and looks right. Operator precedence and pattern matching are the two most common sources of this failure mode.

SELECT * FROM Employees
WHERE Department = 'Technology'
   OR Department = 'Finance'
  AND Salary > 70000;

AND binds tighter than OR, so the engine reads this as:

WHERE Department = 'Technology'
   OR (Department = 'Finance' AND Salary > 70000)

Every Technology employee comes back — including one earning £58,000 — because the second condition never constrained the first. No error, no warning: the query answered a different question than the one intended.

Rule

Whenever AND and OR appear in the same WHERE clause, add explicit parentheses — even when precedence already gives the result you want. The parentheses are for the next reader, not the engine.

Catch this class of bug with a row count you can verify by hand. If roughly three rows are expected and five come back, stop before trusting the output. A cleaner rewrite of the intended query: WHERE Department IN ('Technology','Finance') AND Salary > 70000 — stated as an assumption, not a guess.

What an index can and can't do with LIKE

Pattern

Matches

'A%'

starts with A

'%A'

ends with A

'%A%'

contains A anywhere

'A__'

A plus exactly two more characters

% matches any run of characters, including none. _ matches exactly one.

Rule

An index is sorted by what a value starts with. A prefix pattern (Mar%) lets the engine jump straight to the right position. A suffix or contains pattern (%rsh, %rsh%) has no reflection in that sort order, so it forces a full scan regardless of table size.

The same limitation applies to any indexed column wrapped in a function — WHERE UPPER(LastName) = 'MARSH' can't use a plain index on LastName either, because the index stores LastName, not its uppercased form. This is a non-sargable predicate. Fix it with a case-insensitive collation or a computed index — the fix targets the cause, not the symptom.

Try it

SELECT * FROM Employees
WHERE Department = 'Technology'
   OR Department = 'Finance'
  AND Salary > 70000;