# sql – MySQL SELECT only not null values

## The Question :

281 people think this question is useful

Is it possible to do a select statement that takes only NOT NULL values?

Right now I am using this:

SELECT * FROM table



And then I have to filter out the null values with a php loop.

Is there a way to do:

SELECT * (that are NOT NULL) FROM table



?

Right now when I select * I get val1,val2,val3,null,val4,val5,null,null etc…. but I just want to get the values that are not null in my result. Is this possible without filtering with a loop?

• What do you want to happen if there is a row where some columns have NULL values and other columns have not NULL values?
• I would like to only get the values from the columns that are not null, and return only the column values in the row that are not null. Right now I use a loop to filter them out, is it possible to do that without a loop?
• @bryan – What is your table structure? Do all columns have the same datatype?
• @bryan – So what would your ideal result set look like then? A one column result set containing all the non null values? If not editing your question with example data and desired results would be helpful…
• @bryan – It sounds like your table may well have repeating groups across columns? (See the Wiki article for an explanation and a suggested alternative structure if that is the case en.wikipedia.org/wiki/First_normal_form)

477 people think this answer is useful

You should use IS NOT NULL. (The comparison operators = and <> both give UNKNOWN with NULL on either side of the expression.)

SELECT *
FROM table
WHERE YourColumn IS NOT NULL;



Just for completeness I’ll mention that in MySQL you can also negate the null safe equality operator but this is not standard SQL.

SELECT *
FROM table
WHERE NOT (YourColumn <=> NULL);



Edited to reflect comments. It sounds like your table may not be in first normal form in which case changing the structure may make your task easier. A couple of other ways of doing it though…

SELECT val1 AS val
FROM  your_table
WHERE val1 IS NOT NULL
UNION ALL
SELECT val2
FROM  your_table
WHERE val2 IS NOT NULL
/*And so on for all your columns*/



The disadvantage of the above is that it scans the table multiple times once for each column. That may possibly be avoided by the below but I haven’t tested this in MySQL.

SELECT CASE idx
WHEN 1 THEN val1
WHEN 2 THEN val2
END AS val
FROM   your_table
/*CROSS JOIN*/
JOIN (SELECT 1 AS idx
UNION ALL
SELECT 2) t
HAVING val IS NOT NULL  /*Can reference alias in Having in MySQL*/



19 people think this answer is useful

You can filter out rows that contain a NULL value in a specific column:

SELECT col1, col2, ..., coln
FROM yourtable
WHERE somecolumn IS NOT NULL



If you want to filter out rows that contain a null in any column then try this:

SELECT col1, col2, ..., coln
FROM yourtable
WHERE col1 IS NOT NULL
AND col2 IS NOT NULL
-- ...
AND coln IS NOT NULL



SELECT * FROM
(
SELECT col1 AS col FROM yourtable
UNION
SELECT col2 AS col FROM yourtable
UNION
-- ...
UNION
SELECT coln AS col FROM yourtable
) T1
WHERE col IS NOT NULL



And I agre with Martin that if you need to do this then you should probably change your database design.

13 people think this answer is useful
Select * from your_table
WHERE col1 and col2 and col3 and col4 and col5 IS NOT NULL;



The only disadvantage of this approach is that you can only compare 5 columns, after that the result will always be false, so I do compare only the fields that can be NULL.

8 people think this answer is useful

I found this solution:

This query select last not null value for each column.

Example

If you have a table:

id|title|body
1 |t1   |b1
2 |NULL |b2
3 |t3   |NULL



you get:

title|body
t3   |b2



Query

SELECT DISTINCT (

SELECT title
FROM test
WHERE title IS NOT NULL
ORDER BY id DESC
LIMIT 1
) title, (

SELECT body
FROM test
WHERE body IS NOT NULL
ORDER BY id DESC
LIMIT 1
) body
FROM test



2 people think this answer is useful

I use the \! command within MySQL to grep out NULL values from the shell:

\! mysql -e "SELECT * FROM table WHERE column = 123456\G" | grep -v NULL



It works best with a proper .my.cnf where your database/username/password are specified. That way you just have to surround your select with \! mysql e and | grep -v NULL.

1 people think this answer is useful

Yes use NOT NULL in your query like this below.

SELECT *
FROM table
WHERE col IS NOT NULL;



1 people think this answer is useful

Following query working for me

when i have set default value of column ‘NULL’ then

select * from table where column IS NOT NULL



and when i have set default value nothing then

select * from table where column <>''



0 people think this answer is useful

MYSQL IS NOT NULL WITH JOINS AND SELECT, INSERT INTO, DELETE & LOGICAL OPERATOR LIKE OR , NOT

Using IS NOT NULL On Join Conditions

SELECT * FROM users
LEFT JOIN posts ON post.user_id = users.id
WHERE user_id IS NOT NULL;

Using IS NOT NULL With AND Logical Operator

SELECT * FROM users
AND mobile_number IS NOT NULL;

Using IS NOT NULL With OR Logical Operator

SELECT * FROM users

SELECT * FROM TABLE_NAME