SQL Basics
SQL NULL Handling
Handling NULL Values
NULL handling uses IS NULL and IS NOT NULL, with COALESCE to manage default values.
What is NULL in SQL?
In SQL, NULL represents a missing or undefined value. Unlike other data types, NULL is not equal to zero, an empty string, or any other default value. It signifies the absence of any value in a column.
Checking for NULL with IS NULL and IS NOT NULL
To check for NULL values, you use the IS NULL and IS NOT NULL operators. These operators allow you to identify rows where a specific column has or does not have a NULL value.
The example above selects all employees who do not have a department assigned (i.e., the department column is NULL).
This example retrieves all employees who have an assigned department, filtering out those with NULL values in the department column.
Handling NULL with COALESCE
The COALESCE function is used to return the first non-NULL value from a list of expressions. It is particularly useful for substituting NULL values with a default value.
In this query, if an employee's department is NULL, the COALESCE function substitutes it with 'Unassigned'.
NULL in SQL Expressions
When NULL interacts with other values or expressions, the result is usually NULL. This behavior is important to consider in arithmetic operations and conditional expressions. Using functions like COALESCE can help manage these cases.
SQL Basics
- Previous
- BETWEEN
- Next
- LIMIT and OFFSET
