PostgreSQL CASE, COALESCE and NULL Handling: Practical SQL Guide

-

 

In PostgreSQL, CASE, COALESCE, and NULL-handling functions help you manage missing values, create conditional results, categorize data, and build meaningful business reports.

These concepts are useful for data analysts, SQL developers, reporting, and technical interviews.

What Is CASE in PostgreSQL?

The CASE expression works like an IF-THEN-ELSE statement. It checks conditions and returns a result based on the first matching condition.

Syntax:

SELECT
    column_name,
    CASE
        WHEN condition1 THEN result1
        WHEN condition2 THEN result2
        ELSE default_result
    END AS result_column
FROM table_name;

1. Categorize Employees Based on Salary

Consider an employees table with these columns: employee_id, employee_name, department, and salary.

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary >= 70000 THEN 'High Salary'
        WHEN salary >= 50000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM employees;

The query assigns a salary category to each employee.

Example:

employee_name salary salary_category
Arjun 75000 High Salary
Rahul 60000 Medium Salary
Priya 40000 Low Salary

The conditions are checked from top to bottom. Once a condition matches, PostgreSQL returns its result.

2. Use CASE with GROUP BY

You can use CASE to group records into categories and calculate totals.

SELECT
    CASE
        WHEN salary >= 70000 THEN 'High'
        WHEN salary >= 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_category,
    COUNT(*) AS employee_count
FROM employees
GROUP BY
    CASE
        WHEN salary >= 70000 THEN 'High'
        WHEN salary >= 50000 THEN 'Medium'
        ELSE 'Low'
    END;

This query returns the number of employees in each salary category.

3. Use CASE for Conditional Aggregation

Conditional aggregation calculates different metrics based on specific conditions.

SELECT
    department,
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (WHERE salary >= 60000) AS employees_earning_60000_or_more,
    SUM(CASE WHEN salary >= 60000 THEN salary ELSE 0 END) AS qualifying_salary_total
FROM employees
GROUP BY department;

This query returns the total number of employees, the number earning at least 60000, and the combined salaries of those qualifying employees for each department.

PostgreSQL also supports the FILTER clause for conditional aggregation.

What Is NULL in PostgreSQL?

NULL represents a missing or unknown value. It is not the same as zero, an empty string, or the word NULL stored as text.

For example, a table might contain employees whose bonus has not been recorded.

employee_name salary bonus
Rahul 60000 5000
Priya 40000 NULL
Arjun 75000 10000

Because Priya's bonus is NULL, its value is unknown or missing.

4. Find NULL Values Using IS NULL

Use IS NULL to identify missing values.

SELECT employee_name, bonus
FROM employees
WHERE bonus IS NULL;

To find records where the bonus is available:

SELECT employee_name, bonus
FROM employees
WHERE bonus IS NOT NULL;

Do not use bonus = NULL to check for missing values. Use IS NULL or IS NOT NULL.

5. Replace NULL Values Using COALESCE

COALESCE() returns the first non-NULL value from its arguments.

Syntax:

COALESCE(value1, value2, default_value)

Example:

SELECT
    employee_name,
    salary,
    bonus,
    COALESCE(bonus, 0) AS bonus_display
FROM employees;

If an employee's bonus is NULL, the result displays zero instead.

Example:

employee_name bonus bonus_display
Rahul 5000 5000
Priya NULL 0
Arjun 10000 10000

Remember that COALESCE() changes the value returned by the query, not the original stored data.

6. Calculate Total Compensation with COALESCE

Suppose NULL bonuses should be treated as zero when calculating total compensation.

SELECT
    employee_name,
    salary,
    COALESCE(bonus, 0) AS bonus,
    salary + COALESCE(bonus, 0) AS total_compensation
FROM employees;

Without COALESCE(), adding a NULL bonus to salary produces NULL. With COALESCE(), the missing bonus is treated as zero for this calculation.

Only use zero as a replacement when it makes sense for the business requirement.

7. Use NULLIF to Avoid Division by Zero

NULLIF(value1, value2) returns NULL when both values are equal. Otherwise, it returns the first value.

Example:

SELECT
    department,
    total_sales,
    total_orders,
    total_sales / NULLIF(total_orders, 0) AS average_order_value
FROM sales_summary;

If total_orders is zero, NULLIF(total_orders, 0) returns NULL, preventing a division-by-zero error.

For decimal results, use a numeric value such as 100.0 where needed to avoid unintended integer division.

8. Understand COUNT with NULL Values

PostgreSQL handles NULL differently depending on the aggregate function.

SELECT
    COUNT(*) AS total_rows,
    COUNT(bonus) AS recorded_bonuses
