NULL vs NOT NULL

Question

In sql database column, empty fields will be set to NULL unless we specify it using NOT NULL. What are the advantages of using one instead of the other? Thanks!

Ummmm....do you want to make sure that a column's values aren't null? Then put a `not null` constraint on that column. If you don't care, then don't. — , Jun 07 '12 at 14:17
If you specify a column to be `NOT NULL` then is ***can't*** be empty. Your quesiton is self contradictory. — MatBailie, Jun 07 '12 at 14:18
@Dems: empty and NULL are different things; many rdbms's can set a NOT NULL field to an empty string, 0, etc., so that's not entirely accurate. — Jeremy Holovacs, Jun 07 '12 at 14:26
@JeremyHolovacs - I'd dispute that as a blurring of symantics. Although some RDBMS use empty strings for NULL in string types, there is *no* equivalent corner case for other data types. What this equates to is not a differentiation of "NULL" and "empty". It simply means that those RDBMS do not treat a zero length string as an actual value. *(In virtually every other sphere a zero length string is still a string.)* So, I would contend that if "empty" has any meaning, it *is* a synonym for NULL. And that, in certain cases, a zero length string is not a value at all (and so is Empty or NULL). — MatBailie, Jun 07 '12 at 14:59
@Dems, I understand where you're coming from, it is a semantics thing, but the definitions for "empty" and "null" are pretty well defined, and, specifically, in the context of this question, a column defined as `NOT NULL` absolutely can be empty. — Jeremy Holovacs, Jun 07 '12 at 15:07
@JeremyHolovacs - In Oracle; if a VARCHAR2() field is constrianed as being NOT NULL, then you can't insert a zero length string into that field. Either `''` is a value and so not empty *(SQL Server, etc)*, or `''` is treated as NULL and so can't be placed in a NOT NULL field *(Oracle)*. I can't see any case (symantically, logically, in practice, etc) where a NOT NULL field can ever be "empty". — MatBailie, Jun 07 '12 at 15:14
@Dems: ... `''` *is* empty. That's pretty much the definition of empty. NULL means missing or unknown per http://en.wikipedia.org/wiki/Null_%28SQL%29, but not empty, which is, as you said, a value, but still empty. I don't know what to tell you; I use empty values in not nullable columns all the time. It means something other than `NULL`. All of this goes back to the fact that `NOT NULL` values **can**, in fact, be empty. If you want to change the definitions of these terms, go for it, but everyone else is going to look at you funny. — Jeremy Holovacs, Jun 07 '12 at 15:22

score 11 · Accepted Answer · answered Jun 07 '12 at 14:18

11

If you need to represent unknown data in a column, you make it nullable. If you will always have data in the column, it's better to make it not nullable, as

Dealing with nulls can be annoying and counterintuitive
It saves a bit of space
On some database systems, null values are not indexed.

answered Jun 07 '12 at 14:18

Jeremy Holovacs

22,480
33
117
254

This might be relevant further reading for the OP: http://www.bennadel.com/blog/85-Why-NULL-Values-Should-Not-Be-Used-in-a-Database-Unless-Required.htm – kush Jun 07 '12 at 14:19
Saves on space? I am not sure if this is a valid point. See [Previous SO question](http://stackoverflow.com/questions/556363/space-used-by-nulls-in-database) and a [SQL-Server Example](http://sqlfiddle.com/#!3/3c962/3). I fully agree with your first point though. – GarethD Jun 07 '12 at 14:40
@GarethD, a table with NULLs in SQL Server uses something called a NULL bitmap... it's one byte per row for every 8 nullable columns. Not a lot of space, but some. It will add up on very large tables; but it's not generally considered a significant amount. – Jeremy Holovacs Jun 07 '12 at 14:42

score 3 · Answer 2 · answered Jun 07 '12 at 14:17

3

When a field is set to NOT NULL, it cannot be empty. Which means you have to specify a value for that field when inserting a record.

answered Jun 07 '12 at 14:17

Superman

3,027
1
15
10

2

`NOT NULL` columns can contain "empty" values. – Ash Burlaczenko Jun 07 '12 at 14:24
@Ash: `NOT NULL` columns that are `CHAR` or `VARCHAR` yes, they can contain empty strings - which is a value. But not "empty values" (whatever that means). An `INT NOT NULL` can not be empty in any way (unless you mean that it has the value `0`) – ypercubeᵀᴹ Jun 07 '12 at 14:33
1

`NULL` examples: for `INT` we have `0`; for `CHAR` or `VARCHAR` we have `""`, what is an empty string but is still a value. @ypercubeᵀᴹ, is that right? – Davidson Lima Mar 08 '18 at 13:18

score 1 · Answer 3 · answered Jun 07 '12 at 14:19

Just to clarify your pointÖ Empty strings are not being set to NULL in any database that conforms to the ANSI standard for SQL. NULLs are not the same as empty strings. Columns given an explicit NULL value or no value are assigned NULL.

Here are a couple of advantages of not using NULLs. You save the space for the bit used to differentiate the NULL value. You also make the meaning of statements, such as "field <> value" more clear. Also, some databases have trouble optimizing statements such as "coalesce(field, '') <> value" to use an index.

Sometimes, NULLs are needed, particularly when you are inserting incomplete records. And, because by default columns allow NULLs, laziness ensures that almost all columns in all databases do accept NULL values.

score 0 · Answer 4 · answered Jun 07 '12 at 14:20

NOT NULL obligate to specify particular value for the field.

use NULL when you need to keep empty values for the field.

use NOT NULL when you want forbid empty values for the field. Additional goal that the most DBs works slowly with NULL-values. So if you doubt use NOT NULL.

score 0 · Answer 5 · answered Jun 07 '12 at 14:20

NOT NULL is a constraint which ensures there is a value in the column for every row. This is good practice when applied correctly because you have to be careful with null values in tables when doing operations like joins because no null is equal to any other null

http://en.wikipedia.org/wiki/Null_(SQL)

NULL vs NOT NULL

5 Answers5

Linked

Related