SQL Utility Functions

Dialect note: IFNULL is MySQL-specific; standard SQL uses COALESCE. GREATEST, LEAST, and ROUND can differ across databases, especially for NULL and tie handling.

Turning Raw Data into Clean and Useful Information

In real-world databases, data is rarely stored exactly how we want to display it.

Sometimes we need to:

• round decimal values
• convert negative numbers into positive values
• find the highest score among multiple columns
• replace missing values with defaults
• select the first available value from multiple columns

SQL provides several built-in utility functions to solve these problems efficiently.

Today we'll learn:

• ROUND()
• ABS()
• GREATEST()
• LEAST()
• IFNULL()
• COALESCE()

These functions are heavily used in reports, dashboards, analytics, and production applications.

Dataset Used

We'll use the following students table throughout this lesson.

CREATE TABLE students (
  id INT PRIMARY KEY,
  name VARCHAR(40) NOT NULL,
  city VARCHAR(30) NULL,
  phone VARCHAR(20) NULL,

  fee_paid DECIMAL(10,2) NULL,
  score_change DECIMAL(10,2) NULL,

  test1 INT NULL,
  test2 INT NULL,
  test3 INT NULL
);

(Use the same INSERT statements you've already added.)

Student Table

1. ROUND()

What is ROUND()?

ROUND() rounds a numeric value to a specified number of decimal places.

Syntax

ROUND(number, decimal_places)

If decimal places are omitted:

ROUND(number)

SQL rounds to the nearest integer.

Example

SELECT
    id,
    name,
    fee_paid,
    ROUND(fee_paid,0) AS fee_rupees,
    ROUND(fee_paid,2) AS fee_2dp
FROM students
ORDER BY id;

Output

Standard Rounding Rules

0.5 or above
↓

Round Up

Example

8.5 → 9

8.6 → 9

Less than 0.5
↓

Round Down

Example

8.4 → 8

8.2 → 8

Real-Life Use Cases

ROUND() is commonly used for:

• displaying prices
• invoice totals
• GST calculations
• average ratings
• financial reports

Example:

Instead of showing

₹2804.453246

display

₹2804.45

Important Rules

• ROUND() works only on numeric values.
• NULL remains NULL.
• It does not modify the stored value, only the displayed result.

2. ABS()

What is ABS()?

ABS() returns the absolute value of a number.

It measures the distance from zero.

Negative numbers become positive.

Positive numbers stay unchanged.

NULL remains NULL.

Syntax

ABS(number)

Example

SELECT
    id,
    name,
    score_change,
    ABS(score_change) AS abs_score_change
FROM students
ORDER BY id;

Output

Internal Understanding

Think of ABS() as removing the minus sign.

-50

↓

50

120

↓

120

Real-Life Use Cases

Useful for:

• score differences
• stock price movement
• balance adjustments
• profit/loss comparison
• measuring deviations

Sometimes you only care about

How much did it change?

not

Whether it increased or decreased.

Important Rules

• Positive values remain unchanged.
• Negative values become positive.
• NULL stays NULL.

3. GREATEST() and LEAST()

These two functions compare multiple values in the same row.

Unlike MIN() and MAX(), they do not aggregate multiple rows.

They simply compare multiple expressions.

GREATEST()

Returns the largest value.

Syntax

GREATEST(value1, value2, value3...)

LEAST()

Returns the smallest value.

Syntax

LEAST(value1, value2, value3...)

Example

SELECT
    id,
    name,
    test1,
    test2,
    test3,
    GREATEST(test1,test2,test3) AS greatest_score,
    LEAST(test1,test2,test3) AS least_score
FROM students
ORDER BY id;

Output

Mental Model

Imagine comparing three exam scores.

55

60

58

Highest

↓

60

Lowest

↓

55

SQL does exactly this for every row.

Real-Life Use Cases

Useful for:

• highest exam score
• lowest temperature
• maximum quarterly sales
• minimum stock price
• best employee rating

Important Rule About NULL

One very important behavior in MySQL:

If any argument passed to GREATEST() or LEAST() is NULL, the result is also NULL.

Example

78
NULL
65

Result

GREATEST → NULL

LEAST → NULL

Because SQL cannot compare an unknown value.

Difference Between MAX() and GREATEST()

Example:

MAX(salary)

Highest salary among employees.

GREATEST(test1,test2,test3)

Highest score for one student.

Summary

4. IFNULL()

What is IFNULL()?

IFNULL() is used to replace NULL values with another value.

Think of it as saying:

"If this value is missing, show something else."

It does not filter rows. It only changes what is displayed.

Syntax

IFNULL(expression, replacement_value)

It accepts exactly 2 arguments:

• expression → value to check
• replacement → value to show if expression is NULL

Example

SELECT
    id,
    name,
    IFNULL(phone, 'Not Provided') AS phone_status
FROM students;

Output

Why Didn't Rohan Become "Not Provided"?

Because his phone number is not NULL.

It is an empty string ('').

NULL
↓

Missing value

''
↓

Value exists

But empty

IFNULL only replaces NULL, not empty strings.

Handling NULL and Empty Strings Together

Production databases usually contain:

• NULL
• ''
• ' ' (spaces)

We should normalize them first.

SELECT
    id,
    name,
    phone,
    IFNULL(NULLIF(phone,''),'Not Provided') AS final_phone
FROM students;

How This Works

Step 1

NULLIF(phone,'')

Converts:

''
↓

NULL

Step 2

IFNULL(...,'Not Provided')

Converts

NULL

↓

Not Provided

Final Output

Real-Life Use Cases

Useful for:

• Missing phone numbers
• Missing addresses
• Missing profile pictures
• Missing product images
• Missing salaries
• Missing coupon codes

Important Rules

✅ Replaces only NULL

❌ Does not replace ''

❌ Does not remove spaces

5. COALESCE()

What is COALESCE()?

COALESCE returns the first NON-NULL value from a list.

Think of it as trying one option after another until it finds data.

Syntax

COALESCE(value1, value2, value3, ...)

Unlike IFNULL(),

COALESCE can accept multiple values.

Dataset Used

CREATE TABLE coalesce_demo (
  id INT PRIMARY KEY,
  primary_email VARCHAR(80),
  work_email VARCHAR(80),
  personal_email VARCHAR(80),
  hours_logged DECIMAL(10,2),
  default_hours DECIMAL(10,2)
);

Scenario 1 — First Available Email

SELECT
    id,
    COALESCE(primary_email,
             work_email,
             personal_email) AS contact_email
FROM coalesce_demo;

Output

Internal Working

SQL checks one value at a time.

Primary Email

↓

NULL ?

↓

Yes

↓

Check Work Email

↓

NULL ?

↓

Yes

↓

Check Personal Email

↓

Found!

↓

Return It

As soon as SQL finds a non-null value,

it stops checking.

Scenario 2 — Default Email

SELECT
    id,
    COALESCE(
        primary_email,
        work_email,
        personal_email,
        'hello@tuf'
    ) AS contact_email
FROM coalesce_demo;

Output

Why?

For id = 4, every email is NULL.

So SQL reaches the default value.

Scenario 3 — Skip Empty Strings Too

SELECT
    id,
    COALESCE(
        NULLIF(TRIM(primary_email),''),
        work_email,
        personal_email
    ) AS contact_email
FROM coalesce_demo;

Why TRIM()?

Suppose data contains

'      '

It is not NULL.

It is only spaces.

TRIM removes spaces.

'      '

↓

''

Then

NULLIF('', '')

becomes

NULL

Now COALESCE ignores it.

Final Output

Real-Life Use Cases

COALESCE is heavily used for:

• Contact emails
• Delivery address
• Phone numbers
• Backup addresses
• Default prices
• Missing salaries

IFNULL() vs COALESCE()

IS NULL vs IS NOT NULL vs IFNULL() vs COALESCE()

Mental Model

IS NULL

Is this value missing?

↓

TRUE / FALSE

Used for filtering rows.

IFNULL()

NULL ?

↓

Yes

↓

Show replacement

COALESCE()

Value 1

↓

NULL?

↓

Yes

↓

Value 2

↓

NULL?

↓

Yes

↓

Value 3

↓

Found!

↓

Return It

Common Mistakes to Avoid

1. IFNULL() does NOT replace empty strings

IFNULL('', 'Unknown')

Result

''

Not

Unknown

2. Forgetting NULLIF()

Production data often contains

''

instead of NULL.

Always normalize first.

IFNULL(NULLIF(phone,''),'Not Provided')

3. Confusing IFNULL() with COALESCE()

Wrong thinking:

They are identical.

Not true.

IFNULL

↓

Only 2 values

COALESCE

↓

Unlimited values

4. Using COALESCE() in PostgreSQL

Good practice:

Prefer COALESCE because it is ANSI SQL standard.

5. Forgetting TRIM()

Sometimes data contains:

'      '

instead of

''

Always use

TRIM()

before

NULLIF()

6. Assuming COALESCE Ignores Empty Strings

It does not.

''

↓

Valid Value

COALESCE returns it immediately.

7. Replacing NULL in WHERE

This is incorrect:

WHERE IFNULL(city,'Delhi')

Use

WHERE city IS NULL

for filtering.

Use IFNULL only for displaying values.

Review Tips

Remember this rule:

• IS NULL → Filter missing rows.
• IS NOT NULL → Filter available rows.
• IFNULL() → Replace NULL with one default value.
• COALESCE() → Return the first available value from multiple options.
• NULLIF() → Convert a specific value (like '') into NULL.
• TRIM() → Remove leading and trailing spaces before cleaning data.

Quick Revision Table