Posted By: Anonymous
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 should use
IS NOT NULL. (The comparison operators
<> both give
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*/