Conditional Logic

10 Functions

Conditional logic allows your SQL queries to return different values based on specific conditions. It functions like "If-Then-Else" logic in programming, enabling you to categorize data, handle missing values, and perform complex business logic directly within your SELECT statements.

Table of Contents

CASE (Simple)CASE (Searched)COALESCE()NULLIF()GREATEST()LEAST()IS DISTINCT FROMIS NOT DISTINCT FROMFILTER (WHERE ...)Logical IF (via CASE)
1.

CASE (Simple)

Description

Compares an expression to a set of simple values to determine the result.

Syntax
CASE expression WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE default_result END
When to Use

Translating numeric status codes into human-readable text.

Example Query
SELECT status_code,
       CASE status_code
           WHEN 1 THEN 'Active'
           WHEN 0 THEN 'Inactive'
           ELSE 'Unknown'
       END AS status_label
FROM users;
💡 Pro Tip

Simple CASE is cleaner when you are checking a single column against specific constants.

2.

CASE (Searched)

Description

Evaluates a set of Boolean expressions to determine the result. More flexible than Simple CASE.

Syntax
CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE default_result END
When to Use

Categorizing users into "Tiers" based on their XP ranges in XQLora.

Example Query
SELECT username, xp,
       CASE
           WHEN xp >= 5000 THEN 'Legend'
           WHEN xp >= 1000 THEN 'Expert'
           ELSE 'Beginner'
       END AS rank
FROM users;
💡 Pro Tip

The order of WHEN clauses matters! The first condition that evaluates to TRUE is the one that gets executed.

3.

COALESCE()

Description

Returns the first non-null value from a list of arguments.

Syntax
COALESCE(value1, value2, ..., value_n)
When to Use

Providing a fallback "Guest" name if a user hasn't set their display name.

Example Query
SELECT COALESCE(display_name, username, 'Anonymous') FROM profiles;
💡 Pro Tip

This is the most common way to handle NULL values in reports to prevent empty cells in your UI.

4.

NULLIF()

Description

Returns NULL if two arguments are equal; otherwise, it returns the first argument.

Syntax
NULLIF(value1, value2)
When to Use

Preventing "Division by Zero" errors by turning a zero denominator into NULL.

Example Query
SELECT total_sales / NULLIF(total_orders, 0) FROM stats;
💡 Pro Tip

Since dividing by NULL results in NULL (not an error), this keeps your queries from crashing on bad data.

5.

GREATEST()

Description

Returns the largest value from a list of any number of expressions.

Syntax
GREATEST(value1, value2, ...)
When to Use

Comparing multiple columns to find the most recent activity date.

Example Query
SELECT GREATEST(last_login, last_quest_date, last_arena_date) FROM users;
💡 Pro Tip

Unlike MAX(), which works across many rows, GREATEST() works across multiple columns in a single row.

6.

LEAST()

Description

Returns the smallest value from a list of any number of expressions.

Syntax
LEAST(value1, value2, ...)
When to Use

Applying the lower of two possible discount rates.

Example Query
SELECT LEAST(manual_discount, automatic_coupon) FROM checkout;
💡 Pro Tip

Just like GREATEST(), this ignores NULL values unless all arguments are NULL.

7.

IS DISTINCT FROM

Description

A null-safe comparison operator that returns true if two values are different, even if one is NULL.

Syntax
value1 IS DISTINCT FROM value2
When to Use

Detecting if a column value has changed, especially when switching from a value to NULL.

Example Query
SELECT * FROM audit_logs WHERE old_value IS DISTINCT FROM new_value;
💡 Pro Tip

Standard = returns NULL if compared to a NULL. This operator treats NULL as a comparable value.

8.

IS NOT DISTINCT FROM

Description

A null-safe comparison that returns true if two values are equal, treating two NULLs as "equal."

Syntax
value1 IS NOT DISTINCT FROM value2
When to Use

Joining two tables where the join key might contain NULL values.

Example Query
SELECT * FROM t1 JOIN t2 ON t1.id IS NOT DISTINCT FROM t2.id;
💡 Pro Tip

Basically the opposite of IS DISTINCT FROM. Useful for joins matching NULLs.

9.

FILTER (WHERE ...)

Description

A PostgreSQL-specific conditional logic for aggregate functions.

Syntax
AGG_FUNC(column) FILTER (WHERE condition)
When to Use

Calculating "Bronze" and "Silver" quest completions in a single query.

Example Query
SELECT user_id,
       COUNT(*) FILTER (WHERE type = 'Bronze') AS bronze_count,
       COUNT(*) FILTER (WHERE type = 'Silver') AS silver_count
FROM user_quests GROUP BY user_id;
💡 Pro Tip

This is much more efficient and readable than using multiple CASE statements inside counts.

10.

Logical IF (via CASE)

Description

While PostgreSQL doesn't have a standalone IF() function (like MySQL), the logic is fully handled by CASE.

Syntax
CASE WHEN condition THEN true_val ELSE false_val END
When to Use

Simple binary logic, like checking if a user is "Eligible" for a reward.

Example Query
SELECT username, CASE WHEN xp > 100 THEN 'Eligible' ELSE 'Locked' END FROM users;
💡 Pro Tip

PostgreSQL doesn't have IF() function like MySQL/Excel. Use CASE instead.