SQL NULL Values
In SQL, a NULL value represents a missing or undefined value. It is important to understand how to handle NULL values in your queries because they can affect the results of your SQL operations.
Checking for NULL Values
To check for NULL values, you can use the IS NULL and IS NOT NULL operators.
Example 1: Checking for NULL Values
SELECT first_name, last_name
FROM employees
WHERE department IS NULL;This query selects the first and last names of employees whose department is NULL.
Example 2: Checking for Non-NULL Values
SELECT first_name, last_name
FROM employees
WHERE department IS NOT NULL;This query selects the first and last names of employees whose department is not NULL.
Handling NULL Values in Expressions
When performing calculations or concatenating strings, NULL values can cause unexpected results. You can use functions like COALESCE or IFNULL to handle NULL values.
Example 3: Using COALESCE
SELECT first_name, last_name, COALESCE(department, 'Unknown') AS department
FROM employees;This query selects the first and last names of employees and their department. If the department is NULL, it returns ‘Unknown’ instead.
Example 4: Using IFNULL (MySQL)
SELECT first_name, last_name, IFNULL(department, 'Unknown') AS department
FROM employees;This query performs the same operation as the previous example but uses the IFNULL function available in MySQL.
Aggregating NULL Values
When using aggregate functions like COUNT, SUM, AVG, etc., NULL values are ignored, except when using COUNT(*).
Example 5: COUNT with NULL Values
SELECT COUNT(department) AS department_count
FROM employees;This query counts the number of non-NULL department values in the “employees” table.
Example 6: COUNT(*) Including NULL Values
SELECT COUNT(*) AS total_employees
FROM employees;This query counts all rows in the “employees” table, including those with NULL values.
Understanding how to work with NULL values is crucial for accurate data querying and manipulation in SQL. Proper handling of NULL values ensures that your queries return the expected results.