I have always used a MySQL database for small projects and I never cared about the option "NULL when it is empty", that is, it was blank.
Now I'm designing a large system, I would like to know the concept of using NULL value when the field is blank, what is it for, what is the advantage and / or disadvantages, etc.
In SQL queries, NULL behaves differently when using certain functions, eg
A table with:
| ID | Cliente | Valor Gasto |
| 01 | JOAO | 100 |
| 02 | PEDRO | NULL |
| 03 | MARIA | 50 |
When running
SELECT SUM(Valor_Gasto) * 100 / COUNT(ID) FROM tabela
SELECT SUM(Valor_Gasto) * 100 / COUNT(Valor_Gasto) FROM tabela
In the first query COUNT equals 3 and you get the value 50, already in the second COUNT equals 2 and you get 75 because null is ignored in the count.
Not too much, but depending on how you want to calculate, this avoids using validation in WHERE.
Or when you do validation with < > (other than) or IS NOT, eg:
SELECT Cliente FROM tabela WHERE Valor_Gasto <> 50
This would bring penalties to Joao, since Pedro has NULL value, would have to treat the column or use a function that considers the null, do not know for all languages but in Postgres can use:
SELECT Cliente FROM tabela WHERE Valor_Gasto IS DISTINCT FROM 50
OR (this is universal)
SELECT Cliente FROM tabela WHERE COALESCE(Valor_Gasto,0) <> 50
In the above query it trades NULL for 0, then the Expended Value will never be NULL, I hope I have helped in some way
First read the question that talks about NULL . Use it the right way. NULL is NULL, empty is empty. NULL indicates the value indetermination.
You would have to measure if you have any performance differences in various situations. I can almost guarantee that there is nothing perceptible. This may cause some problem, but not too great if you use or stop using it wrongly. Even so, it will be a side effect. Do not worry about performance, do the right thing, give the right semantics for the data.
You should only use it where you can have undetermined values in that column. By default it is best if 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.