--- description: The tutorial comprises of brief explanation on NULL value, NOT NULL value, NULL keywords and comparison of NULL values. title: MySQL IS NULL & IS NOT NULL with Examples image: https://www.guru99.com/images/mysql-is-null-and-is-not-null.png --- [Skip to content](#main) **⚡ Smart Summary** MySQL IS NULL and IS NOT NULL are comparison keywords that test whether a column holds a missing value. NULL marks absent data, behaves differently from zero or an empty string, and requires dedicated operators for reliable filtering. * 🧩 **Core Definition:** NULL is a placeholder for data that does not exist. It is not a data type, and it is not the number zero. * ➗ **Arithmetic Behaviour:** Any arithmetic expression that involves NULL returns NULL, so 69 + NULL evaluates to NULL rather than to 69. * 📊 **Aggregate Impact:** COUNT(column) and other aggregate functions skip NULL rows, while COUNT(\*) still counts every row in the table. * 🚫 **NOT NULL Constraint:** Declaring a column NOT NULL rejects any insert that omits a value, which protects mandatory fields such as identifiers. * 🔍 **Correct Filtering:** IS NULL and IS NOT NULL are the only reliable tests, because the equality operator never matches a NULL value. * ⚖️ **Three-Valued Logic:** Comparisons against NULL return UNKNOWN, so SELECT NULL = NULL produces NULL instead of TRUE. [ Read More ](javascript:void%280%29;) ![MySQL IS NULL and IS NOT NULL](https://www.guru99.com/images/mysql-is-null-and-is-not-null.png) In SQL, NULL is both a value and a keyword. Let us look into the NULL value first. [![MySQL IS NULL & IS NOT NULL](https://www.guru99.com/images/null.png)](https://www.guru99.com/images/null.png) ## What is NULL in MySQL? In simple terms, **NULL is a place holder for data that does not exist**. When performing insert operations on tables, there will be times when some field values are not available. In order to meet the requirements of true relational database management systems, MySQL uses NULL as the place holder for the values that have not been submitted. The screenshot below shows how NULL values look in a database table. [](https://www.guru99.com/images/Query.png) Notice that the empty cells are marked NULL, not blank text and not zero. Before going further, look at some of the basics of NULL. * **NULL is not a data type** – this means it is not recognized as an “int”, “date” or any other defined data type. * **Arithmetic operations** involving **NULL** always **return NULL**, for example, 69 + NULL = NULL. * Most **[aggregate functions](https://www.guru99.com/aggregate-functions.html)** **ignore rows that hold NULL values**. The one exception is COUNT(\*), which counts every row regardless of NULL. ## How Aggregate Functions Treat NULL This rule changes the answers that reporting queries return, so let us prove it. Start with the current contents of the members table. SELECT * FROM `members`; Executing the above script gives us the following results. | | | | | ---------------------- | -------------- | | membership_ number | | | full_ names | | | gender | | | date_of_ birth | | | physical_ address | | | postal_ address | | | contact_ number | | | email | | | | | | | 1 | | | Janet Jones | | | Female | | | 21-07-1980 | | | First Street Plot No 4 | | | Private Bag | | | 0759 253 542 | | | janetjones@yagoo.cm | | | | | | | 2 | | | Janet Smith Jones | | | Female | | | 23-06-1980 | | | Melrose 123 | | | NULL | | | NULL | | | jj@fstreet.com | | | | | | | 3 | | | Robert Phil | | | Male | | | 12-07-1989 | | | 3rd Street 34 | | | NULL | | | 12345 | rm@tstreet.com | | | | | 4 | | | Gloria Williams | | | Female | | | 14-02-1984 | | | 2nd Street 23 | | | NULL | | | NULL | | | NULL | | | | | | | | | 5 | | | Leonard Hofstadter | | | Male | NULL | | Woodcrest | | | NULL | | | 845738767 | | | NULL | | | | | | | | | 6 | | | Sheldon Cooper | | | Male | | | NULL | | | Woodcrest | | | NULL | | | 976736763 | | | NULL | | | | | | | | | 7 | | | Rajesh Koothrappali | | | Male | | | NULL | | | Woodcrest | | | NULL | | | 938867763 | | | NULL | | | | | | | | | 8 | | | Leslie Winkle | | | Male | | | 14-02-1984 | | | Woodcrest | | | NULL | | | 987636553 | | | NULL | | | | | | | | | 9 | | | Howard Wolowitz | | | Male | | | 24-08-1981 | | | SouthPark | | | P.O. Box 4563 | | | 987786553 | | | lwolowitz[at]email.me | | | | | The highlighted contact\_ number column holds nine rows in total, but two of them are NULL. Let us count all members who have updated their contact number. SELECT COUNT(contact_number) FROM `members`; Executing the above query gives us the following results. | | | | --------------------- | | COUNT(contact_number) | | | | | 7 | **Note:** the answer is 7 and not 9, because the two NULL values were not included. Running COUNT(\*) on the same table would return 9, since COUNT(\*) counts rows rather than values. ### RELATED ARTICLES * [MySQL SubQuery with Examples ](https://www.guru99.com/sub-queries.html "MySQL SubQuery with Examples") * [MySQL DELETE Query: How to Delete a Row from Table ](https://www.guru99.com/delete-and-update.html "MySQL DELETE Query: How to Delete a Row from Table") * [MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example ](https://www.guru99.com/where-clause.html "MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example") * [SQL Tutorial for Beginners ](https://www.guru99.com/sql.html "SQL Tutorial for Beginners") ## NOT NULL Values A safer approach is to stop NULL from entering mandatory columns at all. That is the job of the NOT NULL constraint. ### What is the NOT Operator? The NOT logical operator is used to test Boolean conditions, and it returns true if the condition is false. The NOT operator returns false if the condition being tested is true. | Condition | NOT Operator Result | | --------- | ------------------- | | True | False | | False | True | ### Why use NOT NULL? There will be cases when we have to perform computations on a query result set and return the values. Performing any arithmetic operation on a column that holds a NULL value returns a NULL result. In order to avoid such situations, we can employ the NOT NULL clause to limit the results on which our data operates. ### Creating a Table with a NOT NULL Column Let us suppose that we want to create a table with certain fields that should always be supplied with values when inserting new rows. We can use the NOT NULL clause on a given field when creating the table. The example shown below creates a new table that contains employee data. The employee number should always be supplied. CREATE TABLE `employees`( employee_number int NOT NULL, full_names varchar(255) , gender varchar(6) ); Let us now try to insert a new record without specifying the employee number and see what happens. INSERT INTO `employees` (full_names,gender) VALUES ('Steve Jobs', 'Male'); Executing the above script in [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) gives the following error, because the mandatory column was left out. [](https://www.guru99.com/images/ErrorCode.png) ## IS NULL and IS NOT NULL Keywords The constraint blocks new NULL values. To work with NULL values that already exist, NULL is used as a keyword. The syntax is as follows. column_name IS NULL column_name IS NOT NULL **HERE** * **“IS NULL”** is the keyword that performs the Boolean comparison. It returns true if the supplied value is NULL and false if the supplied value is not NULL. * **“IS NOT NULL”** is the keyword that performs the opposite comparison. It returns true if the supplied value is not NULL and false if the supplied value is NULL. Let us look at a practical example that uses the IS NOT NULL keyword to eliminate all the rows that hold NULL values in a column. Continuing with the members table above, suppose we need the details of members whose contact number is not NULL. We can execute a query like this. SELECT * FROM `members` WHERE contact_number IS NOT NULL; Executing the above query returns only the seven records where the contact number is present, which matches the COUNT result from the previous section. Now suppose we want the opposite: the member records where the contact number is missing. We can use the following query. SELECT * FROM `members` WHERE contact_number IS NULL; Executing the above query gives the two member records whose contact number is NULL. | | | ------------------ | | | | membership_ number | | full_names | | gender | | date_of_birth | | physical_address | | postal_address | | contact_ number | | email | | | | | | | | | | 2 | | Janet Smith Jones | | Female | | 23-06-1980 | | Melrose 123 | | NULL | | NULL | | jj@fstreet.com | | | | | | 4 | | Gloria Williams | | Female | | 14-02-1984 | | 2nd Street 23 | | NULL | | NULL | | NULL | | | **Warning:** a condition such as WHERE contact\_number = NULL returns an empty result set, even though NULL values exist. The equality operator can never match NULL, so IS NULL is the only correct test. ## Comparing NULL Values with Three-Valued Logic **Three-value logic** – performing Boolean operations on conditions that involve NULL can return **“Unknown”, “True” or “False”**. **Using the “IS NULL” keyword** when doing comparison operations **involving NULL** returns **true** or **false**. Using the other comparison operators returns **“Unknown” (NULL)**. The table below compares each expression side by side. | Expression | Result | Meaning | | -------------------- | ------ | ------- | | SELECT 5 = 5; | 1 | TRUE | | SELECT NULL = NULL; | NULL | UNKNOWN | | SELECT 5 > 5; | 0 | FALSE | | SELECT NULL > NULL; | NULL | UNKNOWN | | SELECT 5 IS NULL; | 0 | FALSE | | SELECT NULL IS NULL; | 1 | TRUE | Compare the number five with itself, then repeat the operation with NULL. SELECT 5 =5; SELECT NULL = NULL; | | | | ----------- | | 5 =5 | | NULL = NULL | | | | | 1 | | NULL | The first result is 1 (TRUE). The second is NULL, because MySQL cannot state that one unknown value equals another unknown value. Now use the IS NULL keyword on the same values. SELECT 5 IS NULL; SELECT NULL IS NULL; | | | | ------------ | | 5 IS NULL | | NULL IS NULL | | | | | 0 | | 1 | This time the answers are definite: 0 (FALSE) and 1 (TRUE). Only the IS NULL and IS NOT NULL keywords return a definite answer when NULL is involved. ## FAQs ⚡ What is the difference between NULL, zero, and an empty string? Zero is a number and an empty string is text, so both match equality tests. NULL means no value was supplied at all, which is why it responds only to IS NULL and IS NOT NULL. 🧮 How do IFNULL and COALESCE replace NULL in a result set? IFNULL(column, ‘N/A’) returns the substitute whenever the column is NULL. COALESCE(a, b, c) returns the first argument that is not NULL. Both are useful inside [MySQL functions](https://www.guru99.com/functions.html) and reports. 🔑 Can a primary key column contain NULL? No. MySQL applies NOT NULL to every primary key column automatically, because a key that identifies a row cannot be missing. A UNIQUE index is different and does permit multiple NULL values. 🤖 Can AI write correct IS NULL conditions from a plain-language question? Usually, yes. AI assistants inside tools such as [MySQL Workbench](https://www.guru99.com/introduction-to-mysql-workbench.html) turn “members with no phone number” into a WHERE column IS NULL clause. Review the filter, because an equality test against NULL silently returns nothing. 🧠 Can AI detect NULL-related bugs in an existing query? Often, yes. AI review tools flag faults such as = NULL comparisons, a [SELECT](https://www.guru99.com/select-statement.html) that averages a column holding NULL, and NOT IN lists containing NULL. The final judgement belongs to the person who knows the data. #### Summarize this post with: ChatGPT Perplexity Grok Google AI **Stay Updated on AI** **Get Weekly AI Skills, Trends, Actionable Advice.** ##### Sign up for the newsletter Subscribe for Free You have successfully subscribed. Please check your inbox. ![AI-Newsletter]() Chosen by over **350,000+** professionals [Scroll to top ](#wrapper)Scroll to top × Toggle Menu Close Search for: Search ```json {"@context":"https://schema.org","@graph":[{"@type":"Organization","@id":"https://www.guru99.com/#organization","name":"Guru99","sameAs":["https://www.facebook.com/Guru99Official","https://twitter.com/guru99com"],"logo":{"@type":"ImageObject","@id":"https://www.guru99.com/#logo","url":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","contentUrl":"https://www.guru99.com/images/guru99-logo-v1-150x59.png","caption":"Guru99","inLanguage":"en-US"}},{"@type":"WebSite","@id":"https://www.guru99.com/#website","url":"https://www.guru99.com","name":"Guru99","publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US"},{"@type":"ImageObject","@id":"https://www.guru99.com/images/mysql-is-null-and-is-not-null.png","url":"https://www.guru99.com/images/mysql-is-null-and-is-not-null.png","width":"700","height":"250","caption":"MySQL IS NULL & IS NOT NULL","inLanguage":"en-US"},{"@type":"BreadcrumbList","@id":"https://www.guru99.com/null.html#breadcrumb","itemListElement":[{"@type":"ListItem","position":"1","item":{"@id":"https://www.guru99.com","name":"Home"}},{"@type":"ListItem","position":"2","item":{"@id":"https://www.guru99.com/sql","name":"SQL"}},{"@type":"ListItem","position":"3","item":{"@id":"https://www.guru99.com/null.html","name":"MySQL IS NULL & IS NOT NULL with Examples"}}]},{"@type":"WebPage","@id":"https://www.guru99.com/null.html#webpage","url":"https://www.guru99.com/null.html","name":"MySQL IS NULL & IS NOT NULL with Examples","dateModified":"2026-07-14T11:02:37+05:30","isPartOf":{"@id":"https://www.guru99.com/#website"},"primaryImageOfPage":{"@id":"https://www.guru99.com/images/mysql-is-null-and-is-not-null.png"},"inLanguage":"en-US","breadcrumb":{"@id":"https://www.guru99.com/null.html#breadcrumb"}},{"@type":"Person","@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen","description":"I'm Marcus Allen, an SQL and Data Warehousing Consultant with over a decade of experience in designing and optimizing large-scale data solutions.","url":"https://www.guru99.com/author/marcus","image":{"@type":"ImageObject","@id":"https://www.guru99.com/images/marcus-allen-author.png","url":"https://www.guru99.com/images/marcus-allen-author.png","caption":"Marcus Allen","inLanguage":"en-US"},"worksFor":{"@id":"https://www.guru99.com/#organization"}},{"articleSection":"SQL","headline":"MySQL IS NULL & IS NOT NULL with Examples","description":"The tutorial comprises of brief explanation on NULL value, NOT NULL value, NULL keywords and comparison of NULL values.","keywords":"sql","speakable":{"@type":"SpeakableSpecification","cssSelector":[".entry-title",".summary"]},"@type":"Article","author":{"@id":"https://www.guru99.com/author/marcus","name":"Marcus Allen"},"dateModified":"2026-07-14T11:02:37+05:30","image":{"@id":"https://www.guru99.com/images/mysql-is-null-and-is-not-null.png"},"copyrightYear":"2026","name":"MySQL IS NULL & IS NOT NULL with Examples","subjectOf":[{"@type":"FAQPage","mainEntity":[{"@type":"Question","name":"What is the difference between NULL, zero, and an empty string?","acceptedAnswer":{"@type":"Answer","text":"Zero is a number and an empty string is text, so both match equality tests. NULL means no value was supplied at all, which is why it responds only to IS NULL and IS NOT NULL."}},{"@type":"Question","name":"How do IFNULL and COALESCE replace NULL in a result set?","acceptedAnswer":{"@type":"Answer","text":"IFNULL(column, 'N/A') returns the substitute whenever the column is NULL. COALESCE(a, b, c) returns the first argument that is not NULL. Both are useful inside MySQL functions and reports."}},{"@type":"Question","name":"Can a primary key column contain NULL?","acceptedAnswer":{"@type":"Answer","text":"No. MySQL applies NOT NULL to every primary key column automatically, because a key that identifies a row cannot be missing. A UNIQUE index is different and does permit multiple NULL values."}},{"@type":"Question","name":"Can AI write correct IS NULL conditions from a plain-language question?","acceptedAnswer":{"@type":"Answer","text":"Usually, yes. AI assistants inside tools such as MySQL Workbench turn \"members with no phone number\" into a WHERE column IS NULL clause. Review the filter, because an equality test against NULL silently returns nothing."}},{"@type":"Question","name":"Can AI detect NULL-related bugs in an existing query?","acceptedAnswer":{"@type":"Answer","text":"Often, yes. AI review tools flag faults such as = NULL comparisons, a SELECT that averages a column holding NULL, and NOT IN lists containing NULL. The final judgement belongs to the person who knows the data."}}]}],"@id":"https://www.guru99.com/null.html#schema-1143547","isPartOf":{"@id":"https://www.guru99.com/null.html#webpage"},"publisher":{"@id":"https://www.guru99.com/#organization"},"inLanguage":"en-US","mainEntityOfPage":{"@id":"https://www.guru99.com/null.html#webpage"}}]} ```