SQL values: NULL, COALESCE, CASE and dates

Real data has holes. A guest orders without an account. A customer does not use a coupon. A salary row has no bonus yet. The interviewer asks a simple question on such data and watches what happens. Many people write a correct-looking query that quietly drops rows or gives an empty total.

A weak answer never mentions the holes, and trusts the first result. A strong answer asks, "Can this column be empty?" before writing a line, and tests the query on a row that has a hole. The same is true for dates: the strong answer says which days are included.

At SDE-1 level you should handle empty values and write a simple label rule. At SDE-2 level you should also do date arithmetic, and say how each rule treats the first and last day.

Think of a paper form with the line "Coupon amount: ____". If the customer left it blank, we do not know if the coupon was Rs 0 or if the clerk forgot. A blank is not a zero. It is "no value here".

In SQL this is called NULL. A NULL is a marker for a missing or unknown value. It is not the number 0 and it is not the empty text ''. Here are three places where NULLs come from:

• A guest order has no customer. Its customer_id is NULL.
• An optional field was never filled. Most orders have no coupon, so their coupon_off is NULL.
• A value is not known yet, such as a delivery time of an order that is still on its way.

Here are the first 10 orders. Look at the empty values in customer_id and coupon_off. Order 8 is a guest order.

SELECT id, customer_id, amount, coupon_off FROM orders WHERE id <= 10;

id  customer_id  amount  coupon_off
--  -----------  ------  ----------
 1            1     440        NULL
 2            2     200        NULL
 3            3     360          50
 4            1     440        NULL
 5            4     600        NULL
 6            5     340        NULL
 7            2     400         100
 8         NULL     210        NULL
 9            3     300        NULL
10            1     640          50

A comparison with NULL is unknown

What is coupon_off = 50 for order 1? We do not know the coupon, so the answer is not true and not false. It is unknown. The same is true for coupon_off = NULL, coupon_off <> 50, and even NULL = NULL. SQL has three answers: true, false and unknown.

WHERE keeps only the rows for which the test is true. A row with an unknown answer is dropped, without any message. To test for a missing value, use IS NULL and IS NOT NULL.

SELECT id, coupon_off FROM orders WHERE id <= 4 AND coupon_off = NULL;

id  coupon_off
--  ----------
(no rows)

SELECT id, coupon_off FROM orders WHERE id <= 4 AND coupon_off IS NULL;

id  coupon_off
--  ----------
 1  NULL
 2  NULL
 4  NULL

The first query returns nothing, even though orders 1, 2 and 4 have no coupon. The second works.

Now the quiet loss of rows. We ask for orders where the coupon is not zero:

SELECT id, coupon_off FROM orders WHERE id <= 10 AND coupon_off <> 0;

id  coupon_off
--  ----------
 3          50
 7         100
10          50

Only the 3 orders with a coupon come back. The 7 with no coupon have an unknown answer, so they vanish. If you want them, say so: coupon_off <> 0 OR coupon_off IS NULL.

How do AND, OR and NOT work with unknown? Think: can the answer still change?

NOT unknown is unknown. In short: false AND anything is false, and true OR anything is true. Otherwise the result stays unknown. SQLite writes true as 1, false as 0 and unknown as NULL.

SELECT 1 AND NULL AS true_and_unknown, 0 AND NULL AS false_and_unknown, 1 OR NULL AS true_or_unknown, 0 OR NULL AS false_or_unknown, NOT NULL AS not_unknown;

true_and_unknown  false_and_unknown  true_or_unknown  false_or_unknown  not_unknown
----------------  -----------------  ---------------  ----------------  -----------
NULL                              0                1  NULL              NULL

NULL spreads

• Arithmetic: 5 + NULL is NULL. So amount - coupon_off is NULL for every order without a coupon.
• Text: 'Order ' || NULL is NULL.
• ORDER BY: SQLite puts NULL first when you sort smallest first, and last when you sort biggest first. You can say it with NULLS FIRST or NULLS LAST.
• DISTINCT: all NULLs count as one value.
• A written list: NOT IN is the opposite of IN, and x NOT IN (1, 2, NULL) is never true. For a row with x = 3, the test 3 <> NULL is unknown, so the whole test is unknown. A NULL inside a NOT IN list silently returns nothing.

SELECT DISTINCT coupon_off FROM orders ORDER BY coupon_off DESC NULLS LAST;

coupon_off
----------
       100
        50
      NULL

SELECT id FROM orders WHERE id <= 10 AND customer_id NOT IN (1, 2, NULL);

id
--
(no rows)

COALESCE, IFNULL and NULLIF

COALESCE(a, b, c) returns the first value that is not NULL. It is the standard way to give a default. IFNULL(a, b) does the same with two values. SQLite and MySQL have it; Postgres does not.

