How to Delete Null Values in SQL

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

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:

idnameemail
1Alice[email protected]
2BobNULL
3Carol[email protected]
4DaveNULL
5Bella[email protected]

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:

idnameemail
1Alice[email protected]
3Carol[email protected]
5Bella[email protected]

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:

idnamedepartmentsalarystart_date
1John DoeHR500002021-01-15
2Jane SmithNULL600002020-09-01
3NULLSales550002022-03-10
4Mike JohnsonMarketingNULL2021-05-23
5Lisa WhiteFinance70000NULL

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:

idnamedepartmentsalarystart_date
1John DoeHR500002021-01-15

Get 100% Hike!

Master Most in Demand Skills Now!

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:

idnamedepartmentsalarystart_date
1John DoeHR500002021-01-15
2nullnullnullnull
3Alice JohnsonSales550002022-03-10
4nullnullnullnull
5Mike BrownIT700002020-05-05

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:

idnamedepartmentsalarystart_date
1John DoeHR500002021-01-15
3Alice JohnsonSales550002022-03-10
5Mike BrownIT700002020-05-05

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.

 

Below are articles designed to introduce you to the basics of SQL and its core commands.-

SQL ORDER BY Clause Tutorial – The article on ‘SQL ORDER BY Clause Tutorial’ demonstrates how to organize query results by specific columns for clearer data analysis.

SQL GROUP BY Clause Tutorial – In this comprehensive ‘SQL GROUP BY Clause Tutorial’, you’ll learn to group records and apply aggregate functions effectively.

Convert NVARCHAR Column to INT in SQL – This detailed guide on ‘Convert NVARCHAR Column to INT in SQL’ shows you how to handle data type conversion in SQL queries with ease.

Display Constraint Names in Oracle SQL – If you’re trying to manage constraints, the article ‘Display Constraint Names in Oracle SQL’ explains how to list and interpret them efficiently.

Find Tables Containing Specific Column – The article titled ‘Find Tables Containing Specific Column’ provides practical queries to help you search for columns across large databases.

Invalid SQL Type – By reading ‘Invalid SQL Type’, you’ll uncover the causes behind common data type errors and how to correct them in SQL environments.

Original Purpose of SQL – In ‘Original Purpose of SQL’, the article explores the foundational goals of SQL and how its core purpose influences modern usage.

SQL Privileges – This write-up on ‘SQL Privileges’ clearly outlines how user permissions work in SQL, including how to grant, revoke, and manage access.

Download SQL Server 2012 – The ‘Download SQL Server 2012’ guide offers straightforward instructions to help you install and begin working with SQL Server 2012.