How to Select Only Not Null Values in MySQL
This tutorial article will show you how to write a select statement in MySQL that removes all the null values from specific columns.
Understanding Null Values in MySQL
The NULL values help us know what parts of any table are empty and allow us to deal with them appropriately.
Reasons to remove null rows or columns from a table largely depend on what you are looking for, but one good example is when you need data from a specific column in a table, and if a row returns as NULL, there is no use reading that information.
When dealing with large tables, removing null values can save time and reduce the computing needed.
Instead of writing a script to query from a table and then writing another to loop through the data and find null values, you can simplify the process by including a IS NOT NULL condition in the initial script.
SELECT * FROM 'table name'
WHERE 'column name' IS NOT NULL;
The outcome will be the complete table without rows with NULL values based on the specified column.
The example below takes a list of zip codes in Albany along with a true or false category along each side to differentiate between zip codes that typed correctly and those that have typos:
| code | cd_check |
| -------|----------|
| NULL | true |
| 12649 | false |
| 12248 | true |
| 12239 | true |
| 12359 | NULL |
| 12227 | true |
To remove only the row with a NULL value in the cd_check column, apply the following code:
CREATE TABLE albany (
code INTEGER,
cd_check TEXT
);
INSERT INTO albany VALUES (NULL,"true");
INSERT INTO albany VALUES (12649,"false");
INSERT INTO albany VALUES (12248,"true");
INSERT INTO albany VALUES (12239,"true");
INSERT INTO albany VALUES (12359,NULL);
INSERT INTO albany VALUES (12227,"true");
SELECT code, cd_check FROM albany
WHERE cd_check IS NOT NULL;
Outcome:
| code | cd_check |
| -------|----------|
| NULL | true |
| 12649 | false |
| 12248 | true |
| 12239 | true |
| 12227 | true |
The above returned the table without the row containing a NULL value in the cd_check column. However, since only one column was specified, the row with a NULL value remains under the code column.
You can remove rows from multiple columns by adding the AND statement. The statement will check in both columns and return the following:
| code | cd_check |
| -------|----------|
| 12649 | false |
| 12248 | true |
| 12239 | true |
| 12227 | true |
Both rows containing null values were removed from the table.
Select Only Non-Null Values using WHERE NOT and <=> in MySQL
Instead of placing IS NOT NULL in the script, you can use the following alternative:
The WHERE NOT and <=>, The comparison operators substitute IS NOT when WHERE is followed by NOT.
Example:
SELECT code, cd_check FROM albany
WHERE NOT cd_check <=> NULL;
While this will also work, it is better to use IS NOT NULL, as this is better practice.