In MySQL, several functions and operators are available to work with NULL values. Here are some commonly used ones:
1. IS NULL: This operator checks if a value is NULL.
SELECT * FROM table_name WHERE column_name IS NULL;
2. IS NOT NULL: This operator checks if a value is not NULL.
SELECT * FROM table_name WHERE column_name IS NOT NULL;
3. IFNULL: This function returns the first non-NULL expression among its arguments.
SELECT IFNULL(column_name, 'default_value') FROM table_name;
4. COALESCE: This function returns the first non-NULL value among its arguments.
SELECT COALESCE(column1, column2, 'default_value') FROM table_name;
5. NULLIF: This function returns NULL if the two provided expressions are equal; otherwise, it returns the first expression.
SELECT NULLIF(column1, column2) FROM table_name;
These functions and operators can be helpful for handling NULL values in queries, such as substituting them with default values, checking for NULL conditions, or comparing values while treating NULLs specially.