Problem
Is it feasible to create a select statement that only accepts values that are NOT NULL?
This is what I’m using right now:
SELECT * FROM table
Then I have to use a php loop to filter out the null values.
Is it possible to:
SELECT * (that are NOT NULL) FROM table
?
When I choose *, I get val1,val2,val3,null,val4,val5,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null Is this achievable without using a loop to filter the data?
Asked by bryan sammon
Solution #1
IS NOT NULL should be used. (With NULL on either side of the expression, the comparison operators = and> both return UNKNOWN.)
SELECT *
FROM table
WHERE YourColumn IS NOT NULL;
To be thorough, I’ll explain that you can negate the null safe equality operator in MySQL, but this isn’t standard SQL.
SELECT *
FROM table
WHERE NOT (YourColumn <=> NULL);
Comments have been added to the text. It appears that your table is not in its original state, in which case altering the structure may make your job easier. However, there are a few of alternative options…
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 downside of the preceding method is that it searches the table many times for each column. The following may help eliminate this, but I haven’t tested it with 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*/
Answered by Martin Smith
Solution #2
You can exclude rows with a NULL value in a certain column by using the following filter:
SELECT col1, col2, ..., coln
FROM yourtable
WHERE somecolumn IS NOT NULL
If you want to filter out rows with a null in any column, use the following formula:
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 feedback, do you think you’d like 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 agree with Martin that if you need to do this, your database design should probably be changed.
Answered by Mark Byers
Solution #3
Select * from your_table
WHERE col1 and col2 and col3 and col4 and col5 IS NOT NULL;
The only drawback to this method is that you can only compare 5 columns before the result is always false, hence I only compare fields that can be NULL.
Answered by Alan Chavez
Solution #4
This is what I came up with:
For each column, this query selects the last non-null value.
Example
If you have a table, set it up as follows:
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 this information is useful to you.
Answered by porquero
Solution #5
The query below is working for me.
When I set the column’s default value to NULL,
select * from table where column IS NOT NULL
When I set the default value to nothing, nothing happens.
select * from table where column <>''
Answered by Basant
Post is based on https://stackoverflow.com/questions/5285448/mysql-select-only-not-null-values