NULL and Predicates: Three-Valued Logic, LIKE, IN, BETWEEN and COALESCE

Version note: The SQL (Structured Query Language) queries in this post were run on SQL Server 2025 (17.0, Cumulative Update 10); the PostgreSQL comparisons were run on PostgreSQL 18.6.

Guess First: How Many Rows Does "Equal or Not Equal" Return?

We run three counts. The first counts books whose subtitle equals NULL, the second those whose subtitle IS NULL. The third is a tautology: the subtitle either equals "Essays on Software Engineering" or does not. In a logic class this condition is true for every row; it should return all 77 books:

SELECT
    (SELECT COUNT(*) FROM dbo.books WHERE subtitle = NULL) AS eq_null,
    (SELECT COUNT(*) FROM dbo.books WHERE subtitle IS NULL) AS is_null,
    (SELECT COUNT(*) FROM dbo.books WHERE subtitle = N'Essays on Software Engineering'
                                       OR subtitle <> N'Essays on Software Engineering') AS either_way;

eq_null  is_null  either_way
-------  -------  ----------
0        71       6

(1 rows affected)

= NULL found no rows, although 71 books have no subtitle. The tautology returned 6 instead of 77. Both have the same cause: in SQL a comparison has three outcomes, not two. In this post we cover three-valued logic, how NULL spreads through expressions, COALESCE, ISNULL and NULLIF, then the LIKE, IN and BETWEEN predicates and how each relates to NULL.

Three-Valued Logic: TRUE, FALSE and UNKNOWN

NULL is not a value but a marker that a value is missing or unknown. Compare something unknown with a value and the answer is unknown too: NULL = 'x', NULL <> 'x', even NULL = NULL yield UNKNOWN. WHERE only lets TRUE rows through; FALSE and UNKNOWN are dropped alike. In the opening tautology both comparisons are UNKNOWN for the 71 books without a subtitle, and UNKNOWN OR UNKNOWN is UNKNOWN; the remaining 6 books pass.

Let's have SQL Server compute how AND and OR behave with UNKNOWN. 1 = 1 stands for TRUE, 0 = 1 for FALSE and NULL = 1 for UNKNOWN:

SELECT a.label AS a, b.label AS b,
       CASE WHEN a.v = 1 AND b.v = 1 THEN 'TRUE'
            WHEN NOT (a.v = 1 AND b.v = 1) THEN 'FALSE' ELSE 'UNKNOWN' END AS a_and_b,
       CASE WHEN a.v = 1 OR b.v = 1 THEN 'TRUE'
            WHEN NOT (a.v = 1 OR b.v = 1) THEN 'FALSE' ELSE 'UNKNOWN' END AS a_or_b
FROM (VALUES ('TRUE', 1), ('FALSE', 0), ('UNKNOWN', NULL)) AS a(label, v)
CROSS JOIN (VALUES ('TRUE', 1), ('FALSE', 0), ('UNKNOWN', NULL)) AS b(label, v)
WHERE a.label <= b.label;

a        b        a_and_b  a_or_b
-------  -------  -------  -------
TRUE     TRUE     TRUE     TRUE
TRUE     UNKNOWN  UNKNOWN  TRUE
FALSE    TRUE     FALSE    TRUE
FALSE    FALSE    FALSE    FALSE
FALSE    UNKNOWN  FALSE    UNKNOWN
UNKNOWN  UNKNOWN  UNKNOWN  UNKNOWN

(6 rows affected)

Instead of memorizing the rule, think of it this way: UNKNOWN means "TRUE or FALSE, I do not know which". FALSE AND UNKNOWN is FALSE, because whatever the unknown is, the result does not change. TRUE OR UNKNOWN is TRUE for the same reason. Whenever the result depends on the unknown, the answer is UNKNOWN. NOT UNKNOWN is UNKNOWN too, and that is where the sneakiest bug comes from. Let's write "books not in the Programming category" with NOT:

