- SQL Tutorial
- SQL - Home
- SQL - Overview
- SQL - RDBMS Concepts
- SQL - Databases
- SQL - Syntax
- SQL - Data Types
- SQL - Operators
- SQL - Expressions
- SQL Database
- SQL - Create Database
- SQL - Drop Database
- SQL - Select Database
- SQL - Rename Database
- SQL - Show Databases
- SQL - Backup Database
- SQL Table
- SQL - Create Table
- SQL - Show Tables
- SQL - Rename Table
- SQL - Truncate Table
- SQL - Clone Tables
- SQL - Temporary Tables
- SQL - Alter Tables
- SQL - Drop Table
- SQL - Delete Table
- SQL - Constraints
- SQL Queries
- SQL - Insert Query
- SQL - Select Query
- SQL - Select Into
- SQL - Insert Into Select
- SQL - Update Query
- SQL - Delete Query
- SQL - Sorting Results
- SQL Views
- SQL - Create Views
- SQL - Update Views
- SQL - Drop Views
- SQL - Rename Views
- SQL Operators and Clauses
- SQL - Where Clause
- SQL - Top Clause
- SQL - Distinct Clause
- SQL - Order By Clause
- SQL - Group By Clause
- SQL - Having Clause
- SQL - AND & OR
- SQL - BOOLEAN (BIT) Operator
- SQL - LIKE Operator
- SQL - IN Operator
- SQL - ANY, ALL Operators
- SQL - EXISTS Operator
- SQL - CASE
- SQL - NOT Operator
- SQL - NOT EQUAL
- SQL - IS NULL
- SQL - IS NOT NULL
- SQL - NOT NULL
- SQL - BETWEEN Operator
- SQL - UNION Operator
- SQL - UNION vs UNION ALL
- SQL - INTERSECT Operator
- SQL - EXCEPT Operator
- SQL - Aliases
- SQL Joins
- SQL - Using Joins
- SQL - Inner Join
- SQL - Left Join
- SQL - Right Join
- SQL - Cross Join
- SQL - Full Join
- SQL - Self Join
- SQL - Delete Join
- SQL - Update Join
- SQL - Left Join vs Right Join
- SQL - Union vs Join
- SQL Keys
- SQL - Unique Key
- SQL - Primary Key
- SQL - Foreign Key
- SQL - Composite Key
- SQL - Alternate Key
- SQL Indexes
- SQL - Indexes
- SQL - Create Index
- SQL - Drop Index
- SQL - Show Indexes
- SQL - Unique Index
- SQL - Clustered Index
- SQL - Non-Clustered Index
- Advanced SQL
- SQL - Wildcards
- SQL - Comments
- SQL - Injection
- SQL - Hosting
- SQL - Min & Max
- SQL - Null Functions
- SQL - Check Constraint
- SQL - Default Constraint
- SQL - Stored Procedures
- SQL - NULL Values
- SQL - Transactions
- SQL - Sub Queries
- SQL - Handling Duplicates
- SQL - Using Sequences
- SQL - Auto Increment
- SQL - Date & Time
- SQL - Cursors
- SQL - Common Table Expression
- SQL - Group By vs Order By
- SQL - IN vs EXISTS
- SQL - Database Tuning
- SQL Function Reference
- SQL - Date Functions
- SQL - String Functions
- SQL - Aggregate Functions
- SQL - Numeric Functions
- SQL - Text & Image Functions
- SQL - Statistical Functions
- SQL - Logical Functions
- SQL - Cursor Functions
- SQL - JSON Functions
- SQL - Conversion Functions
- SQL - Datatype Functions
- SQL Useful Resources
- SQL - Questions and Answers
- SQL - Quick Guide
- SQL - Useful Functions
- SQL - Useful Resources
- SQL - Discussion
SQL - IS NOT NULL
A NULL value indicates a missing or unknown value. It appears to be blank and does not contain any data. It is very important to understand that a NULL value is different than a zero value or a field that contains spaces. For checking null values we can use two basic operators.
- IS NULL
- IS NOT NULL
The SQL IS NOT NULL Operator
The SQL IS NOT NULL operator is used to filter data by verifying whether a particular column has a not-null values. This operator can be used with SQL statements such as SELECT, UPDATE, and DELETE.
By using the IS NOT NULL operator, we can only fetch the records that contain valid data in a particular column.
Syntax
Following is the syntax of the SQL IS NOT NULL operator −
SELECT column_names FROM table_name WHERE column_name IS NOT NULL;
Example
Firstly, let us create a table named CUSTOMERS using the following query −
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR(20), AGE INT, ADDRESS CHAR(25), SALARY DECIMAL(18, 2), PRIMARY KEY(ID) );
Now, insert values into this table using the INSERT statement as follows −
INSERT INTO CUSTOMERS VALUES (1, 'Ramesh', 32, 'Ahmedabad', NULL ), (2, 'Khilan', 25, NULL, 1500.00 ), (3, 'Kaushik', NULL, 'Kota', 2000.00 ), (4, 'Chaitali', 25, 'Mumbai', NULL ), (5, 'Hardik', 27, 'Bhopal', 8500.00 ), (6, 'Komal', NULL, 'Hyderabad', 4500.00 ), (7, 'Muffy', 24, NULL, 10000.00 );
The table will be created as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | NULL |
2 | Khilan | 25 | NULL | 1500.00 |
3 | Kaushik | NULL | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | NULL |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | NULL | Hyderabad | 4500.00 |
7 | Muffy | 24 | NULL | 10000.00 |
Example
In the following query, we are going to return all the records from the CUSTOMERS table where the ADDRESS is not null −
SELECT * FROM CUSTOMERS WHERE ADDRESS IS NOT NULL;
Output
On executing the above query, it will generate the output as shown below −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | NULL |
3 | Kaushik | NULL | Kota | 2000.00 |
4 | Chaitali | 25 | Mumbai | NULL |
5 | Hardik | 27 | Bhopal | 8500.00 |
6 | Komal | NULL | Hyderabad | 4500.00 |
IS NOT NULL with COUNT() Function
We can use the IS NOT NULL operator along with the SQL COUNT() function to count only the non-null values in a specific column.
Syntax
Following is the syntax of IS NOT NULL operator with the COUNT() function −
SELECT COUNT(column_name) FROM table_name WHERE condition IS NOT NULL;
Example
The following query returns the count of all rows in the CUSTOMERS table where the SALARY column is not null −
SELECT COUNT(*) FROM CUSTOMERS WHERE SALARY IS NOT NULL;
Output
The output produced is as shown below −
COUNT(*) | 5 |
---|
IS NOT NULL with DELETE Statement
In SQL, we can delete all rows that do not contain NULL values in a specific column using the DELETE statement with IS NOT NULL operator.
Syntax
Following is the syntax of the IS NOT NULL operator with the DELETE statement in SQL −
DELETE FROM table_name WHERE columnname1, columnname2, ... IS NOT NULL;
Example
In the following query, we are deleting records which are not null in the SALARY column of the CUSTOMERS table −
DELETE FROM CUSTOMERS WHERE SALARY IS NOT NULL;
Output
We get the following result −
Query OK, 5 rows affected (0.02 sec)
Verification
Execute the SELECT query given below to check whether the table has been changed or not −
SELECT * FROM CUSTOMERS;
If we compile and run the program, the result is produced as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | NULL |
4 | Chaitali | 25 | Mumbai | NULL |
IS NOT NULL with UPDATE Statement
We can use the UPDATE statement with the IS NOT NULL operator in SQL to update records with not-null records in a particular column.
Syntax
Following is the syntax of the IS NOT NULL operator with the UPDATE statement in SQL −
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE columnname1, columnname2, ... IS NOT NULL;
Example
Truncate the CUSTOMERS table and reinsert all the 7 records into it again. The following query, increments all the values in the SALARY column of the with 5000, where the salary value is not null −
UPDATE CUSTOMERS SET SALARY = SALARY+5000 WHERE SALARY IS NOT NULL;
Output
When we execute the program above, the output is obtained as follows −
Query OK, 5 rows affected (0.01 sec) Rows matched: 5 Changed: 5 Warnings: 0
Verification
To check whether the table has been updated or not, execute the SELECT query below −
SELECT * FROM CUSTOMERS;
The table is displayed as follows −
ID | NAME | AGE | ADDRESS | SALARY |
---|---|---|---|---|
1 | Ramesh | 32 | Ahmedabad | NULL |
2 | Khilan | 25 | NULL | 6500.00 |
3 | Kaushik | NULL | Kota | 7000.00 |
4 | Chaitali | 25 | Mumbai | NULL |
5 | Hardik | 27 | Bhopal | 13500.00 |
6 | Komal | NULL | Hyderabad | 9500.00 |
7 | Muffy | 24 | NULL | 15000.00 |
To Continue Learning Please Login
Login with Google