Back to articles list March 17, 2021 - 8 minutes read What Is a NULL in SQL? Gustavo du Mortier Gustavo du Mortier is a functional and data analyst at MasterSoft, an Argentinean software company specializing in ERP and healthcare solutions. He’s written many books and articles on different aspects of programming and databases. In his spare time, he plays guitar and helps his two sons build and enhance their gaming computers. Tags: sql learn sql SQL uses NULLs as a special flag that signals the lack of a value for a field or a variable. NULLs should be used wisely so the database gives a faithful picture of the reality it represents. Some people associate NULLs with zeros; others think that NULL means “none”. In SQL, neither of these are true. Simply put, an SQL NULL means that we don’t have a value for that particular field. Maybe we don’t know what the value will be and we need a placeholder, like when an order has been shipped to a customer but not delivered yet; in that case, the delivery date will be NULL until the order has arrived. Or maybe there’s an attribute that is true for some people and not others, e.g. not everyone has a middle name. NULL would work in that case, too. Any field in a table can be defined to allow (or not allow) NULL values, regardless of the data type. Generally, you must define this condition when creating a table, as it can be problematic to change it once the database is in use and the table is filled with data. How to Determine if a Field Should Allow Null Values Let's look at an example table called Springfield_Folks that will store information about characters from The Simpsons TV series. This table will have three columns that store residents’ names, social security numbers, and the names of their spouses (if any). NameSocial_Sec_NoSpouse_Name Apu Nahasapeemapetilon000-00-1000Manjula Nahasapeemapetilon Homer Simpson000-00-5000Marjorie Bouvier Lenford Leonard000-00-3000 Nedward Flanders000-00-4000Maude Flanders Waylon Smithers000-00-8000 The Name and Social_Sec_No fields do not allow NULL values because every US citizen has both pieces of information. (We’ll pretend, for this example, that The Simpsons characters are real people.) However, since not all of the characters have a spouse, the Spouse_Name field should allow for NULL values. Representation of NULL Values When query results display table data, SQL NULL values can be seen in different ways, depending on the visualization tool used. For example, when data from a table is displayed through an Excel spreadsheet, NULL values commonly appear as blank spaces, as in the previous example. The downside to Excel’s approach is that you can't visually distinguish a NULL value from an empty value – which SQL considers entirely different things. If the contents of a table are displayed through a database query tool – such as SQL Server Management Studio – the fields containing SQL NULL values will show the text “NULL”. However, this is just a visual indication, as those fields do not actually contain the text that is displayed. Inserting and Updating Data Containing NULL Values When a NULL is inserted into a table using the SQL INSERT command with the VALUES clause, the word NULL can be used as a literal value. Here’s an example: INSERT INTO Springfield_Folks ( Name, Social_Sec_No, Spouse_Name ) VALUES ( 'Charles Montgomery Burns', '000-00-9000', NULL ) It is important to note that if a column is omitted in an INSERT statement, SQL automatically assigns a NULL value to that column. Taking this into account, the example above could be rewritten as follows and achieve exactly the same result: INSERT INTO Springfield_Folks ( Name, Social_Sec_No ) VALUES ( 'Charles Montgomery Burns', '000-00-9000' ) In the same way as we've seen for the INSERT statement, the word NULL can be used as a literal when updating a field in a table: UPDATE Springfield_Folks SET Spouse_Name = NULL WHERE Name = 'Nedward Flanders' If an attempt is made to assign a NULL value using either INSERT or UPDATE to a field that does not allow NULLs, the database engine raises an error and cancels the operation. For example, in our Springfield_Folks table, we have specified that the Name field does not allow NULL values. Therefore, this INSERT ... INSERT INTO Springfield_Folks ( Name, Social_Sec_No, Spouse_Name ) VALUES ( NULL, '000-00-1100', 'John Doe' ) … will cause SQL to cancel the operation and display an error similar to this one: Cannot insert the value NULL into column 'Name', table 'Springfield_Folks'; column does not allow nulls. INSERT fails. The statement has been terminated. If, instead of inserting or updating the field with a literal value, we take the content from another field or table, we must consider the possibility that the field where we take the data from could have NULL values. For example: INSERT INTO Springfield_Folks ( Name, Social_Sec_No, Spouse_Name ) SELECT Name, Social_Sec_No, Spouse_Name FROM Springfield_Newcomers In this example, we are inserting new rows in the Springfield_Folks table, taking them from another table named Springfield_Newcomers. If this table allows NULLs in the Social_Sec_No field, we could have trouble when inserting the data in our table. To prevent errors, we need to verify if a field has a NULL value. But first, let's see how NULL values behave in comparisons. Comparisons with NULLs As we’ve seen in the previous examples, the word NULL can be used as a literal value. But when doing so we must be cautious, since the use of NULL as a literal can be somewhat tricky. To begin with, the NULL value has no type: it can be assigned to a field of the VARCHAR, NUMERIC, DATE, or whatever type. On the other hand, it is an absorbing value: a NULL value used in any operation also results in a NULL value. And most confusing of all: any comparison with a NULL value yields false, since NULL is not equal to anything, not even to itself. Mindblowing, right? Let’s clarify this idea by seeing how these comparisons behave when querying our Springfield_Folks table: SELECT * FROM Springfield_Folks WHERE Spouse_Name = NULL As you may have guessed, this query returns zero rows. You may think that, if there are no rows that meet the condition of Spouse_Name = NULL, then all rows must meet the opposite condition: Spouse_Name <> NULL. Let’s try it: SELECT * FROM Springfield_Folks WHERE Spouse_Name <> NULL Also zero rows! No, your database is not trying to trick you. It’s just the way NULL works. Whatever you compare to a NULL value will return false with any comparison, be it <, <=, >, >=, or LIKE. So if comparing a NULL-valued field with anything always returns false, how can we tell if a field or variable is NULL? Simple: by asking if it IS NULL. Let’s make a few small tweaks to the SELECT from the above example to make it work: SELECT * FROM Springfield_Folks WHERE Spouse_Name IS NULL NameSocial_Sec_NoSpouse_Name Lenford Leonard000-00-3000Manjula Nahasapeemapetilon Waylon Smithers000-00-8000Marjorie Bouvier This gives us the correct result. In the same way, we could ask for IS NOT NULL and it would also give us the correct result for our second query: SELECT * FROM Springfield_Folks WHERE Spouse_Name IS NOT NULL NameSocial_Sec_NoSpouse_Name Apu Nahasapeemapetilon000-00-1000Manjula Nahasapeemapetilon Homer Simpson000-00-5000Marjorie Bouvier Nedward Flanders000-00-4000Maude Flanders How to Filter Columns with NULL Values When filtering data from a table using a nullable column in the WHERE condition, all rows with a NULL value in that column will be excluded, no matter what the WHERE condition is. The exception is when you ask specifically for IS NULL values, as we have already seen. Examine this SELECT to better understand how this works: SELECT * FROM Springfield_Folks WHERE Spouse_Name LIKE 'M%' You can try different literals to see that, no matter what value you compare to the column values, rows with a NULL value will be excluded. Sorting data in nullable columns also has its secrets. If you need to sort a query by a column that allows NULL values, see this article on how SQL orders NULL values. Giving Meaning to NULL Values We can add a little bit more information by defining a column called Marital_Status in the SELECT list that will show the text “Married” when Spouse_Name is not NULL and “Single” when Spouse_Name is NULL: SELECT *, 'Married' AS Marital_Status FROM Springfield_Folks WHERE Spouse_Name IS NOT NULL SELECT *, 'Single' AS Marital_Status FROM Springfield_Folks WHERE Spouse_Name IS NULL This way, we are helping the user better interpret the meaning of the NULL values in the Spouse_Name field. Going a little further, we can combine the two previous examples into one query with the UNION clause: SELECT *, 'Married' AS Marital_Status FROM Springfield_Folks WHERE Spouse_Name IS NOT NULL UNION SELECT *, 'Single' AS Marital_Status FROM Springfield_Folks WHERE Spouse_Name IS NULL By doing this, we can get all the Springfield folks, either married or single, in one result set: NameSocial_Sec_NoSpouse_NameMarital_Status Apu Nahasapeemapetilon000-00-1000Manjula NahasapeemapetilonMarried Homer Simpson000-00-5000Marjorie BouvierMarried Nedward Flanders000-00-4000Maude FlandersMarried Lenford Leonard000-00-3000Single Waylon Smithers000-00-8000Single Deleting Rows that Have Null Values If we need to eliminate all rows that have a NULL value in a certain column, we can use the IS NULL criterion in the WHERE clause of the DELETE command. For example, if we want to delete all rows from our Springfield_Folks table that have a NULL value in Spouse_Name, we can execute this command: DELETE Springfield_Folks WHERE Spouse_Name IS NULL The Usefulness of NULL At this point, it should be clear what SQL NULL means in the design and use of information stored in databases. Null elements are very useful for representing real-life situations where a piece of information is absent. But they should be used with caution to avoid adversely affecting the integrity of the information contained in the database. You can practice what you’ve learned so far about SQL NULL values (and learn a little more) in our interactive SQL Practice course and our extensive SQL Practice Set. If you don’t have too much time for learning, check out how to learn SQL fast. Need to get deeper into SQL practice? You will surely find plenty of resources in these 12 ways to practice SQL online. Tags: sql learn sql You may also like Difference between GROUP BY and ORDER BY in Simple Words For someone who's learning SQL, one of the most common concepts that they get stuck with is the difference between GROUP BY and ORDER BY. Read more NULL Values and the GROUP BY Clause We've already covered how to use the GROUP BY clause but how does SQL's GROUP BY clause work when NULL values are involved? Find out! Read more Useful SQL Patterns: Matching Nulls by Masking Nulls Today, in the first post of the SQL patterns series, we will consider the match by null pattern. It’ll help you deal with tables containing null values. Read more How to Tackle SQL NULLs: COALESCE function Let's introduce the COALESCE postgresql function. It's inevitable that some data in the database has no value. Find out what then with MySQL. Read more Understanding the Use of NULL in SQL Three-Valued Logic NULLs are necessary in databases, learning to use them is fundamental to SQL success. However, NULLs should be handled with care – see how! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.