Introduction to Oracle NULL Data Type

In Oracle database, NULL is a special data type that represents a missing or unknown value. Unlike other data types, NULL cannot be compared, calculated, or concatenated with values of other types.

Syntax

In Oracle, NULL exists as a keyword and can be used in columns or variables of any data type. The syntax is as follows:

NULL

Use Cases

NULL is commonly used in the following scenarios:

  • Representing missing data
  • Representing unknown data
  • Distinguishing between empty strings and NULL values

In a database, if a column has no value, its value can be set as NULL, indicating that the value of the column is missing or unknown. In some cases, it is important to distinguish between NULL values and empty strings. For example, when handling customer information, if a customer’s address is an empty string, it means the customer has no address, but if the address column is NULL, it means the customer’s address is unknown or missing.

Examples

Here are two examples of using NULL in Oracle to illustrate its usage:

Example 1

Create a table named students with columns id, name, and score. The id and name columns cannot be empty, but the score column can be null:

CREATE TABLE students (
  id NUMBER(10) NOT NULL,
  name VARCHAR2(50) NOT NULL,
  score NUMBER(10)
);

Insert some data:

INSERT INTO students (id, name, score)
VALUES (1, 'Alice', 80);

INSERT INTO students (id, name, score)
VALUES (2, 'Bob', NULL);

INSERT INTO students (id, name, score)
VALUES (3, NULL, 90);

Query the data:

SELECT * FROM students;

The result is as follows:

ID  NAME   SCORE
--- ------ ----
1   Alice  80
2   Bob    NULL
3   NULL   90

From the above result, we can see that Bob’s score is NULL, indicating that the score of this student is unknown or missing. The name column in the third row is also NULL, indicating that the name of this student is unknown or missing.

Example 2

In the second example, we can use NULL to represent missing data. Let’s assume we have an employee table that includes employees’ names and salary information. If an employee does not provide salary information, we can set it as NULL. Here’s an example:

CREATE TABLE employees (
  name VARCHAR2(50),
  salary NUMBER(10, 2)
);

INSERT INTO employees (name, salary) VALUES ('Alice', 10000);
INSERT INTO employees (name, salary) VALUES ('Bob', NULL);
INSERT INTO employees (name, salary) VALUES ('Charlie', 8000);

SELECT * FROM employees;

After executing the above SQL statements, we will get the following result:

NAME     SALARY
-------- -------
Alice    10000
Bob      NULL
Charlie  8000

In this example, Bob’s salary information is missing, so we set it as NULL. This approach helps us store incomplete data in the database and avoids errors caused by missing data.

Conclusion

In this article, we have introduced the NULL data type in Oracle database, which represents missing or unknown data values.