SELECT
    (SELECT COUNT(*) FROM dbo.books) AS all_books,
    (SELECT COUNT(*) FROM dbo.books WHERE category_id = 11) AS programming,
    (SELECT COUNT(*) FROM dbo.books WHERE NOT (category_id = 11)) AS not_programming;

all_books  programming  not_programming
---------  -----------  ---------------
77         9            66

(1 rows affected)

9 + 66 = 75. Two books are neither in Programming nor outside it: The Elements of Style and Meditations, which have no category. For them category_id = 11 is UNKNOWN, and so is its NOT. If a condition and its opposite do not add up to the whole table, the cause is almost always a nullable column. The right way is to handle NULL explicitly: WHERE category_id <> 11 OR category_id IS NULL returns 68. Since SQL Server 2022 you can also write it as a single predicate: WHERE category_id IS DISTINCT FROM 11 gives the same 68. IS [NOT] DISTINCT FROM is the standard comparison that treats two NULLs as equal; we will look at it in detail in post 17.

If you see SET ANSI_NULLS OFF in old code, be careful: this session setting makes = NULL work, but Microsoft states that the setting will be removed and will always be on in the future. The right tools are IS NULL and IS NOT NULL.

How NULL Spreads: Arithmetic, Concatenation, Aggregates and Sorting

NULL is contagious in expressions. An addition, multiplication or string concatenation involving NULL returns NULL. Let's build the label of customers without a city in two ways:

SELECT TOP (2) first_name + N' (' + city + N')' AS plus_concat,
       CONCAT(first_name, N' (', city, N')') AS concat_fn
FROM dbo.customers
WHERE city IS NULL
ORDER BY customer_id;

plus_concat  concat_fn
-----------  ----------
NULL         Barbara ()
NULL         Niklaus ()

(2 rows affected)

+ lost the whole text; CONCAT treats NULLs as empty strings. Aggregate functions follow another rule: they ignore NULLs:

SELECT COUNT(*) AS all_books, COUNT(page_count) AS with_pages, AVG(page_count) AS avg_pages
FROM dbo.books;

all_books  with_pages  avg_pages
---------  ----------  ---------
77         67          420

(1 rows affected)

COUNT(*) counts rows, COUNT(page_count) counts values. The average was also computed over 67 books; the 10 books with an unknown page count were not counted as zero. That is usually the desired behaviour, but whoever reports the "average" needs to know it. Aggregates in detail come in post 10. In sorting, NULL has to go somewhere, and products diverge. SQL Server puts NULLs first in ascending order:

SELECT TOP (3) book_id, title, page_count
FROM dbo.books
ORDER BY page_count, book_id;

book_id  title                       page_count
-------  --------------------------  ----------
17       Mrs Dalloway                NULL
21       The Picture of Dorian Gray  NULL
24       Beowulf                     NULL

(3 rows affected)

On PostgreSQL 18.6, SELECT v FROM (VALUES (3), (NULL), (1)) AS t(v) ORDER BY v returns 1, 3 and NULL last. The standard lets you make this choice explicitly with NULLS FIRST and NULLS LAST; PostgreSQL supports it, SQL Server does not. In T-SQL you get the same effect with ORDER BY CASE WHEN page_count IS NULL THEN 1 ELSE 0 END, page_count.

COALESCE and ISNULL

COALESCE returns the first of its arguments that is not NULL and is standard; it works the same on SQL Server, PostgreSQL and MySQL. It takes as many arguments as you like: COALESCE(NULL, NULL, N'third', N'fourth') returns third. To fill in missing data in the customer list:

SELECT TOP (4) customer_id, first_name, phone,
       COALESCE(phone, 'no phone') AS phone_text,
       COALESCE(city, N'unknown city') AS city_text
FROM dbo.customers
WHERE phone IS NULL OR city IS NULL
ORDER BY customer_id;

customer_id  first_name  phone  phone_text  city_text
-----------  ----------  -----  ----------  ----------
1            Ada         NULL   no phone    Manchester
2            Alan        NULL   no phone    Berlin
3            Grace       NULL   no phone    Istanbul
6            Donald      NULL   no phone    Berlin

