MySQL SELECT only not null values
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 valFROM your_tableWHERE val1 IS NOT NULLUNION ALLSELECT val2 FROM your_tableWHERE 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 valFROM your_table /*CROSS JOIN*/ JOIN (SELECT 1 AS idx UNION ALL SELECT 2) tHAVING val IS NOT NULL /*Can reference alias in Having in MySQL*/
You can filter out rows that contain a NULL value in a specific column:
SELECT col1, col2, ..., colnFROM yourtableWHERE somecolumn IS NOT NULL
If you want to filter out rows that contain a null in any column then try this:
SELECT col1, col2, ..., colnFROM yourtableWHERE col1 IS NOT NULLAND 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) T1WHERE col IS NOT NULL
And I agre with Martin that if you need to do this then you should probably change your database design.