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
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
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
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
This is what I came up with:
For each column, this query selects the last non-null value.
If you have a table, set it up as follows:
id|title|body 1 |t1 |b1 2 |NULL |b2 3 |t3 |NULL
title|body t3 |b2
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
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