FROM employees;
  • COUNT(*) counts all rows.
  • COUNT(bonus) counts only rows where bonus is not NULL.
  • SUM(bonus) ignores NULL bonus values.

This distinction is important when calculating totals and data-quality metrics.

9. Calculate Missing-Value Counts

You can use conditional aggregation to count missing values in a column.

SELECT
    COUNT(*) AS total_employees,
    COUNT(*) FILTER (WHERE bonus IS NULL) AS missing_bonus_count,
    COUNT(*) FILTER (WHERE bonus IS NOT NULL) AS available_bonus_count
FROM employees;

This is useful for data cleaning and checking data completeness before analysis.

10. CASE vs COALESCE vs NULLIF

Expression Purpose Example
CASE Returns values based on conditions Categorize salaries
COALESCE Returns the first non-NULL value Replace a missing bonus with zero
NULLIF Returns NULL when two values are equal Avoid division by zero

Common Mistakes to Avoid

  1. Using = NULL instead of IS NULL.
  2. Assuming NULL is the same as zero.
  3. Forgetting that arithmetic involving NULL usually returns NULL.
  4. Replacing every NULL with zero without checking the business meaning.
  5. Forgetting that COUNT(column_name) excludes NULL values.
  6. Writing overlapping CASE conditions in the wrong order.

Practice Questions

Use the employees table to solve these questions.

  1. Categorize employees as High, Medium, or Low salary.
  2. Find employees whose bonus is NULL.
  3. Display NULL bonuses as zero using COALESCE().
  4. Calculate total compensation using salary and bonus.
  5. Count employees with missing bonuses in each department.
  6. Use CASE to label employees earning at least 60000 as Eligible and others as Not Eligible.
  7. Use NULLIF() to prevent division by zero when calculating an average.
  8. Compare COUNT(*) and COUNT(bonus).

Interview Questions

1. What is NULL in PostgreSQL?

NULL represents a missing or unknown value. It is different from zero and an empty string.

2. What is the difference between IS NULL and = NULL?

IS NULL correctly checks whether a value is NULL. Comparisons using = NULL do not evaluate to true.

3. What does COALESCE() do?

It returns the first non-NULL argument.

4. What is the purpose of NULLIF()?

It returns NULL when its two arguments are equal. It can help prevent division-by-zero errors.

5. What is the difference between COUNT(*) and COUNT(column_name)?

COUNT(*) counts all rows, whereas COUNT(column_name) counts only rows where that column is not NULL.

6. Can CASE be used inside an aggregate function?

Yes. For example, SUM(CASE WHEN condition THEN amount ELSE 0 END) calculates a total for rows matching a condition.

Conclusion

Understanding CASE, COALESCE(), NULLIF(), and NULL handling is essential for writing reliable PostgreSQL queries. These techniques help you categorize data, handle missing values, prevent calculation errors, and prepare accurate reports.

Practise these examples with business datasets to strengthen your SQL skills for data analyst and SQL developer roles.

Comments

Post a Comment

Popular posts from this blog

PostgreSQL Tutorial for Beginners: Learn SQL with Practical Examples

-
Introduction PostgreSQL is a powerful, open-source relational database management system used to store, organize, and retrieve data. It is widely used in web applications, business software, reporting systems, and data analytics projects. In this tutorial, you will learn the fundamentals of PostgreSQL through practical examples. You will understand how to create tables, insert records, retrieve data, filter results, and summarize information using SQL queries. This guide is designed for beginners who want to develop practical database skills for software development, data analytics, and other technology-related careers. What You Will Learn By the end of this tutorial, you will understand: What PostgreSQL is and how it works. How to create a database table. How to insert and retrieve records. How to filter and sort data. How to summarize data using aggregate functions. How to practice SQL using a business-related example. What Is PostgreSQL? PostgreSQL is an open-source relational datab...
Read more

PostgreSQL Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK and NOT NULL – Practical SQL Guide

-
Constraints are rules applied to table columns to maintain data accuracy and consistency in a PostgreSQL database. They prevent invalid data from being inserted or updated and help maintain relationships between tables. In this tutorial, you will learn the most commonly used PostgreSQL constraints with practical SQL examples. What Are Constraints in PostgreSQL? Constraints define rules that table data must follow. For example: - An employee ID should be unique. - An employee name should not be NULL. - A salary should not be negative. - An order should reference an existing customer. PostgreSQL provides several constraints to enforce these rules. 1. NOT NULL Constraint The NOT NULL constraint ensures that a column cannot contain NULL values. Suppose you have an employees table. CREATE TABLE employees (     employee_id INT,     employee_name VARCHAR(100) NOT NULL,     department VARCHAR(50) ); In this example, employee_name cannot contain NULL values. Insert ...
Read more