SQL NULL Values

 In SQL, NULL represents the absence of a value or an unknown value in a database field. It is not the same as an empty string or a zero value—NULL means that no data has been entered or the value is undefined.

Key Points About NULL in SQL:

1. NULL is not a value: It's a placeholder for missing, undefined, or unknown values.

2. NULL is not equal to 0, empty string, or any other value: Any comparison with NULL using regular operators (e.g., =, !=, >, <) will not return TRUE or FALSE, but rather UNKNOWN.

3. SQL IS NULL and IS NOT NULL: To check for NULL values, you cannot use the standard comparison operators. Instead, you must use the IS NULL or IS NOT NULL conditions.

   Example:

   SELECT * FROM employees WHERE salary IS NULL;
   SELECT * FROM employees WHERE salary IS NOT NULL;
   

4. NULL and Aggregate Functions: Aggregate functions like COUNT(), SUM(), AVG(), etc., generally ignore NULL values when performing calculations. However, COUNT(*) counts rows regardless of NULL values.

5. NULL in INSERT statements: You can insert NULL values explicitly into a table, or if a column allows NULL, omitting it will result in a NULL value being inserted by default.

   Example:

   INSERT INTO employees (name, salary) VALUES ('John Doe', NULL);
   

6. Handling NULL with COALESCE and IFNULL:

   • COALESCE() returns the first non-NULL value from a list of expressions.

     SELECT COALESCE(salary, 0) FROM employees;  -- returns 0 if salary is NULL
     

   • IFNULL() (in some SQL dialects like MySQL) is used to replace NULL with a specified value.

     SELECT IFNULL(salary, 0) FROM employees;  -- returns 0 if salary is NULL
     

7. NULL in Joins:

   • When performing joins, NULL values in columns can lead to rows being excluded, especially when using INNER JOIN. A LEFT JOIN will return rows with NULL values if no match is found.

   Example of LEFT JOIN:

   SELECT employees.name, departments.name 
   FROM employees 
   LEFT JOIN departments ON employees.department_id = departments.id;
   

   This query returns all employees, including those who don't belong to any department (i.e., department_id is NULL).

8. NULL and WHERE clause: A WHERE clause with NULL will not match any rows, even if the column contains NULL. You need to use IS NULL or IS NOT NULL to filter for NULL values explicitly.

   Example:

   SELECT * FROM employees WHERE department_id IS NULL;
   

9. NULL in comparison expressions:

   • When comparing a value to NULL using =, it results in UNKNOWN (i.e., the comparison doesn’t return TRUE or FALSE).

   Example:

   SELECT * FROM employees WHERE department_id = NULL;  -- This won't work as expected
   

   Instead, use:

   SELECT * FROM employees WHERE department_id IS NULL;  -- Correct way to check for NULL
   

Practical Example:

1. Creating a Table with NULL-able Columns:

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(100),
    salary DECIMAL(10, 2) NULL,
    department_id INT NULL
);

2. Inserting NULL Values:

INSERT INTO employees (id, name, salary, department_id)
VALUES (1, 'Alice', NULL, 101),
       (2, 'Bob', 50000.00, NULL),
       (3, 'Charlie', 55000.00, 102);

3. Querying NULL Values:

-- Employees with no salary
SELECT * FROM employees WHERE salary IS NULL;

-- Employees with a department assigned
SELECT * FROM employees WHERE department_id IS NOT NULL;

4. Using COALESCE to Replace NULL:

SELECT name, COALESCE(salary, 0) AS salary FROM employees;

This query will return 0 for employees who have NULL in the salary column.