COALESCE

COALESCE is the SQL version of a spare key: when one value is missing, it reaches for the next one.

Question: What does COALESCE do in SQL?

Answer: COALESCE returns the first value in a list that is not NULL. If every value is NULL, the result is NULL. It is one of the simplest ways to provide a fallback display value, a fallback number, or a fallback filter value.

Interview-Ready Answer: I use COALESCE to pick the first non-NULL expression from left to right. It is standard SQL, so it works across databases, and it is great for fallbacks like COALESCE(nickname, full_name, 'guest'). One important detail is that if all inputs are NULL, the result is still NULL.

What it is

COALESCE is a scalar function, which means it works on one row at a time and returns one value. The name is easy to remember: it is a fallback chooser for missing data. In SQL, NULL means unknown or missing, not zero and not an empty string.

How it works under the hood

1. The database evaluates the first expression.
2. If that value is not NULL, it returns it immediately.
3. If it is NULL, the database checks the next expression.
4. This continues left to right until it finds a non-NULL value.
5. If every expression is NULL, the result is NULL.

This left-to-right behavior is the mental model to keep in your head. A good shorthand is: first non-null wins.

When and why to use it

• Display fallbacks: show nickname, or if missing, show full_name, or if missing, show guest.
• Math safety: turn NULL numbers into zero before addition, like COALESCE(bonus, 0).
• Filtering: check the first available contact field, like COALESCE(email, phone) IS NOT NULL.
• Reporting: group missing categories under a label such as Unknown.

COALESCE vs alternatives

CASE is more verbose but very flexible. ISNULL and NVL are common in some databases, but they are not as portable as COALESCE. In interviews, saying COALESCE is standard SQL is a nice point to mention.

Performance and complexity

For each row, COALESCE checks values from left to right, so its work is roughly O(k) per row, where k is the number of arguments. In practice, k is usually small, like 2 to 4, so the cost is tiny. On a table with 1 million rows and 3 arguments, that means up to about 3 million null checks, which is usually cheap compared with joins or sorts.

The bigger performance gotcha is using COALESCE in a WHERE clause around an indexed column. Many databases cannot use a plain index efficiently when the column is wrapped in a function, because the engine has to compute the expression first. That can turn a quick index lookup into a slower table scan. If the goal is col = value OR col IS NULL, a rewritten predicate or an expression index may be better, depending on the database.

Important edge cases

• All inputs are NULL: result is NULL.
• Empty string is not always NULL: in most databases, '' is a real empty string, not NULL. Oracle is a special case where empty string behaves like NULL.
• Mixed types: databases must choose a compatible result type. Mixing text and numbers can fail or force conversion, depending on the engine.
• Do not use it for empty-string cleanup alone: if you want to treat blank strings as missing, use NULLIF(col, '') first, then wrap that with COALESCE.

Memory rule: think of COALESCE as a line of backup batteries. The first battery that still has charge powers the device; if every battery is dead, you get no power.

Real-world story

Imagine an e-commerce checkout service that builds a customer contact label from multiple sources: COALESCE(customer_phone, account_phone, support_phone). That is perfect for a support screen because customers may have different contact fields populated at different times. The service also uses COALESCE in reports to show a friendly fallback like Unknown instead of a blank cell.

Now the bug: an engineer adds COALESCE inside a filter on a 50 million row orders table, such as WHERE COALESCE(shipped_at, packed_at, created_at) > .... The query starts doing a table scan instead of using the index on shipped_at. Dashboards time out, the database CPU spikes, and logs show slow queries with huge row counts. Users do not see order updates quickly, and support agents think the system is broken even though the real issue is the query shape.

The lesson is simple: COALESCE is excellent for presentation and safe fallbacks, but in filters you must think about index use and query plans.

Follow-up & Tricky Questions

• How is COALESCE different from CASE? COALESCE is a shorter, standard way to write a common fallback pattern. CASE is more flexible when the logic is not just null handling, such as comparing ranges or multiple conditions.
• Can I use COALESCE in a WHERE clause? Yes, but be careful: it can make predicates less index-friendly if it wraps an indexed column. For performance-critical filters, rewrite the condition if needed or use an expression index where supported.
• What happens if the arguments have different data types? The database tries to find a compatible result type, but some mixes fail. A safe interview answer is that you should keep the arguments type-compatible and expect implicit conversion rules to vary by database.
• Is COALESCE the same as ISNULL or NVL? The idea is similar, but those are vendor-specific and usually only accept two arguments. COALESCE is the portable standard SQL choice and can take many arguments.
• Does COALESCE stop after the first non-NULL value? Yes, that is the core behavior. That left-to-right short-circuit idea is why it is so useful for fallback values.
• Does COALESCE treat an empty string as NULL? Usually no; empty string and NULL are different in most databases. Oracle is the main exception, so be careful when switching systems.
• Can I rely on later expressions never being evaluated? Do not build logic with side effects into SQL expressions. The safe mental model is that COALESCE returns the first non-NULL value, but you should not depend on side effects inside expressions.

Common Mistakes

• Thinking NULL means zero or blank. Correction: NULL means missing or unknown, so COALESCE only handles missing values, not every kind of empty-looking data.
• Using COALESCE blindly in filters. Correction: wrapping a column in a function can block index use; check the query plan or rewrite the predicate.
• Mixing incompatible data types. Correction: keep arguments type-compatible, especially when combining numbers, dates, and text.
• Forgetting the all-NULL case. Correction: if every argument can be missing, the result can still be NULL, so plan a final fallback if needed.

Memory Hook

Spare battery model: COALESCE is a row of backup batteries. The first one with power wins, and if none have power, the device stays off. That is the fastest way to remember left-to-right fallback.

Cheat Sheet

• COALESCE returns the first non-NULL value.
• It is standard SQL and works across databases.
• Evaluation is left to right; first non-NULL wins.
• If all inputs are NULL, the result is NULL.
• Great for display defaults, safe math, and fallback filters.
• Be careful in WHERE clauses because it can hurt index use.

Practice Tasks

• Write a query that shows COALESCE(nickname, full_name, 'guest') for a user table.
• Add a numeric column like discount and use COALESCE(discount, 0) in a calculation.
• Test an all-NULL case and confirm the result is still NULL.