Re: find any row with NULL

On 09/07/16 14:13, Hal.sz S.ndor wrote:
> 2016/07/08 09:15 ... Johan De Meersman:
>> You will have to repeat all the column names - no 
>> wildcards in where clause fieldnames - but the clause 
>> you're looking for is WHERE field IS NULL. Or IS NOT NULL 
>> if you want those:-)
>
> Well, one could try this:
> set @sel=(select 'SELECT ' || GROUP_CONCAT(column_name) || 
> ' FROM membership.address WHERE ' || 
> GROUP_concat(column_name || ' IS NULL' SEPARATOR ' OR ') 
> as cmd
> from information_schema.columns where (table_schema, 
> table_name, is_nullable) = ('membership', 'address', 'yes'));
> but changing "membership" and "address" to the right own 
> database and table name. If one goes this way, I advise 
> saving the result in a file to be fetched by 'source', not 
> a variable, because I suspect that one would want to 
> adjust it to one s own situation.
> (After the aforesaid, I did
>     prepare w from @sel;
>     execute w;
> and got 1000 rows out of 1216; only 216 rows have no NULL 
> in any field.)
>
> Maybe Lejeczek really wants to make some of his table s 
> fields NOT NULL?
>
Thanks! I'm not a sqler and what I needed was a working 
piece of a code so I could try to wrap my head around it.
I'd think of something like this as a real world situation:
(and don't mind anything else, eg, poorly design app logic 
or/and DB initial design, etc.)
eg. NULL if exists anywhere in a record would simply mean an 
incomplete thus useless data, which after some time should 
simply be discarded. I thought it would be "sort" of a 
common (certainly not unusual) problem.

shame NULL won't work as in:
 > select user_id,completetion_time from depression where 
NULL in(email_me, other_diagnosis);
or does it?
many! thanks

Now - would searching, saving/storing then removing NULL vs 
ALLbutNULL in a simple DB with one table wich has only 
primary key be sa
>> Remember, NULL is a special value that is not the same as 
>> zero or the empty string; nor to itself: NULL != NULL, by 
>> definition. Personally, I mostly discourage the use of it 
>> (use DEFAULT VALUE in your table definition wherever 
>> possible) except in circumstances where it really is 
>> necessary to know the difference between 'nothing here' 
>> and 'we have no information about this at all'.
>
> In SQL NULL has too many uses. It is as if they got this 
> bright idea of NULL as you describe and all over used it 
> (x/0 ?).
> We have fields "HomePhone", "CellPhone", and "WorkPhone" 
> and allow them to be NULL, not because maybe we don't 
> know, although that sometimes is true, but because in the 
> expression
>     CONCAT_WS(', ', 'c' || CellPhone, HomePhone, 'w' || 
> WorkPhone)
> any NULL argument (after the first) so conveniently 
> completely disappears from the result.
>
> When one makes a field NOT NULL in MySQL one also uses 
> slightly less storage.
>


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql