Control Flow Functions (IF, IFNULL, COALESCE)0%

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Control Flow Functions (IF, IFNULL, COALESCE)

Beginner12 min read•Updated: 2026-09-12

Control Flow Functions: IF, IFNULL, and COALESCE

Relational databases are not limited to passive data retrieval. You often need inline conditional logic: classifying orders as High Value vs Standard, substituting missing telephone numbers, or preventing division by zero errors. MySQL provides several compact Control Flow Functions: IF(), IFNULL(), NULLIF(), and the ANSI standard COALESCE().

1. The Ternary `IF(condition, true_val, false_val)` Function

MySQL's built-in IF() function operates exactly like the ternary operator in programming languages (condition ? true_val : false_val):

1-- Classify employee compensation tiers:
2SELECT 
3    full_name, 
4    salary,
5    IF(salary >= 75000.00, 'Senior Compensation', 'Standard Compensation') AS salary_tier
6FROM employees;

2. Replacing NULLs: `IFNULL(expression, fallback)`

The IFNULL() function takes two arguments:

1
If the first argument is NOT NULL, it returns the first argument.
2
If the first argument IS NULL, it returns the second argument (the fallback).

1-- Replace missing discount percentages with 0%:
2SELECT 
3    product_name,
4    base_price,
5    IFNULL(discount_percentage, 0.00) AS applied_discount
6FROM products;

3. The Universal Standard: `COALESCE(val1, val2, ..., valN)`

While IFNULL() is a MySQL-specific convenience limited to two arguments, COALESCE() is the ANSI SQL standard function supported by every relational database on Earth. It evaluates arguments from left to right and returns the first non-null value:

1-- Cascading contact lookup:
2-- Checks mobile phone first. If NULL, checks work phone. If NULL, checks email.
3-- If all are NULL, falls back to 'No Contact Info'!
4SELECT 
5    customer_name,
6    COALESCE(mobile_phone, work_phone, home_phone, email, 'No Contact Info') AS primary_reach
7FROM customers;

4. Preventing Errors: `NULLIF(val1, val2)`

The NULLIF() function compares two arguments:

If val1 = val2, it returns NULL!
Otherwise, it returns val1.

The Classic Use Case: Safe Division by Zero Prevention

1-- If sales_count is 0, NULLIF(sales_count, 0) turns it into NULL.
2-- In SQL, any number divided by NULL safely evaluates to NULL without error!
3SELECT 
4    total_revenue,
5    sales_count,
6    total_revenue / NULLIF(sales_count, 0) AS average_sale_amount
7FROM store_analytics;

5. Comparison: Control Flow Functions

Function	Standard?	Arguments	Return Rule
`IF(cond, a, b)`	MySQL Only	Exactly 3	Returns `a` if `cond` is true; else `b`.
`IFNULL(a, b)`	MySQL Only	Exactly 2	Returns `a` if `a` is not null; else `b`.
`COALESCE(a, b, ...)`	ANSI SQL	2 or more	Returns the first non-null argument in list.
`NULLIF(a, b)`	ANSI SQL	Exactly 2	Returns `NULL` if `a = b`; else `a`.

6. Best Practices & Common Pitfalls

Prefer COALESCE over IFNULL for Portability: If there is any chance your application might migrate to PostgreSQL, Oracle, or SQLite, always choose COALESCE() over IFNULL().
Complex Multi-Branch Logic: For multi-branch conditional trees with more than two outcomes, avoid nesting multiple IF() statements. Use standard CASE WHEN ... THEN ... ELSE ... END expressions instead.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does SELECT IF(10 > 5, 'Yes', 'No'); return in MySQL?

Question 2

What will COALESCE(NULL, NULL, 'Found Me', 'Backup') return?

Question 3

What does NULLIF(50, 50) return?

Question 4

Which function is an official ANSI SQL standard supported across all major database engines?

Question 5

How can NULLIF be used to prevent division by zero errors when dividing revenue by visits?

Next Lesson

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Date & Time Functions	Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

PreviousPrevNext

MSK Institute • Shikohabad

Loading Page Content...

Preparing courses, live labs, and learning resources.सामग्री लोड हो रही है, कृपया प्रतीक्षा करें...

Connecting serverPlease wait...

💻 Practical Labs🚀 Free Demo📜 Verified Certificates

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Control Flow Functions (IF, IFNULL, COALESCE)

Beginner12 min read•Updated: 2026-09-12

Study Materials Cheatsheet

Control Flow Functions: IF, IFNULL, and COALESCE

1. The Ternary `IF(condition, true_val, false_val)` Function

MySQL's built-in IF() function operates exactly like the ternary operator in programming languages (condition ? true_val : false_val):

1-- Classify employee compensation tiers:
2SELECT 
3    full_name, 
4    salary,
5    IF(salary >= 75000.00, 'Senior Compensation', 'Standard Compensation') AS salary_tier
6FROM employees;

