0 SQL Tutorial -" NULL in SQL"

SQL Tutorial - Tips1- 'NULL in SQL'

The Four Golden Rules for 'NULL' in SQL

1. Use NULLs to indicate unknown/missing information only. Do not use NULLs in place of zeroes, zero-length strings or other "known: blank values. Update your NULLs with proper information as soon as possible.
2. In ANSI SQL, NULL is not equal to anything, even other NULLs! Comparisons with NULL always result in UNKNOWN
3. Use SET ANSI_NULLS ON, and always use ANSI standard SQL syntax for NULLs. Straying from the standard can cause problems including portability issues, incompatibility with existing code and databases and returning incorrect results.
4. The ANSI standard COALESCE() and CASE syntaxes are preferred over ISNULL() or other proprietary syntax

No Two NULLs Are Created Equal
ANSI SQL three-valued logic (3VL) is that NULL is not equal to anything else. It is not less than, greater than, or even unequal to anything else either.

All NULLs Are Created Not Distinct
From above we know that comparisons with NULL never evaluate to TRUE or FALSE, that NULL is never equal to NULL, and that NULL comparisons always result in UNKNOWN. Now it's time to list the exceptions. In order to simulate NULL equality, and to keep from contradicting themselves in the process, the ANSI SQL-92 standard decreed that two NULL values should be considered "not distinct". The definition of not distinct in the ANSI standard includes any two values that return TRUE for an equality test (e.g., 3 = 3, 4 = 4, etc.), or any two NULLs. This simulated NULL equality is probably most used in the GROUP BY clause, which groups all NULL values into a single partition. In Unique Constraint also NULLs are treated same.

NULLs Flock Together
The ORDER BY clause in SELECT queries places all NULL values together when it orders your results. SQL Server treats NULLs as the "lowest possible values" in your results. What this means is NULL will always come before your non-NULL results when you sort in ascending order, and after your non-NULL results when you sort in descending order