Web lists-archives.com

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