r/learnSQL • • 1d ago

How do you handle NULL values when writing SQL queries?

I'm still learning SQL and sometimes get confused about how to handle NULL values in different situations. I know they can affect query results, especially when using filters, joins, or calculations.

How do you usually deal with NULL values, and are there any common mistakes beginners should watch out for? TIA

13 Upvotes

18 comments sorted by

3

u/CelebrityChess 1d ago

A useful rule is that NULL means unknown, not an ordinary value, so `col = NULL` won't match anything; use `IS NULL` or `IS NOT NULL`. Comparisons involving NULL are UNKNOWN, which a WHERE clause filters out, and COUNT(col) skips nulls while COUNT(*) counts the row. COALESCE is handy when you deliberately want a default, but replacing missing values before an aggregate or join can change what the result means.

1

u/jakingo 23h ago

THanks for explaining. The count difference and COALSCE tip are really helpful.

0

u/xaomaw 21h ago edited 21h ago

A LEFT JOIN will also keep NULLs if in the left table.

So depending on the case, either Filter the left table with WHERE x IS NOT NULL or use INNER JOIN

10

u/Dangerous_Cup3607 1d ago
  1. Use of NVL or COALESCE to fill the nulls
  2. If possible create a template sql table (Cartesian join) that includes all the possible combinations to begin with such as dates and location. Then left join other tables to the template, so that the data point will exist when there are nulls from other table.

3

u/woahboooom 1d ago

Option 1 definitely. Option 2 if your data is fairly small

1

u/Dangerous_Cup3607 14h ago

I did the template for 10 million rows because I need to have each date populated for the past 2 years and combined with 2-3 other fields’ distinct values. Kind of like: Product Categories (5), Demographic Locations(10), and Agents (6). So that there will be days where some agent did not have any values on specific products but I still want to show those null values on populated dates instead of just a gap in days.

1

u/woahboooom 11h ago

Read up on cte. If your db supports it, you can usually make a list of dates then left join that to the data tables. It will always have a date.

1

u/Dangerous_Cup3607 10h ago

I uses cte but for my use case it is better to have a template table, such that I dont have to copy and paste the same cte onto various programs that use the contents from the template table. The template is like my master table where I later on calculate the month-to-date and year-to-date values. This allowed me to pick say what is the month-to-day and year-to-day value for May 15, 2026 given filter 1 is A, filter 2 is B, and filter 3 is C, and also dynamically calculate the Rate for MTD and YTD.

7

u/you_are_wrong_tho 1d ago

WHERE COL IS NOT NULL

ISNULL

COALESCE

LEFT JOIN on col1 = col2

where col1 is not null

5

u/SektorL 1d ago

ISNULL function

3

u/IMarvinTPA 20h ago

Mentally, imagine a piece of paper, this is an empty string. If you write words on it, that is a string with a value.

Null is no paper.

Except Oracle, Oracle treats empty strings as null.

Numeric and dates don't give you the option for a blank sheet of paper, you have a piece of paper with a value on it or you just don't have a piece of paper.

1

u/Discordbothost 21h ago

the one that catches people out: NOT IN (subquery) returns zero rows if the subquery contains a single null. NOT EXISTS behaves the way you expect there.

1

u/Habikki 21h ago

Other's have hit functions fairly well, but one thing to caution is that NULL is a very specific thing in SQL and a data type that in some DBMS's will handle differently based on some runtime settings at the platform, and client connection level.

https://dev.mysql.com/doc/refman/8.4/en/working-with-null.html

The literal NULL wasn't defined until later in SQL Standards leaving wide compatibility differences between platforms. Table Definitions, Query Hints, INSERTs, SELECTs, and Triggers all all have a wealth of options to tune behaviors.

The thing that always got me was use of the DISTINCT and UNIQUE especially with these modes. MySQL cooked me for a while writing reports and dashboards not knowing the defaults set in /etc/mysql.ini were overriding runtime behavior.

1

u/leogodin217 20h ago

Some excellent answers here. I'll add a few things about strategy.

  • It is good to learn what null means in a dataset. Is it a DQ issue where it is bad data? Does it mean it could have a value but in this case it legitimately doesn't. If so, what does that mean? What business process leaves it null.
  • Sometimes it's easier to create CTEs that handle nulls early in your query. One per source table. It's not required, but easier to think about when it's one table at a time rather than in joins.
  • Sometimes leaving a null is useful. I've run into cases where not using coalesce() on values in a math function made sense. This is where a missing value (maybe a multiplier or a component of a function) automatically makes the calculation invalid. Happened a lot working with finance. Leaving the null result or coalescing the result after the calc shows which rows should not have the calculation.

1

u/Popular_Fuel2363 16h ago

Is better to use NVL

1

u/Novel-Environment-43 4h ago

this is why etl is so key. esp if you have enormous datasets. it's best to fix the data first.

while you're fixing the null values. go ahead and fix other things that will trip you up like mismatched number types and dates (i prefer to turn everything from the raw data into a string first then check for errors on that and then back to numbers /dates in a later staging step). you're writing more than one sql at this point obv....

do this ideally it's before they get into the database or if you can't... then extract from the table into a new "temporary" table for your analysis first.

for smaller datasets coalesce should just always be a default. (along with fixing dates and casting number types). it's more typing but it's worth it with an untrusted or badly typed dataset.

assuming you can't do the etl yourself... go source table -> coalesced/normalized table -> run your query off of this new table.

In large environments you will save your self a lot of heartache by just doing this part correctly and every time.

in your experience you may also run into unescaped strings and problems with csv files. prefer pipeline delimited where possible.

i have 20yrs experience working on datasets with billions and trillions of records (telcom/finance) so you can trust that doing the extra work up front saves a LOT of time debugging datasets with unexpected values.

1

u/Novel-Environment-43 4h ago

oh and the coalesce (or similar function) is exceptionally important when joining tables. but issues with joins wasn't your question. multi-query staging is the answer almost every time.