[mysql] MySQL SELECT only not null values

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?

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

Update: Based on your comments, perhaps you want this?

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.

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 <>''

SELECT * FROM TABLE_NAME
where COLUMN_NAME <> '';

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.

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.

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 
 WHERE email_address IS NOT NULL 
 AND mobile_number IS NOT NULL;

Using IS NOT NULL With OR Logical Operator

 SELECT * FROM users 
 WHERE email_address IS NOT NULL 
 OR mobile_number IS NOT NULL;

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

I hope help you.

Yes use NOT NULL in your query like this below.

SELECT * 
FROM table
WHERE col IS NOT NULL;