NULL in SQL

In SQL, a NULL is never equal to anything, even another NULL. The following query won’t return anything:

SELECT  *
FROM a
WHERE a.column = NULL
Use instead
SELECT  *
FROM a
WHERE a.column is NULL


COUNT(*), when called with an argument, ignores NULLs.


For
SELECT  * FROM  b WHERE   b.column > 'something' AND b.column IS NOT NULL
can be simplified as:
SELECT  * FROM  b WHERE   b.column > 'something'
because any comparison to NULL evaluates to a boolean NULL and does not pass the filter.


For
SELECT  * FROM a JOIN b ON a.column = b.column
When column is nullable in both tables, this query won't return a match of two NULLs for the reasons described above: no NULLs are equal.


The query to include the situation:


SELECT  * FROM a JOIN b ON a.column = b.column  OR (a.column IS NULL AND b.column IS NULL)
For
SELECT  a.* FROM  a WHERE   a.column NOT IN  ( SELECT column FROM  b )
This query will never return anything if there is but a single NULL in b.column. As with other predicates, both IN and NOT IN against NULL evaluate to NULL.


Unlike IN, EXISTS always evaluates to either true or false. The above should be rewritten using a NOT EXISTS:


SELECT  a.* FROM a WHERE  NOT EXISTS ( SELECT NULL  FROM b   WHERE   b.column = a.column )


Comments

Popular posts from this blog

Check MySQL query history from command line

Installing chocolatey on Windows

Fixed: ImportError: No module named utilities.common.tools