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?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

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

Method 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.

Method 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.

Method 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.

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

Method 6

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

! mysql -e "SELECT * FROM table WHERE column = 123456G" | 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.

Method 7

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;

Method 8

Yes use NOT NULL in your query like this below.

SELECT * 
FROM table
WHERE col IS NOT NULL;

Method 9

SELECT duration,id FROM `tickets` WHERE duration !=""

Method 10

SELECT * FROM TABLE_NAME
where COLUMN_NAME <> '';

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0