![SQL Logo](https://www.tutorialspoint.com/sql/images/sql-mini-logo.jpg)
- 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 Database
- 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
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
SQL - NULL Values
The term NULL in SQL is used to specify that a data value does not exist in the database. It is not the same as an empty string or a value of zero, and it signifies the absence of a value or the unknown value of a data field.
Some common reasons why a value may be NULL −
The value may not be provided during the data entry.
The value is not yet known.
It is important to understand that you cannot use comparison operators such as “=”, “<”, or “>” with NULL values. This is because the NULL values are unknown and could represent any value. Instead, you must use “IS NULL” or “IS NOT NULL” operators to check if a value is NULL.
Syntax
The basic syntax of NULL while creating a table.
SQL> CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Here, NOT NULL signifies that column should always accept an explicit value of the given data type. There are two columns where we did not use NOT NULL, which means these columns could be NULL.
A field with a NULL value is the one that has been left blank during the record creation.
Example
Let us create a table with the name CUSTOMERS in the SQL database using the CREATE statement as shown in the query below −
SQL> CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25) , SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Let us insert some values into the above created table using the following query −
SQL> INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(1, 'Ramesh', '32', 'Ahmedabad', 2000); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(2, 'Khilan', '25', 'Delhi', 1500); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(3, 'kaushik', '23', 'Kota', 2000); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(4, 'Chaitali', '25', 'Mumbai', 6500); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(5, 'Hardik','27', 'Bhopal', 8500); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(6, 'Komal', '22', 'MP', NULL); INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY) VALUES(7, 'Muffy', '24', 'Indore', NULL);
To verify whether the table CUSTOMERS is created or not, use the following query −
SQL> SELECT * FROM CUSTOMERS;
The table is successfully created in the database.
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | | | 7 | Muffy | 24 | Indore | | +----+----------+-----+-----------+----------+
IS NOT NULL Query
Now, let us try to retrieve the records present in the table that are not null using the IS NOT NULL operator −
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS NOT NULL;
Output
The above query would produce the following result −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | +----+----------+-----+-----------+----------+
IS NULL Query
Let us try to retrieve the records present in the table that are null using the IS NULL operator −
SQL> SELECT ID, NAME, AGE, ADDRESS, SALARY FROM CUSTOMERS WHERE SALARY IS NULL;
Output
The above query would produce the following result −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 6 | Komal | 22 | MP | NULL | | 7 | Muffy | 24 | Indore | NULL | +----+----------+-----+-----------+----------+
Updating NULL values in a table
You can update the NULL values present in a table using the UPDATE statement in SQL. To do so, you can use the IS NULL operator in your WHERE clause to select the rows with NULL values and then set the new value using the SET keyword.
Example
Assume the previously created table and let us try to update the NULL value(s) in the present in the table using the UPDATE statement as shown below −
SQL> UPDATE CUSTOMERS SET SALARY = 9000 WHERE SALARY IS NULL;
Output
When you execute the above query, the output is obtained as follows −
Commands completed successfully.
Verification
Let us try to verify whether the specified record(s) in the table is updated or not using the following query −
SQL> SELECT * FROM CUSTOMERS;
On executing the above query, the output is displayed as follows −
+----+----------+-----+-----------+----------+ | ID | NAME | AGE | ADDRESS | SALARY | +----+----------+-----+-----------+----------+ | 1 | Ramesh | 32 | Ahmedabad | 2000.00 | | 2 | Khilan | 25 | Delhi | 1500.00 | | 3 | kaushik | 23 | Kota | 2000.00 | | 4 | Chaitali | 25 | Mumbai | 6500.00 | | 5 | Hardik | 27 | Bhopal | 8500.00 | | 6 | Komal | 22 | MP | 9000.00 | | 7 | Muffy | 24 | Indore | 9000.00 | +----+----------+-----+-----------+----------+