
- Learn MySQL
- MySQL - Home
- MySQL - Introduction
- MySQL - Installation
- MySQL - Administration
- MySQL - PHP Syntax
- MySQL - Connection
- MySQL - Create Database
- MySQL - Drop Database
- MySQL - Select Database
- MySQL - Data Types
- MySQL - Create Tables
- MySQL - Drop Tables
- MySQL - Insert Query
- MySQL - Select Query
- MySQL - Where Clause
- MySQL - Update Query
- MySQL - Delete Query
- MySQL - Like Clause
- MySQL - Sorting Results
- MySQL - Using Join
- MySQL - NULL Values
- MySQL - Regexps
- MySQL - Transactions
- MySQL - Alter Command
- MySQL - Indexes
- MySQL - Temporary Tables
- MySQL - Clone Tables
- MySQL - Database Info
- MySQL - Using Sequences
- MySQL - Handling Duplicates
- MySQL - SQL Injection
- MySQL - Database Export
- MySQL - Database Import
Conditional NOT NULL case MySQL?
For conditional NOT NULL case, you do not need to use <> and = operator. You need to use IS NULL and IS NOT NULL property because NULL is a special case in MySQL.
To understand the conditional NOT NULL case, let us create a table. The query to create a table is as follows:
mysql> create table ConditionalNotNullDemo -> ( -> Id int NOT NULL AUTO_INCREMENT, -> SendMessage longtext, -> PRIMARY KEY(Id) -> ); Query OK, 0 rows affected (0.62 sec)
Insert some records in the table using insert command. The query is as follows:
mysql> insert into ConditionalNotNullDemo(SendMessage) values(NULL); Query OK, 1 row affected (0.16 sec) mysql> insert into ConditionalNotNullDemo(SendMessage) values('Hi'); Query OK, 1 row affected (0.15 sec) mysql> insert into ConditionalNotNullDemo(SendMessage) values('Hello'); Query OK, 1 row affected (0.20 sec) mysql> insert into ConditionalNotNullDemo(SendMessage) values(NULL); Query OK, 1 row affected (0.18 sec)
Display all records from the table using select statement. The query is as follows:
mysql> select *from ConditionalNotNullDemo;
The following is the output:
+----+-------------+ | Id | SendMessage | +----+-------------+ | 1 | NULL | | 2 | Hi | | 3 | Hello | | 4 | NULL | +----+-------------+ 4 rows in set (0.00 sec)
Here is the demo of IS NULL and IS NOT NULL property.
Case 1: If you want to filter all NULL messages, the use IS NULL property. The query is as follows:
mysql> select *from ConditionalNotNullDemo where SendMessage IS NULL;
The following is the output:
+----+-------------+ | Id | SendMessage | +----+-------------+ | 1 | NULL | | 4 | NULL | +----+-------------+ 2 rows in set (0.00 sec)
Case 2: Use of IS NOT NULL property. If you want to display all valid messages apart from NULL message, you can use IS NULL property. The query is as follows:
mysql> select *from ConditionalNotNullDemo where SendMessage IS NOT NULL;
The following is the output:
+----+-------------+ | Id | SendMessage | +----+-------------+ | 2 | Hi | | 3 | Hello | +----+-------------+ 2 rows in set (0.03 sec)
- Related Articles
- Difference Between MySql NULL and IS NOT NULL?
- In which conditions, MySQL CASE statement return NULL?
- CASE WHEN column1 IS NULL THEN NULL ELSE column2 END with MySQL
- Working with NULL and IS NOT NULL in MySQL
- Display only NOT NULL values from a column with NULL and NOT NULL records in MySQL
- Conditional $first in MongoDB aggregation ignoring NULL?
- MySQL CASE statement to place custom values in place of NULL
- What is the benefit of MySQL ‘IS NULL’ and ‘IS NOT NULL’?
- Insert default into not null column if value is null in MySQL?
- Sum if all rows are not null else return null in MySQL?
- In MySQL what is the difference between != NULL and IS NOT NULL?
- Check for NULL or NOT NULL values in a column in MySQL
- Conditional WHERE clause in MySQL stored procedure to set a custom value for NULL values
- Empty string in not-null column in MySQL?
- Removing NOT NULL restriction from column in MySQL?
