Does sqlite support any kind of IF(condition) statement in a select

sql

conditional-statements

sql-functions

best-practices

byAlex Kataev·Feb 5, 2025

The ELI5 (Explain Like I'm 5) version is: SQLite's way to say IF is using the CASE expression. The example below shows the syntax for conditional logic:

SELECT 
  CASE 
    WHEN column1 > 10 THEN 'Over 10'
    ELSE '10 or less'
  END AS result_label
FROM my_table;

Here, the idea is to select a text label based on whether column1 is greater than 10. In short - a handy, copy-paste ready trick perfect for conditional queries in SQLite.

The express lane: Using "iif"

As of SQLite 3.32.0, the iif function came into play. It shortens the visibility of conditional statements, acting more like a breath of fresh air than a life-saver:

SELECT iif(column1 > 10, 'Over 10', '10 or less') AS result_label
FROM my_table;

Words to the wise: when using "iif", make sure to always cover both true and false cases. This practice ensures no surprises lurk in the bushes around the corner.

Dealing with multiple conditions

Just like an overachiever, the CASE expression is capable of dealing with several conditions. It feels a lot like organizing an else if ladder:

SELECT 
  CASE 
    WHEN column1 > 10 THEN 'Over 10'
    WHEN column1 = 10 THEN 'Exactly 10'
    ELSE 'Less than 10'
  END AS result_label
FROM my_table;

Comes in handy when logic branches start to resemble a family tree.

Roadblocks in SQLite land

Control the detours before the roadblocks pop up:

Null values are like vampires to mirrors when it comes to WHEN clauses - they simply don't trigger.
Don't confuse the SQLite engine. Arrange conditions from most specific to most general to sidestep logic errors.

A touch of readability

Keep both your queries and colleagues happy by ensuring readability. Use indentation and sprinkle some comments liberally to decode complex CASE expressions:

SELECT 
  CASE
    -- When you're dealing with high-rollers
    WHEN column1 > 100 THEN 'High value'
    -- It's all about the middle-class, right?
    WHEN column1 BETWEEN 50 AND 100 THEN 'Medium value'
    ELSE 'Low value'
  END AS value_category
FROM my_table;