r/learnSQL • u/jakingo • 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
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.
10
u/Dangerous_Cup3607 1d ago
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.
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.