SQL Programming Resources on Telegram: “SELECT c.customer_id, c.customer_name, o.order

SELECT
    c.customer_id,
    c.customer_name,
    o.order_id
FROM customers c
LEFT JOIN orders o
    ON c.customer_id = o.customer_id;

Customers without orders may have:

order_id = NULL

You can identify them with:

WHERE o.order_id IS NULL;

This is a common technique for finding:

«Customers who have never placed an order.»

📦 16. NULL in GROUP BY

NULL values can also appear as a group.

Example:

SELECT
    department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY department;

If some employees have no department, the result can contain a group where:

department = NULL

You can make it more readable:

SELECT
    COALESCE(department, 'Unassigned') AS department,
    COUNT(*) AS employee_count
FROM employees
GROUP BY COALESCE(department, 'Unassigned');

↕️ 17. NULL and ORDER BY

NULL sorting behavior can differ between database systems.

For example:

SELECT *
FROM employees
ORDER BY salary DESC;

Depending on the database, NULL values may appear at the beginning or end.

Some systems support:

ORDER BY salary DESC NULLS LAST;

Always check the SQL dialect you're using when NULL ordering matters.

🧠 18. NULL vs Empty String

These are not necessarily the same:

NULL
''

"NULL" means:

«No known value.»

An empty string means:

«A string exists but contains no characters.»

For example:

phone = ''

is different from:

phone IS NULL

This distinction matters during data cleaning.

🏢 19. Real-World Analytics Example

Imagine an e-commerce dataset:

order_id | revenue | discount | shipping_cost
1        | 2000    | 200      | 100
2        | 1500    | NULL     | 80
3        | 3000    | 300      | NULL

Calculate profit safely:

SELECT
    order_id,
    revenue,
    COALESCE(discount, 0) AS discount,
    COALESCE(shipping_cost, 0) AS shipping_cost,
    revenue
        - COALESCE(discount, 0)
        - COALESCE(shipping_cost, 0) AS net_revenue
FROM orders;

This prevents missing values from turning the entire calculation into NULL.

🎯 20. Business KPI Example — Conversion Rate

Suppose:

conversions = 50
visitors = 0

A safe calculation is:

SELECT
    COALESCE(
        conversions * 100.0 / NULLIF(visitors, 0),
        0
    ) AS conversion_rate
FROM marketing;

The logic is:

NULLIF(visitors, 0)
        ↓
Prevents division by zero
        ↓
Returns NULL if visitors = 0
        ↓
COALESCE(..., 0)
        ↓
Displays 0 instead of NULL

This pattern is highly useful for KPI dashboards.

⚠️ Common NULL Mistakes

Mistake 1:

WHERE salary = NULL;

❌ Incorrect

Use:

WHERE salary IS NULL;

Mistake 2:

Assuming NULL means zero.

NULL ≠ 0

Mistake 3:

Ignoring NULL during calculations.

price - discount

may produce NULL when discount is NULL.

Consider:

price - COALESCE(discount, 0)