MySQL NULL or NOT NULL That is The Question?

I would suggest

• Use NOT NULL on every field if you can
• Use NULL if there is a sensible reason it can be null

Having fields which don't have a meaningful meaning for NULL nullable is likely to introduce bugs, when nulls enter them by accident. Using NOT NULL prevents this.

The commonest reason for NULL fields is that you have a foreign key field which is options, i.e. not always linked, for a "zero or one" relationship.

If you find you have a table with lots of columns many of which can be NULL, that starts sounding like an antipattern, consider whether vertical partitioning makes more sense in your application context :)

There is another useful use for NULL - making all the columns in an index NULL will stop an index record being created for that row, which optimises indexes; you may want to index only a very small subset of rows (e.g. for an "active" flag set on only 1% or something) - making an index which starts with a column which is usually NULL saves space and optimises that index.

What is the difference between NULL and NOT NULL?

When creating a table or adding a column to a table, you need to specify the column value optionality using either NULL or NOT NULL. NOT NULL means that the column can not have a NULL value for any record; NULL means NULL is an allowable value (even when the column has a foreign key constraint). Because NULL isn't a value, you can see why some call it optionality - because database table requires that in order to have a column, there must be an instance of the column for every record within the table.

And when should they be used?

That is determined by your business rules.
Generally you want as many columns as possible to be NOT NULL because you want to be sure data is always there.

NULL means you do not have to provide a value for the field...

NOT NULL means you must provide a value for the fields.

For example, if you are building a table of registered users for a system, you might want to make sure the user-id is always populated with a value (i.e. NOT NULL), but the optional spouses name field, can be left empty (NULL)