MySQL Database can store huge amounts of data in tables, sometimes few records are left empty while inserting data in the table. These empty records are called “NULL values” while extracting data, the database administrator wants to select only NOT NULL values to filter the valuable data only for analysis or creating reports.
This post will teach:
- How to Use the “IS NOT NULL” Operator to Select Only NOT NULL Values?
- How to Use the “NULL-safe equal” and “NOT” Operator to Select NOT NULL Values?
Prerequisite: Display Data of MySQL Table
To begin with this post, make sure MySQL is installed in your system. After the installation, Login to your MySQL local server using this syntax in the Command Prompt:
Provide your MySQL username in the syntax and hit “Enter.” Once you have logged in to the MySQL server, change the database in which you want to work by using this syntax:
The success message “Database changed” will display. Now display the data of any table from the database by running the command using this syntax:
Provide the name of the table according to your database. The output will display complete records from the table:
How to Use the “IS NOT NULL” Operator to Select Only NOT NULL Values?
To select only the “NOT NULL” values, the “IS NOT NULL” MySQL operator is used in the “SELECT” statement within the “WHERE” clause. The “IS NOT NULL” operator in MySQL returns true if the value is not empty; otherwise, it returns false. When it is utilized in the “SELECT” statement within the “WHERE” clause, it filters the non-empty records for a defined condition.
The syntax for the “IS NOT NULL” value in the “SELECT” statement is provided below:
For example, the user wants to filter non-empty records from the “employee” table where the “Phone” column is. Type the command given below:
The output is providing the non-empty records in the “Phone” column:
How to Use the “NULL-safe equal” and “NOT” Operator to Select NOT NULL Values?
The “NULL-safe equal” operator aids in comparing NULL values with the records. Its symbol is “<=>”. Its functioning is the same as the “IS NULL” operator which means it returns “TRUE” if the value is null. This operator can be used with the “NOT” operator in the “SELECT” statement to filter the records that are not NULL, using this syntax:
Let’s see an example if the user wants to filter the records from the “employee” table when the “Phone” columns value are “NOT” of the empty records, which means to display only non-empty records by typing the command given below:
The output has filtered all the not NULL records for the “Phone” column:
Note: These operators can be used with Logical operators “OR” and “AND” to extract non-empty records based on multiple conditions defined in the “WHERE” clause.
Conclusion
To select only not NULL values from the MySQL table, the “IS NOT NULL” operator is available, which tests for values and gives “TRUE” if the value is non-empty. Users can also use the “NULL-safe equal” operator with the “NOT” operator to work like “IS NOT NULL” and filter non-empty records when used in the “SELECT” statement. This post discussed how to select only not NULL values in MySQL.
