How to Delete Null Values in SQL

When working with databases, it might be possible that we have to play with null values. The null value represents the missing or undefined data. Sometimes, It might be possible that we have to remove a row that contains null values. SQL Provides different methods to deal with such types of problems.

In this blog, we will learn how to delete null values in SQL in detail.

Table of Contents:

• Understanding Null Values in SQL
  • Removing Rows Containing Null Values in a Specific Column
  • Removing Rows with Null Values in Any Column
  • Removing Rows with Null values Across Multiple Columns
• Conclusion

Understanding Null Values in SQL

In SQL, Null values represent that a value is missing or undefined. It is different from a space ‘ ’ or 0(zero). 

1. Removing Rows Containing Null Values in a Specific Column

To remove a row having NULL values, we have to use the DELETE statement along with the IS NULL condition. 

Syntax:

DELETE FROM table_name
WHERE column_name IS NULL;

Where table_name is the name of the table and column_name is the name of the column,

Example:

Consider a customer table with the following data:

To remove the row with null values, we have to run

Query:

DELETE FROM customers
WHERE email IS NULL;

After executing the query, the table will look like this:

2. Removing Rows with Null Values in Any Column

In SQL, To remove rows where any column contains NULL values, we have to use the DELETE statement along with the IS NULL condition in all the columns.

Syntax:

DELETE FROM table_name
WHERE column1 IS NULL OR column2 IS NULL OR column3 IS NULL;

Where,

• column1, column2, column3 are the name of the columns.
• table_name is the name of the table

Example:

Suppose we have a table with the following data:

Table: Employees:

Query to remove rows where any column is null

DELETE FROM employees
WHERE name IS NULL OR department IS NULL OR salary IS NULL OR start_date IS NULL;

After executing the above query, the table will look like:

3. Removing Rows with Null values Across Multiple Columns

To remove rows with null values in multiple columns, we have to use the DELETE Statement.

Syntax:

DELETE FROM table_name
WHERE column1 IS NULL OR column2 IS NULL OR column3 IS NULL OR ...;

Where table_name is the name of the table and column1, column2, column3 is the name of columns.

Example: 

Suppose we have a table with the following data:

Query to remove the row with Null Values in all the Columns:

Query:

DELETE FROM employees
WHERE name IS NULL AND department IS NULL AND salary IS NULL AND start_date IS NULL;

After executing the above query, the table will look like:

Conclusion

Now, we have learned all the methods to delete null values from a table. Whether it is in a single row or multiple rows. By using the DELETE statement and IS NULL condition, we can delete the null values in SQL.
If you want to learn more about SQL, you may explore our SQL Course.