In the “Distinct” exercise, sometimes we see some NULL values in the output. How could that be explained? Isn’t the DISTINCT argument supposed to display only the already existing values that are unique?
Great question! Quote from the SQLite documentation:
SQLite was modified to work the same as Oracle, PostgreSQL, and DB2. This involved making NULLs indistinct for the purposes of the SELECT DISTINCT statement and for the UNION operator in a SELECT. NULLs are still distinct in a UNIQUE column. This seems somewhat arbitrary, but the desire to be compatible with other engines outweighed that objection.
This means that SELECT DISTINCT treats NULL like any other value - it is allowed, but duplicates will be removed.