How to handle NULL values in MySQL?
In MySQL, you can handle NULL values using the following methods:
- Check for NULL values: Use the IS NULL or IS NOT NULL operator to check for NULL values. For example, you can use the following query to check if a column contains NULL values:
SELECT *
FROM table_name
WHERE column_name IS NULL;
- Replace NULL values: You can use the COALESCE function to replace NULL values with a specified value. For example, the following query replaces NULL values with 0:
SELECT COALESCE(column_name, 0) AS new_column_name
FROM table_name;
- Filtering NULL values:
You can use the WHERE clause to filter out NULL values. For example, the following query will exclude rows that contain NULL values.
SELECT *
FROM table_name
WHERE column_name IS NOT NULL;
- By using the IFNULL function, you can check and replace NULL values. For example, the following query will replace NULL values with “NA”:
SELECT IFNULL(column_name, 'NA') AS new_column_name
FROM table_name;
- By using a CASE statement, you can handle NULL values based on conditions. For example, the following query replaces NULL values with “Unknown”:
SELECT
CASE
WHEN column_name IS NULL THEN 'Unknown'
ELSE column_name
END AS new_column_name
FROM table_name;