MySQL SELECT only not null values
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 <>''