MySQL SELECT only not null values

Question

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?

Solution 1

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*/

Solution 2

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.

Solution 3

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.

Solution 4

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.

Solution 5

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

View more solutions

Comments

[Mark Byers]

What do you want to happen if there is a row where some columns have NULL values and other columns have not NULL values?

[bryan sammon]

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?

[Martin Smith]

@bryan - What is your table structure? Do all columns have the same datatype?

[bryan sammon]

Yes, they are all text value type

[Martin Smith]

@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 sammon]

Yea, I would just like to get all the values that are not null from that row.

[Martin Smith]

Well you could do it with a bunch of UNION ... WHERE coln IS NOT NULL statements but that will scan the table once for each column. MySQL doesn't have an UNPIVOT operator that would help here. So probably the most efficient way would be to do it in your code. The best you can do is exclude rows where all columns are NULL. Are you sure your table stucture is normalised?

[bryan sammon]

Im not sure about normalized. What is normalized?

[Martin Smith]

@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)

[bryan sammon]

Thanks man, Im gonna look into that

[bryan sammon]

Im not sure if I explained it well enough, but im gonna try a little better. 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?

[Martin Smith]

@bryan - Could you explain what columns * returns? Maybe provide a bit of example data in your question as it is not clear from your comment above whether this is all one column.

[bryan sammon]

Right now, * returns all of my values in the row. i.e. val1,val2,val3,null,val4,val5,null,null. But I want it to only return the column values that are not null. Right now I do it with a loop to filter out the values after it returns the result.

[DfrDkn]

Thanks a lot.. It helped :)

[krishna]

This is not select which are empty. question is for selecting not null values

[Istiaque Ahmed]

In the last approach, you used CASE statement, not CASE function. So shouldn't it be END CASE instead of END in the SELECT CASE ... part ?

[Istiaque Ahmed]

For not-so-expert people, can you explain the last solution ? Does the idx rom the first SELECT come from the idx in the second SELECT ? What does the CASE statement try to accomplish ? What does the second SELECT actually do ? And you are doing an inner join, not a cross join, right ?

[csey]

I don't think this answers the question. It sounded like OP wanted to select (I assume one specific) row but exclude all columns from that result that were null - this answer requires you to either specify which columns aren't allowed to be null (which is a different problem entirely) or specify all columns, unsuitable for tables with many columns

[csey]

(e.g. something along the lines of SELECT * FROM table WHERE * IS NOT NULL AND primary_key="somevalue")

[Martin Smith]

@csey - The question asker accepted this so presumably it answered it sufficiently as far as they are concerned. Feel free to add your own answer or ask a new question

[csey]

I assume that was more because it solved his problem than answered his question, and it sounds like what he wanted isn't possible as succinctly as above

[Ravindra Singh]

GROUP_CONCAT(body) AS body

[Andy Winarko]

' ' and null is different