# NULL meaning in MySQL



## maccatalan (Jul 15, 2002)

Hi.

I'm just trying to understand what NULL means as property of a column in MySQL.

For example, when I do :

CREATE TABLE `toto` (
`c1` INT NOT NULL, 
`c2` INT NULL
);

what's the difference beetwen c1 and c2 (excluding the name) ?

I think that means I can put an empty field into c2 and not in c1, but am I right ?

Thank you,
Pierre.


----------



## iconara (Jul 16, 2002)

try the DESCRIBE function:


```
> DESCRIBE toto;
+-------+---------+------+-----+---------+-------+
| Field | Type    | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+-------+
| c1    | int(11) |      |     | 0       |       |
| c2    | int(11) | YES  |     | NULL    |       |
+-------+---------+------+-----+---------+-------+
```

The first field cannot be null (the "Null" column is not "YES") and the second column is null by default (the "Default" column...)



theo


----------



## maccatalan (Jul 16, 2002)

thank you but I found the answer. Here it is since your last is not the answer I was waiting for (but thank you anyway) :

NULL -> the field in an Insert can be null (no value)
NOT NULL -> the field must contain some data when Insert is called.

Pbl : with some bases NULL can implies some problems with Select methods.


Sorry for this question, it was so easy (maybe too much easy  )
Pierre


----------



## iconara (Jul 16, 2002)

I think you got it wrong


```
c2 INT NULL
```

is (most probably) the same as 


```
c2 INT DEFAULT NULL
```

which means that the default value of the field should be NULL
which is the same as


```
c2 INT
```

(since any field will, by default, be NULL if not explicitly given a value)


this site needs a webprogramming board (I have made the suggestion to the admin... but no response)


theo


----------



## maccatalan (Jul 16, 2002)

you're right. the three works, but it was just to be explicit. Writing NULL in the definition (even if it is by default null) I do insist on it (for the reader, not the MySQL server who knows it is the same thing  )

-> good idea of creating a Web Programming board


----------



## scooby (Jul 17, 2002)

Also note that: According to the SQL standard, "NULL" is the default.

BTW, avoid NULLs if at all possible. It makes logic confusing because certain axioms don't work.

For example, p OR NOT p is always TRUE, right? Not if you allow NULLs: NOT NULL is NULL, so NULL OR NOT NULL is NULL.

In theory, you can always get rid of NULLs even if you don't have a valid DEFAULT value. Remember that a field with a NULL is a repeating field that can repeat 0 or 1 times.

So it's not really in "first normal form." (1NF dictates that, among other things, there should be no repeating fields.)

What you can do is break that field off into a little table of its own, just copying the primary key over.

Now, this is all in theory. In practice this can make queries difficult to express or hurt performance because of limitations with the DBMS and the SQL standard. However, be forewarned that it is poor logical design to use NULLs because you might make logical assumptions that don't hold up, and three-valued logic (logic with TRUE, FALSE, NULL) is so obscure it's not even taught in school. You have to work it all out by hand, which is tedious and error-prone.

So if you do use NULLs and get wierd behavior, that's going to be the first thing you should check for bugs.


----------

