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*/
CASE statement, not CASE function. So shouldn't it be END CASE instead of END in the SELECT CASE ... part ? –
Motorboat idx rom the first SELECT come from the idx in the second SELECT ? What does the CASE statement try to accomplish ? What does the second SELECT actually do ? And you are doing an inner join, not a cross join, right ? –
Motorboat SELECT * FROM table WHERE * IS NOT NULL AND primary_key="somevalue") –
Heptad 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.
* returns? Maybe provide a bit of example data in your question as it is not clear from your comment above whether this is all one column. –
Barbed 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.
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.
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 = 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.
Yes use NOT NULL in your query like this below.
SELECT *
FROM table
WHERE col IS NOT NULL;
You didn't even have to use an asterisk to get the table fields.
Add condition >0 for int columns and != "" for varchar columns
select column1_id, column2, colummn3, where column1_id>0 and column2 != ""
SELECT duration,id FROM `tickets` WHERE duration !=""
WHERE COALESCE(ALL YOUR COLUMNS) IS NOT NULL
SELECT * FROM TABLE_NAME
where COLUMN_NAME <> '';
© 2022 - 2024 — McMap. All rights reserved.
UNION ... WHERE coln IS NOT NULLstatements but that will scan the table once for each column. MySQL doesn't have anUNPIVOToperator that would help here. So probably the most efficient way would be to do it in your code. The best you can do is exclude rows where all columns areNULL. Are you sure your table stucture is normalised? – Barbed