SQL NULLs Vs Non-Existing Rows In Outer Joins
In SQL, NULL represents an existing but unknown value, not non-existence. Outer joins reveal inexisting rows with all-null values, including keys. Proper data modeling distinguishes between these meanings.
In a previous post, I briefly mentioned that I consider a NULL in SQL databases as an existing but unknown value instead of a not existing one. But there's an exception in outer joins where a non-existent row will have null values in the results. Let's clarify it with an example. It’s important to distinguish between columns with a null value and columns from inexisting rows where all values are null, including the key. Here is an example extracted from the traditional EMP/DEPT schema. I don't show the create table on purpose because the way it is traditionally built in SQL examples is incorre...