NULLIF(a, b) does the opposite. It returns NULL when a equals b, and a otherwise. It turns a "bad" value into NULL. A common use is x / NULLIF(y, 0), which gives NULL instead of an error when y is 0. SQLite already gives NULL for a division by zero, but Postgres stops with an error, so the safe form is good habit.

SELECT id, amount, coupon_off, amount - COALESCE(coupon_off, 0) AS to_pay FROM orders WHERE id <= 4;

id  amount  coupon_off  to_pay
--  ------  ----------  ------
 1     440        NULL     440
 2     200        NULL     200
 3     360          50     310
 4     440        NULL     440

SELECT 5 / 0 AS plain, 5 / NULLIF(0, 0) AS safe;

plain  safe
-----  ----
NULL   NULL

CASE: choose a value by rules

CASE WHEN test THEN value ... ELSE value END checks the tests from top to bottom. The first true test wins. If no test is true and there is no ELSE, the result is NULL. Use it for labels, bands, and a 1 or 0 flag. You can also use it in ORDER BY.

SELECT id, amount,
  CASE WHEN amount < 250 THEN 'small'
       WHEN amount < 450 THEN 'medium'
       ELSE 'big' END AS size,
  CASE WHEN coupon_off IS NULL THEN 0 ELSE 1 END AS used_coupon
FROM orders WHERE id <= 6;

id  amount  size    used_coupon
--  ------  ------  -----------
 1     440  medium            0
 2     200  small             0
 3     360  medium            1
 4     440  medium            0
 5     600  big               0
 6     340  medium            0

Because the first true test wins, the order of the tests matters. Put the smallest limit first.

Dates in SQLite

You know from lesson 42 that a date is text such as 2026-03-31. Text in this form sorts and compares in date order. These functions work on it:

• date(x) cuts away the time. datetime(x) gives date and time.
• strftime(format, x) builds text from parts. %Y-%m is year and month. %w is the weekday number, where Sunday is 0.
• julianday(x) gives a day count. The difference of two of them is the number of days.
• strftime('%s', x) gives the number of seconds since 1 January 1970, as text. Use CAST(... AS INTEGER) to calculate with it. The difference of two of them is exact seconds, with no decimals.
• Modifiers go after the date: '+7 days', '-1 month', 'start of month'.

SELECT id, ordered_at, date(ordered_at) AS day, strftime('%Y-%m', ordered_at) AS month, strftime('%w', ordered_at) AS weekday,
  date(ordered_at, '+7 days') AS week_later, date(ordered_at, 'start of month', '+1 month') AS next_month
FROM orders WHERE id IN (1, 14);

id  ordered_at           day         month    weekday  week_later  next_month
--  -------------------  ----------  -------  -------  ----------  ----------
 1  2026-03-02 13:10:00  2026-03-02  2026-03  1        2026-03-09  2026-04-01
14  2026-03-31 18:00:00  2026-03-31  2026-03  2        2026-04-07  2026-04-01

The first day of the week is a choice. Many countries start on Monday. The number %w counts Sunday as 0. In an interview, say which day you use.

This trick finds the Monday of the week of a date. The modifier 'weekday 1' moves the date forward to the next Monday, and keeps the date if it is already a Monday. That alone is not enough, because it would jump to the next week for a Tuesday. So we first go back 6 days. Then the date is on or before the Monday of its own week, and 'weekday 1' lands on that Monday. The three dates below are a Wednesday, a Monday and a Sunday.

SELECT date('2026-03-04', '-6 days', 'weekday 1') AS monday_of_wed,
       date('2026-03-02', '-6 days', 'weekday 1') AS monday_of_mon,
       date('2026-03-08', '-6 days', 'weekday 1') AS monday_of_sun;

monday_of_wed  monday_of_mon  monday_of_sun
-------------  -------------  -------------
2026-03-02     2026-03-02     2026-03-02

Days between two dates: use julianday, and cut the time first, or you get a fraction. For exact seconds use strftime('%s'). The query below is the gap between 10:00 and 10:30, and it is exactly 1,800 seconds.

SELECT CAST(strftime('%s', '2026-03-10 10:30:00') AS INTEGER) - CAST(strftime('%s', '2026-03-10 10:00:00') AS INTEGER) AS seconds;

seconds
-------
   1800

SELECT julianday('2026-03-31 18:00:00') - julianday('2026-03-30') AS with_time,
       julianday(date('2026-03-31 18:00:00')) - julianday('2026-03-30') AS day_only;

with_time  day_only
---------  --------
     1.75       1.0

Edge rules.

• If the first and the last day both count, the number of days is the difference plus 1. A coupon valid from 10 March to 16 March is 6 + 1 = 7 days.

SELECT CAST(julianday('2026-03-16') - julianday('2026-03-10') AS INTEGER) AS difference, CAST(julianday('2026-03-16') - julianday('2026-03-10') AS INTEGER) + 1 AS days_counted;

difference  days_counted
----------  ------------
         6             7

• A date with a time is later than the same date without time. Use "from the day, up to but not including the next day", as in lesson 42.