2. Replacing NULLs: `IFNULL(expression, fallback)`

The IFNULL() function takes two arguments:

1
If the first argument is NOT NULL, it returns the first argument.
2
If the first argument IS NULL, it returns the second argument (the fallback).

1-- Replace missing discount percentages with 0%:
2SELECT 
3    product_name,
4    base_price,
5    IFNULL(discount_percentage, 0.00) AS applied_discount
6FROM products;

3. The Universal Standard: `COALESCE(val1, val2, ..., valN)`

1-- Cascading contact lookup:
2-- Checks mobile phone first. If NULL, checks work phone. If NULL, checks email.
3-- If all are NULL, falls back to 'No Contact Info'!
4SELECT 
5    customer_name,
6    COALESCE(mobile_phone, work_phone, home_phone, email, 'No Contact Info') AS primary_reach
7FROM customers;

4. Preventing Errors: `NULLIF(val1, val2)`

The NULLIF() function compares two arguments:

If val1 = val2, it returns NULL!
Otherwise, it returns val1.

The Classic Use Case: Safe Division by Zero Prevention

1-- If sales_count is 0, NULLIF(sales_count, 0) turns it into NULL.
2-- In SQL, any number divided by NULL safely evaluates to NULL without error!
3SELECT 
4    total_revenue,
5    sales_count,
6    total_revenue / NULLIF(sales_count, 0) AS average_sale_amount
7FROM store_analytics;

5. Comparison: Control Flow Functions

Function	Standard?	Arguments	Return Rule
`IF(cond, a, b)`	MySQL Only	Exactly 3	Returns `a` if `cond` is true; else `b`.
`IFNULL(a, b)`	MySQL Only	Exactly 2	Returns `a` if `a` is not null; else `b`.
`COALESCE(a, b, ...)`	ANSI SQL	2 or more	Returns the first non-null argument in list.
`NULLIF(a, b)`	ANSI SQL	Exactly 2	Returns `NULL` if `a = b`; else `a`.

6. Best Practices & Common Pitfalls

Prefer COALESCE over IFNULL for Portability: If there is any chance your application might migrate to PostgreSQL, Oracle, or SQLite, always choose COALESCE() over IFNULL().
Complex Multi-Branch Logic: For multi-branch conditional trees with more than two outcomes, avoid nesting multiple IF() statements. Use standard CASE WHEN ... THEN ... ELSE ... END expressions instead.

Interactive Quiz• Knowledge Check

Multiple Choice Questions

Question 1

What does SELECT IF(10 > 5, 'Yes', 'No'); return in MySQL?

Question 2

What will COALESCE(NULL, NULL, 'Found Me', 'Backup') return?

Question 3

What does NULLIF(50, 50) return?

Question 4

Which function is an official ANSI SQL standard supported across all major database engines?

Question 5

How can NULLIF be used to prevent division by zero errors when dividing revenue by visits?

Next Lesson

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Continue learning with hands-on practice, examples, and exercises in the upcoming topic.

Related Lessons

Previous Lesson	Next Lesson
Date & Time Functions	Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Practice Quiz

Test your understanding of this lesson with 5 questions. Each question has one correct answer.

Previous Next

Control Flow Functions: IF, IFNULL, and COALESCE

1. The Ternary IF(condition, true_val, false_val) Function

2. Replacing NULLs: IFNULL(expression, fallback)

3. The Universal Standard: COALESCE(val1, val2, ..., valN)

4. Preventing Errors: NULLIF(val1, val2)

The Classic Use Case: Safe Division by Zero Prevention

5. Comparison: Control Flow Functions

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Related Lessons

Practice Quiz

Loading Page Content...

Control Flow Functions: IF, IFNULL, and COALESCE

1. The Ternary IF(condition, true_val, false_val) Function

2. Replacing NULLs: IFNULL(expression, fallback)

3. The Universal Standard: COALESCE(val1, val2, ..., valN)

4. Preventing Errors: NULLIF(val1, val2)

The Classic Use Case: Safe Division by Zero Prevention

5. Comparison: Control Flow Functions

6. Best Practices & Common Pitfalls

Multiple Choice Questions

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Related Lessons

Practice Quiz

1. The Ternary `IF(condition, true_val, false_val)` Function

2. Replacing NULLs: `IFNULL(expression, fallback)`

3. The Universal Standard: `COALESCE(val1, val2, ..., valN)`

4. Preventing Errors: `NULLIF(val1, val2)`

1. The Ternary `IF(condition, true_val, false_val)` Function

2. Replacing NULLs: `IFNULL(expression, fallback)`

3. The Universal Standard: `COALESCE(val1, val2, ..., valN)`

4. Preventing Errors: `NULLIF(val1, val2)`