Introduction to Oracle BOOLEAN Data Type

The BOOLEAN data type in Oracle is a logical type used to represent one of two values: true or false. It is one of the data types introduced in Oracle 10g, known for its improved query efficiency and wider range of applications.

Syntax

The syntax for the BOOLEAN data type is as follows:

BOOLEAN

Use Cases

The BOOLEAN data type is primarily used for storing logical values, such as determining if a certain condition is true or false. In some scenarios, the BOOLEAN data type can also be used as a replacement for numeric types (such as 0 and 1), making SQL statements more concise and clear.

Examples

Here are two examples of using the BOOLEAN data type:

Example 1

Assuming there is a table named employees with two columns, employee_id and is_manager. The data type of the is_manager column is BOOLEAN, indicating whether an employee is a manager or not. Here are some example data:

employee_id	is_manager
100	true
101	false
102	false
103	true

You can use the following SQL statement to query information of all managers:

SELECT * FROM employees WHERE is_manager = true;

The result will be as follows:

employee_id	is_manager
100	true
103	true

Example 2

Assuming there is a table named orders with two columns, order_id and is_completed. The data type of the is_completed column is BOOLEAN, indicating whether an order is completed or not. Here are some example data:

order_id	is_completed
1001	true
1002	false
1003	false
1004	true

You can use the following SQL statement to query all incomplete orders:

SELECT * FROM orders WHERE is_completed = false;