Is there a way to avoid empty rows in a SQL statement? My output is:
a | b | c
1 2 3
4
EMPTY EMPTY EMPTY
5
But i want:
a | b | c
1 2 3
4
5
2 Answers
If you really want to check all columns of the table use this:
select *
from the_table
where not (the_table is null);
This will remove all rows where all columns are null.
If you just want to check a subset of the columns (e.g. because there is a generated PK column that you didn't show us), use:
select *
from the_table
where not ( (a,b,c) is null);
1 Comment
James Mapledoram
Side question: Which does the optimizer like more? An exclusive AND operator with a NOT or inclusive OR operators? Or, does it not make a difference? From a pure readability option, I like the where not ( (a,b,c) is null) answer better than the accepted answer just from a readability standpoint. But, maybe it's syntactical sugar?
If the value is empty or if it is null are two different things so I am unsure which you are looking for.
If NULL
SELECT * FROM table WHERE a IS NOT NULL OR b IS NOT NULL OR c IS NOT NULL;
If empty
SELECT * FROM table WHERE a <> '' OR b <> '' OR c <> '';
6 Comments
Matt
take this one step farther and show how you can use ISNULL() or COALESE() to compare empty & null at the same time. WHERE COALESCE(a,'') <> '' AND ....
Matt
@maplemale I am in agreement with duncan on AND. But duncan I just re-read your answer I must of blanked why are you using and is NOT null instead of IS NULL? That would drop everythign except row 1 off. I think you meant to use a IS NULL AND b IS NULL and c IS NULL
duncan
@maplemale You are correct after looking at the question again. I apologize editing now.
Matt
Yes it is OR if using IS NOT NULL, but it is AND if using IS NULL
|
where a<>'EMPTY' && b<>'EMPTY' && c<> 'EMPTY'? or whatever?where a is not null and b is not null and c is not nullbut to know for sure we would need to see sample data and expected results and your current query. There may be away to eliminate the nulls/empty do to something wrong in the current query. Such as an outer join and columns ABC are coming from the table returning only matched records. maybe an inner join should be used instead?