I wrote some hints on how to optimise #SQL queries containing LIKE. More ideas, including unconventional ones, are very welcome - just comment here or on the website.
The problem, though, is that SQL standard treats NULL as unknown or as missing. One word for two meanings.
For example, AVG(col) and COUNT(col) treat it as a missing value (NULLs do not alter the average or the count), but 1 + NULL treats it as an unknown value (the result is unknown). The complete list of NULL-related inconsistencies would be long.
It would have been better to use different words for different semantics, or not implementing NULL at all. Also, in some cases it can lead to poor query performance.