Re: find any row with NULL

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?

> 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