Updated March 8, 2023
Introduction to SQL NOT NULL
SQL NOT NULL is the constraint or condition that can be applied on any expression, which can be a column name, variables, literals, or any other expression that involves functions and results to a certain value, determining whether the value of the expression is NULL or NOT. When the expression evaluates to a non-null value, then the NOT NULL constraint returns true else, it returns false. SQL not null constraint can be used along with all the SELECT DML statements of SQL, INSERT, UPDATE and DELETE. This article will learn about the syntax and usage of the NOT NULL constraint and study how this can be implemented in different DML statements of SQL using examples and demonstrations.
Syntax and usage
The general syntax of SQL NOT NULL constraint is as shown below –
expression_or_value IS NOT NULL;In the above expression, the expression_or_value is the table’s column name that you wish to check or any variable whose value is to be considered for NULL checking or any return value of the expression that involves using a function whose execution might return a NULL value. Most of the times, while using tables in SQL, if we have not specified the value of the column at the time of insertion of the record in the table, the NULL value gets stored for that column for that record as NULL is the default value for any column of tables in SQL until and unless an explicit DEFAULT value is mentioned for that column.
Hence, this results in the presence of NULL values in the records of the table, and while retrieving the data from the databases, we might need to choose or consider only those values for whom certain columns should not possess NULL value. In such scenarios IS NOT NULL constraint helps to return a boolean value that helps in considering whether that particular record should be added to the final result set or not. In the above syntax, when the expression or value results in a NULL value, then the IS NOT NULL clause will return a FALSE boolean value. In other cases, the expression evaluates to a NON-NULL, then it returns a TRUE boolean value.
Examples
We will study the working of the IS NOT NULL constraint by using it in different DML statements of SQL such as SELECT, INSERT, UPDATE and DELETE.
Let us create two tables named educba_learning and educba_writers using the following create table statements.
CREATE TABLE `educba_writers` (
`id` INT(11) NOT NULL,
`firstName` VARCHAR(10) NOT NULL,
`rate` DECIMAL(5,2) DEFAULT NULL,
`joining_date_time` DATETIME DEFAULT NULL
);The execution of the above query statement will give the following output –
CREATE TABLE `educba_learning` (
`topic_id` INT(11) NOT NULL IDENTITY,
`subject` VARCHAR(100) DEFAULT NULL,
`sessions` INT(5) DEFAULT '0',
`expert_name` VARCHAR(100) DEFAULT NULL,
`charges` DECIMAL(7,2) DEFAULT '0.00'
);The execution of the above query statement will give the following output –
Now, we will insert some records in both of the tables –
INSERT INTO `educba_learning` (`topic_id`, `subject`, `sessions`, `expert_name`, `charges`) VALUES
(1, 'SQL', 750, 'Payal', '3750.00'),
(2, 'MySQL', 700, 'Vyankatesh', '3500.00'),
(3, 'PostgreSQL', 600, 'Omprakash', '3000.00'),
(4, 'Hadoop', 980, 'Parineeta', '4900.00');The execution of the above query statement will give the following output –
INSERT INTO `educba_writers` (`id`, `firstName`, `rate`, `joining_date_time`) VALUES
(1, 'Payal', '750.00', '2020-05-28 16:02:34'),
(2, 'Vyankatesh', '700.00', NULL),
(3, 'Omprakash', '600.00', '2020-05-28 20:32:50'),
(4, 'Parineeta', '980.00', NULL);The execution of the above query statement will give the following output –
As you can see, we have inserted some records with a NULL value in the table educba_writers.
Let us retrieve the contents of both tables –
Executing the select query mentioned below for the educba_learning table gives the following output –
SELECT * FROM `educba_learning`
Executing the select query for educba_writers table gives the following output –
Now, we are ready to use the NOT NULL constraint on the above tables. We will see examples of each of the DML statements one by one.
Using NOT NULL constraint with the select query –
Now, we will retrieve only that many records of the educba_writers table that will not have a NULL value in the joining state time column. For this, we will use the IS NOT NULL constraint in our SELECT statement, and our query statement will be as follows –
SELECT * FROM `educba_writers` WHERE joining_date_time IS NOT NULL;The execution of the above query statement will give the following output containing only those records that have a NON-NULL value in the joining date time column –
Using NOT NULL constraint with update query –
While updating the records, there might be a scenario where you want to update only those records having values of certain columns set to some NON-NULL value. Suppose in our above table; we have to update the contents of the table educba_writers and set the column value of the rate column to 800 for the records that do not have a NULL value in the joining_date_time column. In this case, we can use the following query statement to update our records.
UPDATE educba_writers
SET rate = 800
WHERE joining_date_time IS NOT NULL;The execution of the above query statement will give the following output –
Let us check the contents of the table educba_writers after updating and see which records have been updated.
SELECT * FROM `educba_writers` ;The execution of the above query statement will give the following output having records Payal and Omprakash with updated rate –
Using NOT NULL constraint with insert query –
Let us insert records in table educba_learning from the educba_writers table for only those records having NON-NULL values in the joining date time column. We will use the following query for that –
INSERT INTO `educba_learning` (SELECT
NULL, 'Java', 800, firstName, 4500.00
FROM
educba_writers
WHERE joining_date_time IS NOT NULL) ;The execution of the above query statement will give the following output –
Let us select the contents of the educba_learning table –
SELECT * FROM educba_learningexecuting the above statement gives the following output
Using NOT NULL constraint with delete query –
Now, we will delete all the records from the table educba_writers having a NULL value in the joining date time column.
DELETE FROM educba_writers WHERE joining_date_time IS NOT NULL;
Let us select the contents of the educba_writers table –
SELECT * FROM educba_writers;executing the above statement gives the following output
Conclusion – SQL NOT NULL
We can apply the constraint on one or more columns to mention that the value of that column should not be NULL. This constraint can be used in either of the DML statements in SQL: SELECT, INSERT, UPDATE, and DELETE.
Recommended Articles
We hope that this EDUCBA information on “SQL NOT NULL” was beneficial to you. You can view EDUCBA’s recommended articles for more information.
