EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Data Science Data Science Tutorials SQL Tutorial SQL NOT NULL
 

SQL NOT NULL

Updated March 8, 2023

SQL NOT NULL

 

 

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

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 –

SQL NOT NULL output 1

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 –

SQL NOT NULL output 2

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 –

SQL NOT NULL output 3

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 –

SQL NOT NULL output 4

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`

SQL NOT NULL output 5

Executing the select query for educba_writers table gives the following output –

output 6

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 –

output 7

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 –

output 8

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 –

output 9

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 –

output 10

Let us select the contents of the educba_learning table –

SELECT * FROM educba_learning

executing the above statement gives the following output

 output 11

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;

output 12

Let us select the contents of the educba_writers table –

SELECT * FROM educba_writers;

executing the above statement gives the following output

output 13

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.

  1. SQL NOT Operator
  2. MySQL DELETE Trigger
  3. SQL ORDER BY Alphabetical
  4. SQL Table Partitioning

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

🚀 Limited Time Offer! - ENROLL NOW