Tell me more ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

I am using MySQL 5.0.88. I have a query like:

select DISTINCT party.­id 
FROM party­
  LEFT join party_­identifier AS pi 
  ON pi.­party_­id = party.­id­
WHERE ­(pi.­source_­note=? 
       AND (first_­name LIKE ? OR last_­name LIKE ? OR pi.­identifier LIKE ?))­ 
  AND party.­type = ? 
  AND party.­record_­status_­id = ?

If the Pi.source_note = ? evaluates to false, will the rest of the conditions will evaluated or not?

What is the execution sequence for the where conditions? (In terms specifically of SQL, not programming languages.)

share|improve this question

2 Answers

up vote 1 down vote

There is no order, subject to standard operator precedence.

SQL is a declarative language. You tell the engine "what to get", "how to do it". So the engine does not evaluate left to right in code order, nor does it generally short circuit.

For example:

  • the engine attempts to match the predicates against a suitable index. The index key columns order may not match the WHERE clause order so it will ignore the WHERE order.
  • it may decide that the 2nd condition is cheaper to evaluate in order to limit rows before applying the 1st condition
share|improve this answer
up vote 0 down vote

If you remember your order of operations (BODMAS), then remember this: AND is multiplication, OR is addition. Multiplication comes before addition, so all the ANDs are executed before the ORs:

(pi.­source_­note=? AND first_­name LIKE ? OR last_­name LIKE ? OR pi.­identifier LIKE ?)­ AND party.­type = ? AND party.­record_­status_­id = ?

translates to:

(
 (
   pi.­source_­note=? AND first_­name LIKE ?
 )
  OR last_­name LIKE ?
  OR pi.­identifier LIKE ?
)­
AND 
(
 party.­type = ? AND party.­record_­status_­id = ? 
)
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.