Null Values in DBMS

About Null values in DBMS

In this article, we will learn about null values in DBMS.
Null values are special values in DBMS that represent values which are unknown and are always different from zero value. These values are supported by SQL and deals with them.

 

Null values in dbms

Null Values in DBMS  

  • Special value that is supported by SQL is called as null which is used to represent values of attributes that are unknown or do not apply for that particular row
  • For example age of a particular student is not available in the age column of student table then it is represented as null but not as zero
  • It is important to know that null values is always different from zero value
  • A null value is used to represent the following different interpretations
    • Value unknown (value exists but is not known)
    • Value not available (exists but is purposely hidden)
    • Attribute not applicable (undefined for that row)
  • SQL provides special operators and functions to deal with data involving null values
Null Values in DBMS
Consider the sample table ‘emp’
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7698 BLAKE MANAGER 7839 01-MAY-81 2850 500 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
7369 SMITH CLERK 7902 17-DEC-80 800 500 20

IS NULL operator

All operations upon null values present in the table must be done using this ‘is null’ operator .we cannot compare null value using the assignment operator
Example
select * from emp
where comm is null
O/P
4 rows selected.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7839 KING PRESIDENT 17-NOV-81 5000 10
7566 JONES MANAGER 7839 02-APR-81 2975 20
7788 SCOTT ANALYST 7566 19-APR-87 3000 20
7902 FORD ANALYST 7566 03-DEC-81 3000 20
The details of those employees whose commission value is Null are displayed.

IS NOT NULL

select * from emp
where comm is not null;
O/P
3 rows selected.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7698 BLAKE MANAGER 7839 01-MAY-81 2850 500 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 500 10
7369 SMITH CLERK 7902 17-DEC-80 800 500 20
Details of all those employees whose Commission value is not null value are displayed.

NOT NULL Constraint 

  • Not all constraints prevents a column to contain null values 
  • Once not null is applied to a particular column, you cannot enter null values to that column and restricted to maintain  only some proper value other than null 
  • A not-null constraint cannot be applied at table level

Example 

CREATE TABLE STUDENT 
(
   ID   INT             NOT NULL,
   NAME VARCHAR (20)     NOT NULL,
   AGE  INT             NOT NULL,
   ADDRESS  CHAR (25) ,
   SALARY   DECIMAL (18, 2),       
   PRIMARY KEY (ID)
); 
  • In the above example, we have applied not null on three columns ID, name and age which means whenever a record is entered using insert statement all three columns should contain a value other than null
  • We have two other columns address and salary,  where not null is not applied which means that you can leave the row as empty or use null value while inserting the record into the table.

NVL() NULL Function

  • Using NVL function you can substitute a value in the place of NULL values.
  • The substituted value then temporarily replaces the NULL values in your calculations or expression. Remember that the substituted value only replaces the NULL value temporarily for the session and does not affect the value stored in the table. 
  • Here is the syntax of NVL function.
NVL (exp, replacement-exp)
  • As you can see NVL function takes two parameters exp and replacement exp. First parameter exp can be a column name of a table or an arithmetic expression and the second parameter replacement expression will be the value which you want to substitute when a NULL value is encountered. 
  • Always remember the data type of both the parameters must match otherwise the compiler will raise an error.

Example

SELECT NVL (comm, 500) FROM employees
 WHERE salary>1000;
  • On execution all the null values in the result set will get replaced by 500.
  • Similarly we can use NVL null function while performing arithmetic expression.
  • Again let’s take the same arithmetic expression which we used in the previous query where we added 100 to the values of commission column.
SELECT NVL(comm,100), NVL(comm,100)+100 FROM employees WHERE salary>1000;
 

Prime Course Trailer

Related Banners

Get PrepInsta Prime & get Access to all 200+ courses offered by PrepInsta in One Subscription

Get over 200+ course One Subscription

Courses like AI/ML, Cloud Computing, Ethical Hacking, C, C++, Java, Python, DSA (All Languages), Competitive Coding (All Languages), TCS, Infosys, Wipro, Amazon, DBMS, SQL and others

Checkout list of all the video courses in PrepInsta Prime Subscription

Checkout list of all the video courses in PrepInsta Prime Subscription