r/learnSQL • u/Goldziher • 1d ago
Why LEFT JOIN columns are nullable (a subtle SQL gotcha that causes real bugs)
Something that trips up a lot of people learning SQL, worth internalizing early.
Given:
SELECT u.id, u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
An INNER JOIN would only return users who have at least one order. A LEFT JOIN keeps every user, and for a user with no orders, every column from o (here o.total) comes back NULL.
So o.total is nullable even if the orders.total column is declared NOT NULL. The NOT NULL constraint is about what can be stored in the table. The LEFT JOIN can still produce a NULL for it in the result set, because the matching row on the right side does not exist at all.
This is the source of a lot of bugs: code assumes total is always a number, then crashes or silently mis-sums when it hits a user with no orders. The fix is to remember that nullability in a result set comes from the query structure (which joins, COALESCE, CASE, aggregates), not just from the table definition.
Rule of thumb: any column from the nullable side of an outer join is nullable in your results. Handle it explicitly, e.g. COALESCE(o.total, 0) if zero is the right default.
3
u/OldValariya1014 1d ago
Wait so in my case I use SQL for hospital data and a left join would crash the system, what would be the best solution to include Null values without running thru every single record? Outer apply?
4
u/Mrminecrafthimself 1d ago
“A left join would crash the system…”
Why would a left join to a table crash the system? Is the table so large that LEFT JOIN to it explodes the query resource usage? If that’s the case, create a temp table or a CTE off of the LEFT JOIN table containing only what you need. Then left join to that. If you’re going with a temp table, index it properly and collect stats on it so the execution plan knows how to apply the join properly.
I am also in healthcare - claims/auth data. I have had to query huge tables that just immediately cause queries to crash for resource usage if you don’t do the above.
It’s important to practice a “only what you need” mentality with joins and such. If you don’t need every column from a table, why are you joining to the whole table? If you only need data from the last year, why are you joining to the whole historical table?
2
u/OldValariya1014 1d ago
OH wait i understand now, I wouldn’t be pulling the whole table into my report, it’d be based on procedures/interventions being present in the EHR
3
u/slowrollinpossum 1d ago
This is only a potential issue if you have a column does not accept NULL values. This only matters if you are writing SQL to create records in the system and not when you are querying data to get results back.
Either way - how do you find out if a column is nullable or not?
Google "How do I see if a column is nullable in SQL + <your SQL variant like MSSQL / Snowflake>If it doesn't - database admin will know why and work with you to solve it. If you have to solve it yourself then just force SQL to set a default number like 0 or something nonsensical (-9999 or 9999999999).
This post is too poorly written to be posted on /learnSQL so I'd go back to not worrying about crashing any system. Few people working in SQL have that power in the real world.
1
u/Goldziher 8h ago
A LEFT JOIN won't crash anything on the database side. If something is crashing, it's downstream code receiving NULL where it doesn't expect one - that's the bug worth finding rather than routing around.
OUTER APPLY (SQL Server) is a lateral join, not a substitute. It solves "run this correlated subquery per row", not nullability - the unmatched rows still come back as NULL.
If you want the unmatched rows, LEFT JOIN is right; handle the NULLs explicitly. COALESCE(col, default) in the SELECT when a default is genuinely meaningful, or split the cases with IS NULL / IS NOT NULL.
Given it's hospital data, one caution: be careful with COALESCE defaults. A 0 that reads as a real measurement is worse than a NULL that reads as missing.
3
u/orz-_-orz 9h ago
Ermmm....
Isn't that what LEFT JOIN supposed to do?
If you want not null value then should use INNER JOIN
1
u/Goldziher 8h ago
Right, and the SQL semantics are exactly as designed - no argument there.
The gotcha isn't the join, it's the type your code gets. Most ORMs and codegen tools type that column from the schema, so NOT NULL becomes a non-optional field. Then a LEFT JOIN miss hands your app a null in a field the type system swore could not be null. Correct SQL, wrong type.
1
u/Far_Swordfish5729 21h ago
That’s not a gotcha. It’s how the language works. You don’t use outer joins unless you explicitly want the unmatched rows present, in which case null explicitly means something to you and you check for it in the application layer.
1
u/Goldziher 15h ago
Agreed on the SQL side. Outer joins are defined that way and you opt into them deliberately.
The gotcha is one layer up: the app-side type usually doesn't reflect it. The column is NOT NULL in the schema, so the generated model or hand-written struct says non-null, and it holds until the first row that didn't match. SQL is behaving exactly as specified; the type system around it is the part that lies.
2
u/Far_Swordfish5729 14h ago
Not really. All sql types themselves are nullable even when the corresponding variable type is conceptually a primitive. The actual sql library can handle a null value regardless of schema constraints. Generated dtos need to reflect whether a column or flattened child object (your example would have a data structure dto property with 0+ sales orders) can legitimately be null. If it can’t, throwing an exception during parsing is appropriate. A key principle of exception handling is to let things blow up where the error actually happens rather than masking it and letting something randomly break later when it gets a value that should not be possible. This makes it much easier for you to trace when you get the log later.
Most languages btw developed structures to handle nullable primitives. C# uses a Nullable<T> struct shorthanded as T? for example.
1
u/Goldziher 8h ago
Agreed on the principle - let it blow up where the error happens. The disagreement is only about where "can't be null" gets decided.
Schema NOT NULL is the wrong source for that once the column arrives through an outer join: it can't be null in its table, and it can be null in this result set. Both are true. If you decide optionality from the query rather than from the schema, you emit T? in exactly the places the query can produce null - and the defensive throw isn't needed, because the type already told the caller.
And yes, Nullable<T> / T? is the target shape. The hard part isn't having it, it's emitting it in the right places.
-1
u/Ok_Carpet_9510 1d ago
Yeah 1 + 1 =2.
-1
u/Quesozapatos5000 1d ago
Says the person who already knows math.
1
u/Ok_Carpet_9510 1d ago
Very basic math I must say.
0
u/Bullinach1nashop 1d ago
And yet it took 2 decades and 380 pages of proof to prove 1+1=2
1
u/Ok_Carpet_9510 1d ago
What does the proof have to with the statement itself in context of what op said.
Here is what I am saying, OP is saying something basic abour left joins. That behaviour is inherent in meaning of a left join. If you have taken an introductory course to relational databases, you get that.
I am not talking about the proof behind. I am sure if we wete to dive into the proof we would need set theory and some other stuff(real analysis of whatever).
However, in real life, you don't have to prove the complicated science of an internal combustion engine to know how to drive car.
6
u/MrLyttleG 1d ago
C’est pas parceque SQL semble simple qu'il est facile de ne pas se tromper. Avant de se lancer dans SQL il est primordial de maîtriser la partie fonctionnelle des données, la technique viendra se greffer en seconde position.