What is the purpose of the if, choose, when, otherwise dynamic SQL elements?

These elements provide conditional branching logic within a SQL statement, mirroring familiar programming constructs — <if> as a simple conditional, and <choose>/<when>/<otherwise> together as a switch-like construct that picks exactly one matching branch.

<select id="findUsers" resultType="User">
  SELECT * FROM users
  <choose>
    <when test="status == 'active'">
      WHERE status = 'ACTIVE'
    </when>
    <when test="status == 'inactive'">
      WHERE status = 'INACTIVE'
    </when>
    <otherwise>
      WHERE status IS NOT NULL
    </otherwise>
  </choose>
</select>

The key behavioral difference between the two constructs: multiple <if> elements are each evaluated independently, so more than one can be true and included at once (useful for combining several optional filters with AND), while <choose> evaluates its <when> branches in order and includes only the first one that matches, falling through to <otherwise> if none do — exactly mirroring how a switch statement or if/else-if chain behaves in general-purpose programming languages.

Choosing between them comes down to whether the conditions are meant to combine (use multiple <if> elements) or are mutually exclusive alternatives where only one should ever apply (use <choose>), and using the wrong one is a common source of unintended SQL — like accidentally combining conditions with <if> that were meant to be exclusive alternatives.