If you’ve written SQL for a while, you’ve likely bumped into COALESCE and ISNULL. Both are handy for dealing with NULL values in your tables, swapping them out for something useful. They might seem interchangeable at first, but they’ve got some sneaky differences that can change your results—or even break your query—if you’re not careful. Let’s dig into how they work with real table data, using an Orders table to keep things consistent, so you can see exactly what’s going on.
Setting the Scene: Our Orders Table
To make this relatable, let’s imagine an Orders table with some typical columns: an order ID, a price, and a discount that’s sometimes NULL. Here’s how we’ll set it up:
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
Price DECIMAL(10,2),
Discount DECIMAL(5,2) NULL
);
INSERT INTO Orders (OrderID, Price, Discount)
VALUES (1, 100.50, 10.00), (2, 200.75, NULL), (3, 150.25, 5.50);So, we’ve got three orders: Order 1 has a $10 discount, Order 2 has no discount (NULL), and Order 3 has a $5.50 discount. This gives us a solid base to compare ISNULL and COALESCE as we work with these values.
The Basics: Swapping Out NULLs
Both ISNULL and COALESCE are about handling NULLs in your data by giving you a fallback. Let’s start with ISNULL. Say we want the discount for each order, but NULL isn’t helpful—we’d rather see zero:
SELECT OrderID, ISNULL(Discount, 0) AS DiscountValue FROM Orders;
Here’s what we get:
OrderID DiscountValue ------- ------------- 1 10.00 2 0.00 3 5.50
ISNULL takes one value that might be NULL—our Discount column—and replaces it with a fallback, 0, if it’s missing. Simple and clean.
Now, let’s try COALESCE:
SELECT OrderID, COALESCE(Discount, 0) AS DiscountValue FROM Orders;
The output is the same:
OrderID DiscountValue ------- ------------- 1 10.00 2 0.00 3 5.50
So far, they’re neck and neck. Both grab the Discount and return 0 when it’s NULL. But don’t be fooled—things get interesting when we push them further.
Difference #1: One Value vs. a Chain of Options
Here’s where they split apart. ISNULL is all about one column or value that might be NULL, paired with a single replacement. It’s a straight swap. But COALESCE lets you chain multiple options, picking the first one that isn’t NULL.
Let’s say we’ve got a backup plan—if Discount is NULL, we’ll use 5% of the Price instead of just zero:
SELECT OrderID, COALESCE(Discount, Price * 0.05) AS DiscountValue FROM Orders;
Check the results:
OrderID DiscountValue ------- ------------- 1 10.00 2 10.04 -- 5% of 200.75 3 5.50
For Order 2, where Discount is NULL, COALESCE moves to Price * 0.05 and calculates it on the fly. You can’t do that with ISNULL—it’s one-and-done:
SELECT OrderID, ISNULL(Discount, Price * 0.05) AS DiscountValue FROM Orders;
Same output again:
OrderID DiscountValue ------- ------------- 1 10.00 2 10.04 3 5.50
But if you needed a third fallback—like a flat $2 if both Discount and the 5% calc were NULL—ISNULL can’t help. COALESCE can:
SELECT OrderID, COALESCE(Discount, Price * 0.05, 2.00) AS DiscountValue FROM Orders;
Still the same results here, because Price * 0.05 works for Order 2, but you get the idea—COALESCE keeps going down the list.
Difference #2: Data Type Drama
Now let’s talk data types, because this is where surprises sneak in. ISNULL locks the result to the replacement value’s type, which can bite you if your column’s type doesn’t match. Suppose we use an integer 0 as the fallback:
SELECT OrderID, ISNULL(Discount, 0) AS DiscountValue FROM Orders;
Looks fine:
OrderID DiscountValue ------- ------------- 1 10.00 2 0.00 3 5.50
But what if the replacement isn’t a tidy DECIMAL? Let’s declare a variable to show the problem:
DECLARE @Fallback INT = 0; SELECT OrderID, ISNULL(Discount, @Fallback) AS DiscountValue FROM Orders;
This still works:
OrderID DiscountValue ------- ------------- 1 10.00 2 0.00 3 5.50
SQL Server quietly converts the INT to DECIMAL to match Discount. But flip it around—make the column an INT and the replacement a DECIMAL:
ALTER TABLE Orders ADD IntDiscount INT; UPDATE Orders SET IntDiscount = CAST(Discount AS INT); -- 10, NULL, 5 SELECT OrderID, ISNULL(IntDiscount, 10.5) AS DiscountValue FROM Orders;
Now you get:
OrderID DiscountValue ------- ------------- 1 10 2 10 3 5
The DECIMAL 10.5 gets chopped to an INT because IntDiscount is INT, and ISNULL sticks to the column’s type. COALESCE handles this differently—it picks the highest-precedence type from the list:
SELECT OrderID, COALESCE(IntDiscount, 10.5) AS DiscountValue FROM Orders;
Here’s the output:
OrderID DiscountValue ------- ------------- 1 10.0 2 10.5 3 5.0
COALESCE goes with DECIMAL because 10.5 is higher than INT, keeping those decimals intact. Mix types the wrong way with ISNULL, and you might lose precision or hit errors.
Difference #3: Playing in Expressions
Let’s see how they behave inside calculations, like figuring out the final price after a discount. Try this with ISNULL:
SELECT OrderID, Price - ISNULL(Discount, 0) AS FinalPrice FROM Orders;
Results:
OrderID FinalPrice ------- ---------- 1 90.50 2 200.75 3 144.75
If Discount is NULL, ISNULL swaps in 0, and the math works smoothly—SQL Server adjusts types as needed. Now with COALESCE:
SELECT OrderID, Price - COALESCE(Discount, 0) AS FinalPrice FROM Orders; Same output:
Same output:
OrderID FinalPrice ------- ---------- 1 90.50 2 200.75 3 144.75
No difference here, but COALESCE shines when you add more fallbacks. Say we want Price * 0.05 if Discount is NULL:
SELECT OrderID, Price - COALESCE(Discount, Price * 0.05) AS FinalPrice FROM Orders;
Now:
OrderID FinalPrice ------- ---------- 1 90.50 2 190.71 -- 200.75 - 10.04 3 144.75
ISNULL can do one swap, but COALESCE lets you layer options without extra steps.
Difference #4: Handling Subqueries
Here’s a trickier one—using subqueries as fallbacks. Suppose we want a discount from a Discounts table if our Orders.Discount is NULL:
CREATE TABLE Discounts (OrderID INT, Discount DECIMAL(5,2)); INSERT INTO Discounts VALUES (2, 8.00); -- Only Order 2 has a discount here With ISNULL:
With ISNULL:
SELECT o.OrderID, ISNULL(o.Discount, (SELECT TOP 1 Discount FROM Discounts d WHERE d.OrderID = o.OrderID)) AS DiscountValue FROM Orders o;
Output:
OrderID DiscountValue ------- ------------- 1 10.00 2 8.00 -- From Discounts 3 5.50
ISNULL uses one fallback—here, the subquery—and stops. Now COALESCE:
SELECT o.OrderID, COALESCE(o.Discount, (SELECT TOP 1 Discount FROM Discounts d WHERE d.OrderID = o.OrderID), 0) AS DiscountValue FROM Orders o;
Results:
OrderID DiscountValue ------- ------------- 1 10.00 2 8.00 3 5.50
COALESCE chains options—Discount, then the subquery, then 0. It evaluates each one in order, which is powerful but means more work if subqueries get complex.
When to Use Each One
So, when do you pick ISNULL over COALESCE? If you’ve got one column that might be NULL and a simple replacement—like swapping Discount for 0—ISNULL is quick and easy. If you need a chain of fallbacks, like a column, a calculation, or a subquery, COALESCE is your go-to—it’s built for that. For mixed data types, COALESCE is safer, picking the best type without losing precision, while ISNULL sticks to the column’s type and might trip over mismatches. With subqueries, COALESCE gives you flexibility, but watch those extra evaluations—ISNULL keeps it tight. Pick the one that fits your data and saves you headaches.
Next time you’re wrestling with NULLs, think about what you’re working with. It could dodge a nasty surprise.
