What are Conditional Functions?
COALESCE Example
| name | phone (original) | phone (with COALESCE) |
|---|---|---|
| John | 555-0001 | 555-0001 |
| Mary | NULL | No phone |
| Peter | NULL | No phone |
3 rows
Functions that make decisions based on conditions - like IF statements.
Simple analogy: Like choosing what to wear based on weather (if rainy, take umbrella).
CASE - Multiple Conditions
COALESCE Example
| name | phone (original) | phone (with COALESCE) |
|---|---|---|
| John | 555-0001 | 555-0001 |
| Mary | NULL | No phone |
| Peter | NULL | No phone |
3 rows
Like if-else in programming.
Example 1: Grade Categories
CASE Function Example
| name | score | grade (using CASE) |
|---|---|---|
| John | 95 | A |
| Mary | 85 | B |
| Peter | 75 | C |
| Sarah | 65 | F |
4 rows
SELECT
name,
score,
CASE
WHEN score >= 90 THEN 'A'
WHEN score >= 80 THEN 'B'
WHEN score >= 70 THEN 'C'
ELSE 'F'
END AS grade
FROM students;What happens:
- If score is 95, grade = A
- If score is 85, grade = B
- If score is 75, grade = C
- Otherwise, grade = F
Example 2: Price Categories
SELECT
product_name,
price,
CASE
WHEN price > 100 THEN 'Expensive'
WHEN price > 50 THEN 'Medium'
ELSE 'Cheap'
END AS price_category
FROM products;Example 3: Age Groups
SELECT
name,
age,
CASE
WHEN age < 18 THEN 'Minor'
WHEN age < 65 THEN 'Adult'
ELSE 'Senior'
END AS age_group
FROM users;COALESCE - Replace NULL
COALESCE Example
| name | phone (original) | phone (with COALESCE) |
|---|---|---|
| John | 555-0001 | 555-0001 |
| Mary | NULL | No phone |
| Peter | NULL | No phone |
3 rows
Returns first non-NULL value.
SELECT
name,
COALESCE(phone, 'No phone') AS contact
FROM users;What happens:
- If phone exists: shows phone
- If phone is NULL: shows 'No phone'
Example: Default Values
SELECT
product_name,
COALESCE(discount, 0) AS discount
FROM products;Shows 0 if discount is NULL.
NULLIF - Make NULL if Equal
COALESCE Example
| name | phone (original) | phone (with COALESCE) |
|---|---|---|
| John | 555-0001 | 555-0001 |
| Mary | NULL | No phone |
| Peter | NULL | No phone |
3 rows
Returns NULL if two values are equal.
SELECT NULLIF(status, 'unknown') FROM orders;What happens:
- If status is 'unknown': returns NULL
- Otherwise: returns status
Real Examples
COALESCE Example
| name | phone (original) | phone (with COALESCE) |
|---|---|---|
| John | 555-0001 | 555-0001 |
| Mary | NULL | No phone |
| Peter | NULL | No phone |
3 rows
Example 1: Customer Status
SELECT
name,
CASE
WHEN total_orders > 10 THEN 'VIP'
WHEN total_orders > 5 THEN 'Regular'
ELSE 'New'
END AS customer_type
FROM customers;Example 2: Stock Status
SELECT
product_name,
stock,
CASE
WHEN stock = 0 THEN 'Out of Stock'
WHEN stock < 10 THEN 'Low Stock'
ELSE 'In Stock'
END AS status
FROM products;Example 3: Handle Missing Data
SELECT
name,
COALESCE(email, 'no-email@example.com') AS email,
COALESCE(phone, 'No phone provided') AS phone
FROM contacts;Summary
COALESCE Example
| name | phone (original) | phone (with COALESCE) |
|---|---|---|
| John | 555-0001 | 555-0001 |
| Mary | NULL | No phone |
| Peter | NULL | No phone |
3 rows
Functions:
- CASE: Like if-else, make decisions
- COALESCE: Replace NULL with default
- NULLIF: Turn value into NULL