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?
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
You should use
IS NOT NULL. (The comparison operators
<> both give
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
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.
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 <>''
I use the
! command within MySQL to grep out NULL values from the shell:
! mysql -e "SELECT * FROM table WHERE column = 123456G" | 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
! mysql e and
| grep -v 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;
NOT NULL in your query like this below.
SELECT * FROM table WHERE col IS NOT NULL;
SELECT duration,id FROM `tickets` WHERE duration !=""
SELECT * FROM TABLE_NAME where COLUMN_NAME <> '';