2
I always used database (Mysql) for small projects and never cared about the option "NULL when empty", IE, was blank even.
Now I’m designing a big system, I’d like to know the concept of using value NULL when the field is blank, for what purpose, what advantage and disadvantages etc.
3
In SQL queries, NULL behaves differently when using certain functions, e.g.:
A table with:
| ID | Cliente | Valor Gasto |
| 01 | JOAO | 100 |
| 02 | PEDRO | NULL |
| 03 | MARIA | 50 |
When executing
SELECT SUM(Valor_Gasto) * 100 / COUNT(ID) FROM tabela
SELECT SUM(Valor_Gasto) * 100 / COUNT(Valor_Gasto) FROM tabela
In the first query COUNT is equal to 3 and you receive the value 50, in the second COUNT is equal to 2 and you receive 75, because null is ignored in the count.
Nothing much, but depending on how you want to calculate, this avoids using a validation in WHERE.
Or when you validate with <> (other than) or IS NOT, ex:
SELECT Cliente FROM tabela WHERE Valor_Gasto <> 50
This would bring feathers Joao, because Pedro has value NULL, would have to treat the column or use a function that considers null, I do not know for all languages but in Postgres can use:
SELECT Cliente FROM tabela WHERE Valor_Gasto IS DISTINCT FROM 50
OR (this think is universal)
SELECT Cliente FROM tabela WHERE COALESCE(Valor_Gasto,0) <> 50
In the above query it exchanges NULL for 0, then the Spent Value will never be NULL, I hope to have helped somehow
2
First read the question that talks about NULL. Use it the right way. NULL is NULL, empty is empty. NULL indicates the index of the value.
I would have to measure if there is any difference in performance in several situations. I can almost guarantee that there is nothing noticeable. Which can cause some problem, but not too big if you use or fail to use it mistakenly. It will still be a side effect. Don’t worry about performance, do the right thing, give the proper semantics to the data.
You should only use it where you can have indeterminate values in that column. By default it is better that the column does not accept NULL, because it is ideal that there are no indeterminate values. But often the ideal does not fit into reality and we have to use it.
Browser other questions tagged mysql sql database performance null
You are not signed in. Login or sign up in order to post.