SQL Programming Resources on Telegram: “SELECT customer

SELECT
    customer_id,
    COALESCE(SUM(amount), 0) AS total_spending
FROM transactions
GROUP BY customer_id;

This makes reports easier to interpret.

🧮 9. NULL and COUNT()

These two queries behave differently:

SELECT COUNT(*)
FROM customers;

Counts all rows.

While:

SELECT COUNT(phone)
FROM customers;

Counts only rows where "phone" is not NULL.

Example:

customer | phone
A        | 12345
B        | NULL
C        | 67890

COUNT(*)     → 3
COUNT(phone) → 2

This difference is frequently tested in interviews.

📈 10. NULL and SUM(), AVG(), MIN(), MAX()

Most aggregate functions ignore NULL values.

Example:

Salary
50000
60000
NULL
70000

Then:

SELECT AVG(salary)
FROM employees;

The NULL salary is generally ignored.

So the average is calculated using:

50000, 60000, 70000

not four values.

Important:

• COUNT(*) counts rows.

• COUNT(column) ignores NULL.

• SUM(), AVG(), MIN(), and MAX() generally ignore NULL values.

🔄 11. NULL with CASE

NULL can be handled using CASE.

SELECT
    customer_name,
    CASE
        WHEN phone IS NULL THEN 'Missing'
        ELSE 'Available'
    END AS phone_status
FROM customers;

Result:

customer | phone_status
Alice    | Available
Bob      | Missing
Charlie  | Available

🧹 12. Handling NULL in Data Cleaning

Suppose customer cities contain missing values.

SELECT
    customer_name,
    COALESCE(city, 'Unknown') AS city
FROM customers;

This can make reports more readable.

But be careful:

Replacing NULL does not mean the original data wasn't missing.

For analysis, it may still be important to track missingness.

🧨 13. NULLIF()

NULLIF() returns NULL when two expressions are equal.

Syntax:

NULLIF(value1, value2)

Example:

SELECT NULLIF(10, 10);

Result:

NULL

But:

SELECT NULLIF(10, 5);

Result:

🚨 14. NULLIF() for Division by Zero

This is one of the most useful real-world applications.

Suppose:

SELECT
    revenue / orders AS revenue_per_order
FROM sales;

If "orders = 0", some database systems will raise a division-by-zero error.

Use:

SELECT
    revenue / NULLIF(orders, 0) AS revenue_per_order
FROM sales;

If:

orders = 0

then:

NULLIF(orders, 0)

returns:

NULL

So the calculation becomes:

revenue / NULL

and returns NULL instead of attempting division by zero.

You can then provide a fallback:

SELECT
    COALESCE(
        revenue / NULLIF(orders, 0),
        0
    ) AS revenue_per_order
FROM sales;

This combines:

• NULLIF → prevent invalid division

• COALESCE → provide fallback value

🔗 15. NULL in JOINs

NULL becomes especially important with joins.

Suppose:

customers

contains all customers, while:

orders

contains only customers who placed orders.

Using: