Web lists-archives.com

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