SELECT wat FROM sql

Re:SELECT wat FROM sql by Svip on Monday April 27, 2020 @04:46AM (#22994077)

Wouldn't one just use coalesce()? e.g. coalesce(Field, 1) = 1?

[ Parent | on HN ]

Re:SELECT wat FROM sql by da_chicken on Monday April 27, 2020 @05:50PM (#23000520)

No, that's a bad pattern that should be avoided.

First of all, what happens if the value of Field is actually 1? Did you want that record affected? Because now it will be. It's not exactly clear from your query that you did, is it? This is explicit:

  WHERE (Field = 1 OR Field IS NULL)

And it's not actually that much shorter:

  WHERE COALESCE(Field, 1) = 1

Second of all, if Field is indexed, well, you probably just ignored that index or turned a seek into a scan (i.e., a slower operation). Putting a field into a function will often mean the query engine can't use the index to help with query performance. It means the query engine will have to do a whole lot more work. The term in the industry is "sargable": https://en.wikipedia.org/wiki/Sargable

[ Parent | on HN ]