In Postgres and MySQL

• Dates: Postgres uses DATE_TRUNC('month', x), EXTRACT(...) and x + INTERVAL '7 days'. MySQL uses DATE_FORMAT, DATE_ADD(x, INTERVAL 7 DAY) and DATEDIFF(a, b). In Postgres, date - date gives days.
• Empty values: Postgres sorts NULL last for smallest first. MySQL and SQLite sort it first. MySQL has no NULLS LAST; write ORDER BY x IS NULL, x.
• Postgres has COALESCE and NULLIF but no IFNULL. MySQL has all three.
• Division by zero: Postgres stops with an error. SQLite and MySQL return NULL in a plain query.

The Tiffin Town owner asks: "How much does each customer pay after the coupon? Label the orders. How long has each customer been with us?"

Part 1: the amount after the coupon. First the naive version.

SELECT id, amount, coupon_off, amount - coupon_off AS to_pay FROM orders WHERE id <= 10;

id  amount  coupon_off  to_pay
--  ------  ----------  ------
 1     440        NULL    NULL
 2     200        NULL    NULL
 3     360          50     310
 4     440        NULL    NULL
 5     600        NULL    NULL
 6     340        NULL    NULL
 7     400         100     300
 8     210        NULL    NULL
 9     300        NULL    NULL
10     640          50     590

Seven of the ten to_pay values are NULL. A report built on this would lose most of its money. This also answers the predict box: coupon_off <> 0 keeps 3 rows. The 7 other rows are unknown and are dropped. With COALESCE the total is right:

SELECT id, amount, coupon_off, amount - COALESCE(coupon_off, 0) AS to_pay FROM orders WHERE id <= 10;

id  amount  coupon_off  to_pay
--  ------  ----------  ------
 1     440        NULL     440
 2     200        NULL     200
 3     360          50     310
 4     440        NULL     440
 5     600        NULL     600
 6     340        NULL     340
 7     400         100     300
 8     210        NULL     210
 9     300        NULL     300
10     640          50     590

Part 2: labels. We label by to_pay, and we sort delivered first, then placed, then cancelled. A CASE in ORDER BY is how you sort in your own order. Cancelled orders are not paid.

SELECT id, status, amount - COALESCE(coupon_off, 0) AS to_pay,
  CASE WHEN status = 'cancelled' THEN 'no payment'
       WHEN amount - COALESCE(coupon_off, 0) >= 400 THEN 'big'
       ELSE 'normal' END AS label
FROM orders WHERE id IN (3, 5, 7, 10, 20)
ORDER BY CASE status WHEN 'delivered' THEN 1 WHEN 'placed' THEN 2 ELSE 3 END, id;

id  status     to_pay  label
--  ---------  ------  ----------
 3  delivered     310  normal
 7  delivered     300  normal
10  delivered     590  big
20  delivered     600  big
 5  cancelled     600  no payment

Part 3: days with us. We count the days from joined_on to 31 March 2026. Both dates are plain dates, so the difference is a whole number.

SELECT name, joined_on, CAST(julianday('2026-03-31') - julianday(joined_on) AS INTEGER) AS days_with_us
FROM customers WHERE city = 'Delhi' ORDER BY days_with_us DESC;

name   joined_on   days_with_us
-----  ----------  ------------
Asha   2026-01-05            85
Karan  2026-02-14            45
Dev    2026-03-15            16

Check by hand. Asha joined on 5 January. January has 31 days, so 5 January to 31 January is 26 days. Add 28 days of February and 31 March: 26 + 28 + 31 = 85. This agrees with the result. If Asha counted her first day too, the number would be 86.

• It solves: answers that stay correct when data has holes, and clear rules for labels and days.
• It costs: more words in each query (COALESCE, IS NULL) and a decision for every column: what does an empty value mean here? A default of 0 is not always right. For an average, a 0 would pull the number down.
• I would change it when: I own the table design. Then I make a column NOT NULL with a real default, when an empty value has no meaning. A column that must have a value cannot be empty by mistake.
• Always test on a row that has the hole.

"NULL is the same as 0 or an empty text." It is not. It means unknown. NULL = 0 is unknown, and NULL || 'a' is NULL, while '' || 'a' is 'a'.

"WHERE x <> 5 keeps every row where x is not 5." It drops the rows where x is NULL. A NULL is not "not 5". It is unknown.

"NOT IN (1, 2, NULL) ignores the NULL." It does not. Every row becomes unknown, so the result is empty. Remove the NULL from the list, or use another form.

1. What does NULL = NULL return? Write the test that finds the rows with no coupon.
2. A coupon is valid from 1 April to 30 April, both days included. How many days is that? Write the date of the day after the last day, from '2026-04-30', with a date modifier.
3. A teammate shows the coupon as a share of the amount: 100.0 * coupon_off / amount. For an order with no coupon the result is NULL, and the report looks broken. What do you change, and when would you not change it?