Checking for NULL:

You can use the IS NULL or IS NOT NULL condition to check if a column or expression is NULL:

                                  
                                    -- Select rows where the department is not specified (NULL)
SELECT * FROM employees WHERE department IS NULL;

-- Select rows where the department is specified (not NULL)
SELECT * FROM employees WHERE department IS NOT NULL;
                                  
                                

Handling NULL in Expressions:

When working with expressions, you can use the COALESCE function to replace NULL with a specified value:

                                  
                                    -- Replace NULL salary with 0
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;              
                                  
                                

Using NULL in Aggregations:

NULL values are generally ignored in aggregate functions like SUM, AVG, COUNT, etc.:

                                
                                    -- Calculate the average salary (ignoring NULL values)
SELECT AVG(salary) AS average_salary FROM employees;
                                
                              

Assigning NULL Values:

You can explicitly assign NULL values to columns, especially when inserting data:

                                  
                                    -- Insert a new employee without specifying the department (NULL)
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (4, 'Alice', 'Williams', NULL, 52000.00);
                                  
                                

Handling NULL in Mathematical Operations:

When performing mathematical operations, NULL values propagate:

                                  
                                    -- NULL in an addition operation results in NULL
SELECT 10 + NULL AS result;
                                  
                                

Tips:

• Use IS NULL or IS NOT NULL conditions to filter rows based on NULL values.
• Be cautious when using NULL in mathematical operations, as the result is usually NULL.
• Use functions like COALESCE or IFNULL to handle NULL values in expressions.
• Document and understand the meaning of NULL values in your database schema.