NULL Types

 2 NULL types:

A-Values: The value exists but we don’t know what it is.

I-Values: The attribute is irrelevant.

They both have different meanings, but they are represented by the same NULL.

You should remember that when:

• Design a table.
• Design column values.
• Query table.

IS DISTINCT FROM

In SQL, NULL is not equal (=) to anything (even to another null) and not different than any other value.

Therefore, If I select from a table, one time when a column is equal to a value and one time when it different than this value - the nulls records won't be selected in any case.

In some SQL languages there is a solution for that: IS DISTINCT FROM (or IS not DISTINCT FROM), that returns any value that it's not the given value - includes NULLs.

-- return all records:
select * from itaitable

-- return who is not DBA. NULL is unknown, so it won't be returned:
select * from itaitable where "Job" <> 'DBA’

-- return DBAs:
select * from itaitable where "Job" = 'DBA’

-- return who is not DBA or records that we don't know what is the value of "Job":
select * from itaitable where "Job" is distinct from 'DBA'

For SQL languages that don't support "IS DISTINCT FROM" we would write:

select * from itaitable where Job <> 'DBA’ or Job IS NULL

"IS DISTINCT FROM" is not available in SQL Server, Oracle, MySQL and more.

"IS DISTINCT FROM" is available in PostgreSQL, DB2.