Is leaving a field empty better or filling it with null?

Question

I have a table that contains a column named first_name. It is not mandatory for users to fill it, and sometimes users leave it empty. Now I want to know: is it better to define null value as its default?

If your users send empty data, insert it as NULL or leave the column out in your insert statement. Ex: `insert into tablename (id, last_name) values (123, 'Smith');` or `insert into table (id, first_name, last_name) values (123, NULL, 'Smith');` — zedfoxus, Dec 29 '15 at 22:27
possible duplicate of/answer http://stackoverflow.com/questions/5618357/sql-server-null-vs-empty-string — Dimitar, Dec 29 '15 at 22:28
Your choice. Choose one and stay consistent throughout your application. I would use the 2nd one personally where I explicitly use NULL for first_name. — zedfoxus, Dec 29 '15 at 22:29
I'm sure this is a duplicate, but unlike the linked answer, it should not depend on silly database details. Instead it should model the data however that is defined. For example, is there a difference between *never asking* for a first name and *asking, but not getting a response for* a first name? — user2864740, Dec 29 '15 at 22:31
To expand on what @user2864740 is saying... for a related example, a middle_name field could be null to indicate "unknown" and blank to indicate it is known that the person has no middle name. — Uueerdo, Dec 29 '15 at 22:35

zedfoxus · Accepted Answer · 2015-12-29T22:45:26.970

Consider the following table:

create table test1 (
  id int not null,
  first_name varchar(50), -- nullable
  last_name varchar(50)   -- also nullable
);

If first_name is not provided in your UI, you can choose to not insert data into that field by doing:

insert into test1 (id, last_name) values (123, 'Smith');

Or, you can choose to explicitly provide NULL for first_name like so:

insert into test1 (id, first_name, last_name) values (123, NULL, 'Smith');

-- you could also do like this below:
-- insert into test1 values (123, NULL, 'Smith');
-- I just like providing explicit fieldnames and values

Either way you choose, just stay consistent throughout your application. Your results will look the same:

+-----+------------+-----------+
| id  | first_name | last_name |
+-----+------------+-----------+
| 123 | NULL       | Smith     |
| 123 | NULL       | Smith     |
+-----+------------+-----------+

So - to answer the real question: don't define an explicit null in your table creation.

When supplying '' or NULL, just make sure you are consistent. If some first_name are '' and some are NULL, your select statement would have to be:

select * from test1 where first_name is NULL or first_name is '';

That brings another point - what if user typed ' ' (4 spaces)? You would have to ensure that first_name meets certain criteria and trimmed version of first_name goes through validation before being entered in the database. If your database ends up with '', ' ', ' ' etc. you would have to constantly run:

select * from test1 where first_name is NULL or trim(first_name) = '';
--or--
--select * from test1 where first_name is NULL or length(trim(first_name)) = 0;

Consistency with NULL first_name will help querying with confidence.

score 3 · Answer 2 · answered Dec 29 '15 at 23:30

If you want the default value for your column to be NULL, then write DEFAULT NULL.

It's all well and good saying that shorter code is better and we can rely on the "default default" to do this job for us, but the fact that every single existing answer on this question leaves you prone to a potentially catastrophic bug is proof of what a terrible approach that really is!

Take a look at this:

CREATE TABLE `test` (`ts` TIMESTAMP);
SHOW CREATE TABLE `test`;

-- Result:
CREATE TABLE `test` (
  `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Woah! What's that DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP? That's what you get with a TIMESTAMP field with NOT NULL set and no explicit default. Whenever you update a row in that table, the timestamp column will be reset to "now". This catches lots of people out, and is a great example of why making assumptions is a terrible practice.

So, if you're going to study the manual and you are absolutely confident that omitting DEFAULT NULL will result in the semantics you intended, then go right ahead; but I posit that it really costs you nothing to just say what you mean.

score 1 · Answer 3 · answered Dec 29 '15 at 22:25

1

Defining default null is superfluous. If you do not have an explicit default defined, the column will default to null if no value is supplied. Some people and project standards like explicitly stating these null defaults, but it serves no real purpose, and is a matter of taste more than anything else.

answered Dec 29 '15 at 22:25

Mureinik

297,002
52
306
350

2

Try that on a `TIMESTAMP` column. – Lightness Races in Orbit Dec 29 '15 at 22:25
@LightnessRacesinOrbit What do you mean exactly of "TIMESTAMP" column? – Shafizadeh Dec 29 '15 at 22:31
1

Lightness is saying that `create table test2 (id int not null, ts timestamp default null);` will throw an error `ERROR 1067 (42000): Invalid default value for 'ts'` – zedfoxus Dec 29 '15 at 22:39
@zedfoxus: No, I'm pointing out that [omitting the explicit default for `TIMESTAMP` columns is not the same as writing `DEFAULT NULL`](http://sqlfiddle.com/#!2/7e996/1), making the latter far from "superfluous" (though this seems to have changed as of MySQL 5.6). – Lightness Races in Orbit Dec 29 '15 at 23:21
Aha, I apologize for misunderstanding. Thank you for helping me understand. @LightnessRacesinOrbit – zedfoxus Dec 30 '15 at 00:52
The OP was about a name column – Mureinik Dec 30 '15 at 14:48

score 1 · Answer 4 · answered Dec 29 '15 at 22:29

1

Question is Null better or not?

As per my experience in your mentioned scenario you have to put default empty value instead of null. In this way you can save you in scripting to check its null or not you can just check it empty or not checking is null is bit tricky.

Any how its my opinion.

answered Dec 29 '15 at 22:29

Hassan Ali Shahzad

2,439
29
32

1

Checking for NULL is not "tricky" and it is rather easy to put a constraint on the column in one way or the other. The big issue comes down to design and how the data is modeled - in particular 1) is the difference between no data and unknown data? 2) how is such access treated consistently? – user2864740 Dec 29 '15 at 22:33
@user2864740 Hassan's mention of having to deal with null values is somewhat relevant, as it not only affects code but lengthens `CONCAT(first_name, ' ', last_name)` to `CONCAT(IFNULL(first_name, ''), ' ', IFNULL(last_name, ''))`. It is not a difficult thing to do, but can require some vigilance. – Uueerdo Dec 29 '15 at 22:39
yeh I want to highlight exactly the same thing. null has its own importance depend on scenario but @Shafizadeh high light the point empty is fine. – Hassan Ali Shahzad Dec 29 '15 at 22:45
See also `COALESCE(...)` – Rick James Jan 05 '16 at 01:57

score 1 · Answer 5 · answered Dec 29 '15 at 22:32

1

Using NULL is perfectly valid if it means that a field was not given or is unknown. It makes no difference really, it's a matter of taste, but sometimes is very useful to knoww when something was not given rather than querying empty or blank fields.

answered Dec 29 '15 at 22:32

lumee

613
1
5
15

Is leaving a field empty better or filling it with null?

5 Answers5