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:
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:
id | name | department | salary | start_date |
1 | John Doe | HR | 50000 | 2021-01-15 |
2 | Jane Smith | NULL | 60000 | 2020-09-01 |
3 | NULL | Sales | 55000 | 2022-03-10 |
4 | Mike Johnson | Marketing | NULL | 2021-05-23 |
5 | Lisa White | Finance | 70000 | NULL |
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:
id | name | department | salary | start_date |
1 | John Doe | HR | 50000 | 2021-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:
id | name | department | salary | start_date |
1 | John Doe | HR | 50000 | 2021-01-15 |
2 | null | null | null | null |
3 | Alice Johnson | Sales | 55000 | 2022-03-10 |
4 | null | null | null | null |
5 | Mike Brown | IT | 70000 | 2020-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:
id | name | department | salary | start_date |
1 | John Doe | HR | 50000 | 2021-01-15 |
3 | Alice Johnson | Sales | 55000 | 2022-03-10 |
5 | Mike Brown | IT | 70000 | 2020-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.