If columns are explicitly provided in a column list, then all columns listed must be included in the VALUES clause with an explicit value, NULL, or DEFAULT.
Let’s consider DEFAULT because it has much more complex behavior if compared to explicit value or NULL. It may or may not be specified unlike explicit value or NULL.
If specified, DEFAULT will bring default value into the table through INSERT statement.
If not specified, it will also bring same default value into the table. This is what it is for.
We can verify that by querying the table
DEFAULT value “NEW PROMO NAME” has been added twice. ID 12 corresponds explicit DEFAULT usage inside INSERT statement, ID 13 corresponds implicit DEFAULT usage inside INSERT statement.
NULL should always be specified in VALUES part of the INSERT statement when a corresponding column has been specified. Same about explicit value. This is what makes DEFAULT differ from NULL or explicit value usage inside VALUE part of the INSERT statement.
Some people specify DEFAULT and NOT NULL for column definition, as shown below but it is unnecessary, because if a column has a DEFAULT value, then how it can be NULL?
alter table promotions
modify promo_name DEFAULT 'NEW PROMO NAME' NOT NULL;
When DEFAULT keyword is used inside INSERT statement, but no DEFAULT value has been assigned to the column, then NULL will be inserted. This is also a fact worth to remember.
DEFAULT ON NULL means DEFAULT value will be inserted even when NULL is explicitly used in INSERT statement
