How to handle NULL values in MySQL?

In MySQL, you can handle NULL values using the following methods:

  1. 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;
  1. 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;
  1. 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;
  1. 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;
  1. 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;
Leave a Reply 0

Your email address will not be published. Required fields are marked *


广告
Closing in 10 seconds
bannerAds