MySQL SELECT only not null values
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 = 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*/
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.
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.
I found this solution:
This query select last not null value for each column.
If you have a table:
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 help you.
I use the \! command within MySQL to grep out NULL values from the shell:
\! mysql -e "SELECT * FROM table WHERE column = 123456\G" | grep -v NULL
It works best with a proper .my.cnf where your database/username/password are specified. That way you just have to surround your select with \! mysql e and | grep -v NULL.
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 <>''
Yes use NOT NULL in your query like this below.
SELECT * FROM table WHERE col IS NOT NULL;
Using IS NOT NULL On Join Conditions SELECT * FROM users LEFT JOIN posts ON post.user_id = users.id WHERE user_id IS NOT NULL; Using IS NOT NULL With AND Logical Operator SELECT * FROM users WHERE email_address IS NOT NULL AND mobile_number IS NOT NULL; Using IS NOT NULL With OR Logical Operator SELECT * FROM users WHERE email_address IS NOT NULL OR mobile_number IS NOT NULL;
SELECT * FROM TABLE_NAME where COLUMN_NAME <> '';