Shafizadeh Shafizadeh - 7 months ago 8
SQL Question

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

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?

Answer

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.

Comments