Many analytical queries depend on filters that decide which rows stay in a result set and which rows drop out. Real data sets carry missing values in optional columns, outer joins, and partially filled forms. Null handling sits in the middle of that behavior, because filters compare columns to constants and to other columns. Standard SQL treats null as a marker for missing or unknown data, which affects how predicates in a WHERE clause behave. Comparisons like =, <, and > behave differently when either side is null. Correct filters need a solid view of three valued logic, direct null checks with IS NULL and IS NOT NULL, and support functions such as NULLIF and COALESCE to keep results predictable.
Null Values In SQL Logic
Null handling in SQL rests on a small set of rules that keep coming up in queries, joins, and aggregates. Filters, joins, and computed columns all rely on how the engine treats the difference between a concrete value and a missing one. Getting comfortable with that difference reduces surprises in query results and makes it easier to reason about what the database is doing.
Meaning Of Null In SQL
In SQL, null represents missing or unknown data. Zero is a real numeric value. An empty string is a real text value with length zero. Null is neither of those. Null means there is no recorded value in that row for that column.
Column types stay the same when null appears. An INT column with null is still an integer column. A TIMESTAMP column can hold timestamps or null, without changing its declared type. The null marker sits beside the value storage and tells the engine there is nothing there for that row. That is why many operations pass null through, because the database cannot reliably compute a numeric result or a text result from something that does not exist.
Null also interacts with aggregates in a specific way. Aggregate functions such as SUM, AVG, and COUNT(column) usually skip nulls rather than treat them as zero or empty.
This query counts all rows with COUNT(*). The COUNT(salary) expression counts only rows where salary is not null. The AVG(salary) expression computes the average over non null salaries and ignores rows where salary is null instead of treating them as zero.
Null also interacts with string functions. Many functions propagate null when any argument is null, because the result cannot be determined from missing pieces.
When last_name is null, the full_name expression becomes null as well in many database systems, even though first_name has a real value. Some engines treat concatenation with null differently through configuration, so practical code needs a check or a function such as COALESCE in later sections for more control. In this section, the main point is that null behaves as an absorber in many expressions rather than as an empty placeholder.
Three Valued Predicate Logic
Predicates in SQL can return three outcomes. True means the condition holds for that row. False means the condition does not hold. Null stands for an unknown truth value. This three way result feeds directly into WHERE, ON, and HAVING clauses. Most conditions that appear in filters fall into this pattern. Comparisons such as score = 10 can return true when score holds 10, false when score holds a number other than 10, and null when score is null. That third case is not treated as true or false. It is stored as an unknown result.
This query produces a result set where is_ten can be true, false, or null. Rows with score equal to 10 receive true. Rows with score equal to some other non null number receive false. Rows where score is null receive null.
Filtering turns those three values into keep or discard choices. A WHERE clause keeps rows only when the predicate is true. Rows where the predicate is false are removed. Rows where the predicate expression evaluates to null are also removed.
This filter keeps rows where score = 10 produced true. Rows where score held a different numeric value fall out, and rows where score was null also fall out. That behavior comes directly from the rule that WHERE treats null predicate results the same as false.
Three valued logic also appears in compound predicates. Logical operators AND and OR combine true, false, and null in a defined way. For AND, any false operand forces the whole expression to false, while null can propagate when no operand is false and at least one operand is null. For OR, any true operand forces the whole expression to true, while null can propagate when no true value appears and at least one operand is null.
This query checks two conditions at once. When status = ‘PAID’ is false, the entire predicate is false even if refunded_at is null. When status = ‘PAID’ is true and refunded_at IS NULL is true, the combined predicate is true and the row passes the filter. When status = ‘PAID’ is true and refunded_at IS NULL is false, the combined predicate becomes false and the row is removed.
Null in predicates can also emerge from expressions rather than from plain columns. Computed values that depend on nullable columns can produce null results which then travel into the filter.
When either quantity_on_hand or reserved_quantity is null, the subtraction yields null, and the comparison > 0 yields null as well. The final filter treats that null predicate the same way as false and drops that row from the result.
Equality Checks With Null
Equality comparisons with null create a common source of confusion. Many query writers try to find missing values with = NULL and see empty results. SQL rules explain that outcome step by step.
Null does not compare equal to any value, including itself. An expression such as column = NULL does not test for missing data. That comparison always results in null, not true and not false, no matter what value sits in column. When column has a number or string, the comparison to null gives null. When column itself is null, the comparison still gives null, because the two nulls do not compare as equal in this context.
This query returns null in eq_null for every row. That is true for rows where deleted_at has a timestamp and for rows where deleted_at is null. No row can ever have eq_null equal to true.
Filters based on that comparison then remove every row.
This filter asks for rows where deleted_at = NULL is true. The predicate never evaluates to true, so no row can pass the WHERE clause. That behavior is not a bug in the engine. It follows directly from the rule that any comparison with null yields null, and null predicate results are treated as discarded rows during filtering.
Equality with null in joins follows the same principle. Join conditions that compare nullable columns with = will never match rows where both sides are null, even when those rows represent similar missing data. Expressions such as left_table.col = right_table.col produce null for any pair of nulls and therefore do not create a joined row. Testing for null explicitly with IS NULL or using null aware operators from later sections is needed when missing values should be matched.
This behavior also affects inequality comparisons such as <, >, <=, >=, and <>. Any time a null participates in those comparisons, the result is null, and any filter that depends directly on that result will remove those rows.
Filters That Handle Nulls Safely
Handling missing values in a predictable way relies on predicates that treat null as a first class concept instead of trying to compare it with ordinary values. Conditions built with IS NULL, IS NOT NULL, COALESCE, and NULLIF give direct control over which rows stay in a result set. Modern database engines follow the same basic rules here, so once these tools make sense for one engine, the same habits carry across other engines with only minor syntax differences.
Filters With IS NULL
Filters that look for missing data start with IS NULL. That predicate tests a column or expression directly for the null marker, without passing through three valued comparison logic that turns equality checks into unknown results.
This query returns invoices that have not been recorded as paid. Column paid_at holds a timestamp when a payment clears. For invoices still waiting on payment, the column stays null. Predicate paid_at IS NULL evaluates to true for those rows and false when a timestamp is present.
Filters can work on expressions as well as on plain columns. Computed values that depend on nullable fields can be wrapped in IS NULL to keep or remove rows based on whether the expression produced a real value.
This query looks for shipments where a transit interval cannot be computed. Expression delivered_at - dispatched_at yields null when either timestamp is missing. Predicate delivered_at - dispatched_at IS NULL then picks those rows specifically, which can be useful when checking for incomplete tracking data.
Filters with IS NULL matter in join logic as well. Outer joins introduce nulls on one side when no matching row exists on the other side, and filters can use that behavior to find unmatched rows.
Rows returned from this query represent customers who have no subscription row at all. The left join keeps every customer, adds columns from subscriptions when a match exists, and fills those columns with null when no match exists. Predicate s.subscription_id IS NULL keeps only the rows where join columns came back as null, which signals that no subscription matched that customer.
Complement of IS NULL is IS NOT NULL. Filters that need only rows with some stored value, regardless of which exact value, rely on this predicate.
Here, the query keeps support tickets that already have a closing timestamp, even if some other fields remain null. Predicate closed_at IS NOT NULL filters for rows where that specific column holds a real value, which tends to match the idea of a finished ticket.
IS NOT NULL can also combine with extra conditions that weed out placeholder values such as empty strings or sentinel codes. When a column carries empty strings for missing data, a filter that targets both null and empty strings can separate usable values from placeholders with one expression in later subsections, or with a combination of IS NOT NULL and a comparison here.
COALESCE NULLIF Filters
Functions COALESCE and NULLIF help fold multiple columns or special placeholder values into a single nullable expression. Filters then test that expression with IS NULL or IS NOT NULL to apply business rules that touch several fields at once.
COALESCE takes a list of arguments and returns the first one that is not null. When every argument is null, the function returns null. This rule fits data models where contact information or identifiers may be stored in several optional columns.
Expression COALESCE(home_phone, mobile_phone, office_phone) picks home_phone when it has a real value, falls back to mobile_phone when home is null, and finally uses office_phone when the first two are null. Resulting column primary_phone becomes null only when all three phone fields are missing.
Filters can then check that combined value with IS NOT NULL to pick any row that has at least one contact number.
That query returns contacts for whom at least one phone field holds a value. The function compresses three nullable columns into one nullable expression, and the predicate keeps rows where that expression is not null. Compared to writing three separate conditions joined with OR, this keeps the filter compact while still matching the same logic.
COALESCE also appears with default values that stand in when data is missing. For filters, the default can be chosen to fall outside any real domain of values, then compared directly.
Expression COALESCE(returned_on, DATE ‘2999-12-31’) treats rentals with no recorded return date as if they had a very large date. When that synthetic date is beyond the comparison threshold, the filter can express questions like “returned after a certain date or not returned yet” with one predicate.
Function NULLIF compares two arguments and returns null when they are equal. When they differ, it returns the first argument. That behavior works well when stored values include placeholders such as empty strings or sentinel numbers that should be treated as missing in reports and filters.
Expression NULLIF(trim(email_address), ‘’) turns empty or whitespace-only email strings into null, while leaving real email addresses unchanged. Code that builds on normalized_email no longer has to handle both empty strings and nulls separately.
Filters that need to find rows with actual email addresses can then apply a single null aware predicate.
This query removes rows where an email address field is either null or an empty string, keeping only rows that have a non empty email after trimming whitespace. Comparison against a single condition on top of NULLIF makes the filter behavior easier to reason about, because placeholder codes have already been mapped to null.
COALESCE and NULLIF can also work in combination. Data sets that use several placeholder codes, such as zero, -1, or a specific text marker, can apply nested NULLIF calls to convert all those codes to null, then pass the result into COALESCE or straight into IS NULL and IS NOT NULL predicates. That sequence gives filters a compact way to treat multiple awkward values as missing without repeating the same comparisons across many queries.
Conclusion
Null handling in SQL rests on a compact set of rules that govern how comparisons, three valued logic, and predicates treat missing data. Filters that rely on plain equality never match null values, so predicates such as IS NULL, IS NOT NULL, and null aware operators are the tools that actually see those rows. Functions like COALESCE and NULLIF help group placeholder codes and nullable columns into expressions that behave consistently inside WHERE, JOIN, and HAVING clauses. With those pieces in place, it becomes easier to read a filter, trace how nulls move through it, and see which rows stay in the result set.
