Do we use NULL or 0 for database?

Question

I am new to database management system and I have a question... Let's say I designed a staff relation/table. In the staff relation, there is a column named "Pay". Some staff members do not have pay, so my question is should I input a value of "NULL" or "0"? Both works but which one is better?

Answer 1

NULL and zero have different meanings. Consider the average pay. We have 6 people, with pay 1 2, 3, 0, 0, and 0. The average pay is 1 (six divided by six).

Or we have 6 people with pay 1, 2, 3, NULL, NULL, and NULL. The average pay is 2 (six divided by three). Which answer is more meaningful in your case?

Answer 2

You clearly have a case of "not applicable", this per your own sentence "Some staff members do not have pay".

Using NULL for "not applicable" is an even worse idea than is using it for "[applicable but] not known". "Unknown" is what SQL intended it to be used for and using it for "unknown" you would "benefit" even if only in the sense of not incurring all the punishments SQL will pour over you if you do the theoretically sound thing, which is to split into two tables.

Suppose it really can be the case that someone who does have a pay, but that pay itself is unknown. How are you going to record that and distinguish it from the case of someone who really does not have a pay at all ?