(4 rows affected)

T-SQL also has ISNULL; it takes two arguments and mostly behaves like COALESCE. The difference is in the return type:

DECLARE @code VARCHAR(3) = NULL;
SELECT ISNULL(@code, 'unknown') AS with_isnull,
       COALESCE(@code, 'unknown') AS with_coalesce;

with_isnull  with_coalesce
-----------  -------------
unk          unknown

(1 rows affected)

ISNULL's result takes the type of the first argument, here VARCHAR(3); "unknown" was silently cut to "unk". COALESCE picks a type among its arguments by precedence rules and returns a result 7 long. Checking with SQL_VARIANT_PROPERTY confirms it: isnull_len is 3, coalesce_len is 7. There are two more differences: ISNULL takes only two arguments and is not portable (PostgreSQL has no function by that name; MySQL's equivalent is IFNULL). COALESCE, according to Microsoft's documentation, is turned into a CASE expression behind the scenes; if one argument is a subquery it may be evaluated more than once. Make COALESCE your default and use ISNULL where you specifically want the first argument's type preserved.

Guarding Against Division by Zero with NULLIF

NULLIF(a, b) returns NULL if the two values are equal and a otherwise. Its most common use is preventing division by zero. Let's compute the review rate per book (reviews / orders); The Mythical Man-Month has never been ordered:

SELECT b.book_id, b.title,
       COUNT(DISTINCT r.review_id) AS review_count,
       COUNT(DISTINCT oi.order_id) AS order_count,
       COUNT(DISTINCT r.review_id) * 100 / COUNT(DISTINCT oi.order_id) AS review_rate_pct
FROM dbo.books AS b
LEFT JOIN dbo.reviews AS r ON r.book_id = b.book_id
LEFT JOIN dbo.order_items AS oi ON oi.book_id = b.book_id
WHERE b.book_id IN (26, 62, 64)
GROUP BY b.book_id, b.title;

book_id  title  review_count  order_count  review_rate_pct
-------  -----  ------------  -----------  ---------------
26       Dune   5             27           18
Msg 8134, Level 16, State 1, Server b8be943222fa, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

The report died after the first row. Wrap the divisor in NULLIF(COUNT(DISTINCT oi.order_id), 0) and zero turns into NULL, and dividing by NULL gives NULL instead of an error:

book_id  title                                              review_count  order_count  review_rate_pct
-------  -------------------------------------------------  ------------  -----------  ---------------
26       Dune                                               5             27           18
62       The Mythical Man-Month                             0             0            NULL
64       Structure and Interpretation of Computer Programs  0             1            0

(3 rows affected)

"Zero orders, rate undefined" and "one order, zero reviews, rate 0 percent" now look different. Turning it into zero with COALESCE(..., 0) destroys that information; if you do it for display, do it in the outermost layer of the query. Note the 18 as well: integer division truncated 500 / 27 to 18. We will meet the trap of averages and rates with INT again in the aggregates post. The two LEFT JOINs and COUNT(DISTINCT ...) in the query are deliberate: we will examine in post 9 how joining two one-to-many relationships in the same query multiplies rows.

LIKE: Patterns, Escaping and the Leading %

LIKE knows two wildcards: % for zero or more characters, _ for exactly one character. Books that start with "The" and contain "of":

SELECT book_id, title
FROM dbo.books
WHERE title LIKE N'The %'
  AND title LIKE N'%of%'
ORDER BY book_id;

book_id  title
-------  ---------------------------------------
21       The Picture of Dorian Gray
30       The Left Hand of Darkness
32       The War of the Worlds
40       The Fellowship of the Ring
44       The Colour of Magic
45       The Hound of the Baskervilles
55       The Structure of Scientific Revolutions
65       The Art of Computer Programming
72       The Elements of Style

(9 rows affected)

