When storing missing data, should I store them as NULL?

jephos249 · July 28, 2018, 6:22am

Question

When storing missing data, should I store them as NULL?

Answer

It can depend entirely on how you need the data to be stored and utilized.

Let’s say that you have a table of employee information, which included their address. Say that we wanted to check all rows of this table and find where any addresses are missing. If we stored the addresses as TEXT values, we might choose to store all the missing values as either '' or as NULL.

If we stored the missing address values as an empty string '' then these values are not NULL. Empty strings are seen as a string of length 0. So, if we ran a query using

WHERE address IS NULL

it would not give us the rows with missing address values. We would have to check using

WHERE address = ''

With a table containing many different data types, it may be helpful and more convenient to store any missing values in general as just NULL so that we can utilize the IS NULL and IS NOT NULL operators.

callmemusashi · April 29, 2019, 5:25am

Is there a way to conveniently convert empty string values to NULL?

mtf · April 29, 2019, 6:13pm

I don’t think it is possible to convert an empty string to NULL during a query. It would have to be set as such when creating a row.

masakudamatsu · August 18, 2019, 2:08am

We can use UPDATE for converting an empty string into NULL:

UPDATE movies
SET imdb_rating = NULL
WHERE imdb_rating = '';

I have tested this code on this lesson’s code editor, and it works. (I wasn’t sure whether = NULL or IS NULL works for SET, and I have learned that = NULL works, but IS NULL doesn’t.)

blogsolver36579 · October 21, 2019, 8:09pm

How to store address in your example as Null instead of ’ ’ empty String

CREATE TABLE employees (
    employee_id INT AUTO_INCREMENT PRIMARY KEY,
    address TEXT NOT NULL
);

leidymartnez27324446 · December 25, 2019, 2:49pm

It says here that if i insert a row without a value for that column, in this case address, it won’t let me introduce the entire row.Therefore, i think that’s not the way to introduce NULL values in a row.

Correct me if i’ wrong

leidymartnez27324446 · December 25, 2019, 3:03pm

I have an example:

As you can see, the row was not inserted.

I think the only way to introduces empty values is typing NULL when inserting rows.

guiwald · February 16, 2020, 3:16pm

If your constraint for the column “address” is that it has to be not null, then you won’t be able to insert a line where the address is null as it would violate the constraint

If you want to be able to insert lines with no address (living the field as null) then you should not add the constraint when declaring address TEXT NOT NULL

css2194399306 · June 18, 2020, 1:54pm

what i Observed was, when you are inserting the first row of the table, that should have all the values of the columns but after that i was able to select only those columns in which i wanted to insert my values, ignoring those in which i didnt want to insert any value.

teramontgomery279805 · February 5, 2021, 1:49am

Yes, you are correct! Using NOT NULL is actually placing a constraint for that data, meaning you’re applying certain rules to that set of data. In this case, you’re telling the interpreter that the text data for the address column CANNOT be NULL. In other words, you can not leave that column entry empty when inputting a new record.

teramontgomery279805 · February 5, 2021, 1:50am

leidymartnez is correct! Using NOT NULL is actually placing a constraint for that data, meaning you’re applying certain rules to that set of data. In this case, you’re telling the interpreter that the text data for the address column CANNOT be NULL. In other words, you can not leave that column entry empty when inputting a new record.

pyjumper40913 · February 23, 2021, 5:44am

It is important to remember that during the create, if you don’t set a default value or a “NOT NULL” then the default value will be a “NULL” value. The problem is when you have end users who just enter a space or a punctuation and you’re trying to just find actual NULL values. Sometimes you have to search for NULL and for an empty string (my max so far is a user who lines to enter 20 spaces)

lord_k · April 15, 2021, 7:48pm

seems SQL engine in the exercise does not allow to do it, but in other engines it possible to use if/case condition in select statement to substitute values in output set with desired ones…

byte0836313329 · September 29, 2021, 8:43am

Changing ‘’ , an empty value, to “NULL” only changes it to another text or string value. That is why WHERE imdb_rating = ’ '; works, but IS NULL doesn’t. IS NULL is meant for true NULL values where = seems like it is meant to capture text.

ruronite · February 1, 2023, 6:48am

@callmemusashi @mtf @leidymartnez27324446 @byte0836313329 @teramontgomery279805
It’s possible to convert an empty string to a NULL value using update, PROVIDED WHEN THE TABLE WAS CREATED NOT NULL CONSTRAINT WASN’T APPLIED.
Also the value “” seems to store as NULL. while " " stores as an empty string.
He’s a picture to show how you can achieve this.

juniorchuang · June 28, 2023, 1:04am

@mtf as others have pointed out, it is possible to convert it using the UPDATE statement. It doesn’t nocessarily have to happen when it was created.

juniorchuang · June 28, 2023, 9:21pm

@css2194399306 I don’t think this is true if I understand you correctly. As long as the CREATE table was done correctly, the first row (and the rest of your data) doesn’t necessarily have to have complete values for the columns, with the exception of the columns that were created with the parameter NOT NULL. The first row doesn’t dictate the format for the rest of the table - it’s the underlying parameter defining each column.