To search for a value containing an apostrophe, write it twice inside the string: LIKE N'%''s%' finds The Handmaid's Tale, Alice's Adventures in Wonderland and Charlotte's Web. Searching for the wildcard itself is subtler. Campaign codes contain an underscore:

SELECT code,
       CASE WHEN code LIKE 'SUMMER_10' THEN 'match' ELSE '-' END AS plain_underscore,
       CASE WHEN code LIKE 'SUMMER\_10' ESCAPE '\' THEN 'match' ELSE '-' END AS escaped_underscore
FROM (VALUES ('SUMMER_10'), ('SUMMERX10'), ('SUMMER10')) AS c(code);

code       plain_underscore  escaped_underscore
---------  ----------------  ------------------
SUMMER_10  match             match
SUMMERX10  match             -
SUMMER10   -                 -

(3 rows affected)

Because an unescaped _ accepts any character, SUMMERX10 matched too. ESCAPE is standard; this query works the same on PostgreSQL. On top of that, T-SQL recognises character ranges in square brackets: LIKE N'[A-C]%' returns titles starting with A, B or C. That is not standard; PostgreSQL uses SIMILAR TO or regex for the same job. If you put a search string coming from the application into LIKE without escaping its %, _ and [ characters, the user ends up using wildcards without knowing it.

On the performance side the rule is: if the start of the pattern is fixed, an index can be used; if it starts with a wildcard, it cannot. Let's put an index on title and look at the plans of two queries:

CREATE INDEX IX_books_title ON dbo.books (title);
GO
SET SHOWPLAN_TEXT ON;
GO
SELECT book_id, title FROM dbo.books WHERE title LIKE N'Dune%';
SELECT book_id, title FROM dbo.books WHERE title LIKE N'%Dune%';
GO
SET SHOWPLAN_TEXT OFF;

  |--Index Seek(OBJECT:([sqlt_06].[dbo].[books].[IX_books_title]), SEEK:([sqlt_06].[dbo].[books].[title] >= N'Dune' AND [sqlt_06].[dbo].[books].[title] < N'DunF'),  WHERE:([sqlt_06].[dbo].[books].[title] like N'Dune%') ORDERED FORWARD)

  |--Index Scan(OBJECT:([sqlt_06].[dbo].[books].[IX_books_title]),  WHERE:([sqlt_06].[dbo].[books].[title] like N'%Dune%'))

The first query turned LIKE N'Dune%' into a range (>= N'Dune' AND < N'DunF') and goes only to that range of the index: Index Seek. The second has to look inside every title: Index Scan. On 77 books the difference is 2 versus 3 logical reads; on a 40,000-book catalog it means every search reads the whole index. If you really need "contains" search, the answer is full-text search or a separate search engine, which is outside the scope of this series.

IN Lists

IN asks whether a value is in a list and is shorthand for an OR chain. Orders not yet delivered:

SELECT status, COUNT(*) AS order_count
FROM dbo.orders
WHERE status IN ('Pending', 'Paid', 'Shipped')
GROUP BY status
ORDER BY status;

status   order_count
-------  -----------
Paid     1
Pending  1
Shipped  8

(3 rows affected)

Since IN is an OR chain, it inherits the NULL rules. NOT IN is an AND chain, and a single NULL in the list wipes out the entire result:

SELECT
    (SELECT COUNT(*) FROM dbo.categories WHERE category_id NOT IN (1, 8)) AS not_in_values,
    (SELECT COUNT(*) FROM dbo.categories WHERE category_id NOT IN (1, 8, NULL)) AS not_in_with_null;

not_in_values  not_in_with_null
-------------  ----------------
11             0

(1 rows affected)

category_id NOT IN (1, 8, NULL) is really category_id <> 1 AND category_id <> 8 AND category_id <> NULL; the last part is UNKNOWN for every row, so the whole expression can never be TRUE. You would not put NULL into a hand-written list, but this is exactly what happens when the list comes from a subquery whose column is nullable. We will see this trap and its fix with NOT EXISTS in post 11. A note on long IN lists coming from the application, too: each list of a different length is a separate query text and a separate plan; we saw ANSI (American National Standards Institute) strings and the TVP (Table-Valued Parameter) alternative in the Dapper series post "Dapper ve SQL Server: TVP, ANSI String'ler, {=literal} ve Parameter Sniffing".

BETWEEN and Date Ranges

BETWEEN a AND b means >= a AND <= b; both ends are included. It causes no trouble with numbers: list_price BETWEEN 400 AND 500 finds 17 books. If you write the bounds the wrong way round (BETWEEN 500 AND 400) you get no error, just zero rows. The real trap is with date and time. Let's count the orders of March 2025:

SELECT
    (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at BETWEEN '2025-03-01' AND '2025-03-31') AS with_between,
    (SELECT COUNT(*) FROM dbo.orders WHERE ordered_at >= '2025-03-01' AND ordered_at < '2025-04-01') AS half_open;

with_between  half_open
------------  ---------
25            28

(1 rows affected)

Three orders disappeared. ordered_at is a DATETIME2, and the literal '2025-03-31' means 00:00:00 on March 31. Every order placed after 00:00 that day fell outside the upper bound:

SELECT order_id, ordered_at
FROM dbo.orders
WHERE ordered_at >= '2025-03-31' AND ordered_at < '2025-04-01'
ORDER BY ordered_at;

order_id  ordered_at
--------  -------------------
54        2025-03-31 12:01:22
55        2025-03-31 13:52:21
56        2025-03-31 21:48:08

(3 rows affected)

The right form is a half-open range: the lower bound included (>=), the upper bound excluded (<), and the upper bound is the start of the next period. This form works regardless of the column's precision: DATE, DATETIME2(0) or DATETIME2(7) makes no difference, there is no need to invent a value like "23:59:59.999", and it does not trip over the DATETIME rounding we saw in post 3. Consecutive periods do not overlap either: March's upper bound is April's lower bound.

In Production

For every query that filters a nullable column, answer the question "should rows with NULL be in the result?" explicitly and write the answer into the query. When "total" and "sum of the parts" do not match in a report, the first suspect is a nullable column combined with NOT, <> or NOT IN. Whether a column may be NULL should be a business decision, not a default; making every column that can be NOT NULL NOT NULL closes most of the traps in this post up front. On the Entity Framework Core (EF Core) side, EF Core does the translation between C#'s two-valued logic and SQL's three-valued logic and generates extra IS NULL checks when comparing nullable columns; when you read the SQL it produces, you now know where they come from.

Common Pitfalls

• Writing WHERE column = NULL. It always returns zero rows; the right form is IS NULL.
• Forgetting that a filter written with NOT or <> also drops the NULL rows of a nullable column.
• Concatenating strings with + and having a single NULL turn the whole result into NULL; use CONCAT or COALESCE.
• ISNULL truncating the result to the type of its first argument.
• Not guarding the divisor with NULLIF(..., 0) and having the report die at the first zero.
• Putting user input into LIKE without escaping % and _; making an index unusable with a leading %.
• Using BETWEEN for date and time ranges and losing the last day's orders.
• Feeding a nullable subquery to NOT IN.

A Small Experiment

★☆☆ Count the customers without a phone, then those with one, and verify that the two add up to 40. Repeat with WHERE phone <> '' and WHERE NOT (phone <> ''); why is the total not 40?

★★☆ Write a query that computes the number of orders for each quarter of 2025 with half-open ranges. Then write the same query in the form BETWEEN '2025-01-01' AND '2025-03-31' and find in which quarters the results differ.

★★★ Compute the average page count in two ways: AVG(page_count) and AVG(COALESCE(page_count, 0)). Which one correctly answers which question? Find out why the result is an integer and what you need to change to get a decimal average.

With LIKE we saw why it matters to filter without touching the column in WHERE. In the next post we discuss the general form of this, sargability, through scalar functions: string and date functions, CAST/CONVERT/TRY_CAST, CASE expressions and the cost of writing WHERE YEAR(ordered_at) = 2025.