Boolean data type
The BOOLEAN data type enables the storage and processing of TRUE and FALSE values.
Boolean values can be used as table column values or inside SQL query expressions.
To declare a table column of type BOOLEAN, either use the BOOLEAN or BOOL keyword.
This feature is part of the ISO SQL:1999 standard.
-- Create a new table containing two boolean columns
CREATE TABLE email_addresses
(
user_id NUMBER NOT NULL,
email VARCHAR2(255) NOT NULL,
active BOOLEAN NOT NULL,
primary BOOL NOT NULL
);
-- Insert values into the table
INSERT INTO email_addresses
(user_id, active, primary, email)
VALUES ( 1, true, true, 'jon.doe@example.com'),
( 2, true, true, 'jane.smith@gmail.com'),
( 2, false, false, 'jsmith@gmail.com'),
( 3, true, true, 'max.well@example.com'),
( 3, true, false, 'mwell@gmail.com');
COMMIT;
-- Select all email addresses that are active
SELECT email FROM email_addresses
WHERE active;
-- Select all email addresses that are active but not primary
SELECT email FROM email_addresses
WHERE active AND NOT primary;Result
SQL> -- Create a new table containing two boolean columns
SQL> CREATE TABLE email_addresses
(
user_id NUMBER NOT NULL,
email VARCHAR2(255) NOT NULL,
active BOOLEAN NOT NULL,
primary BOOL NOT NULL
);
Table created.
SQL> -- Insert values into the table
SQL> INSERT INTO email_addresses
(user_id, active, primary, email)
VALUES ( 1, true, true, 'jon.doe@example.com'),
( 2, true, true, 'jane.smith@gmail.com'),
( 2, false, false, 'jsmith@gmail.com'),
( 3, true, true, 'max.well@example.com'),
( 3, true, false, 'mwell@gmail.com');
5 rows created.
SQL> COMMIT;
Commit complete.
SQL> -- Select all email addresses that are active
SQL> SELECT email FROM email_addresses
WHERE active;
EMAIL
--------------------------------------------------------------------------------
jon.doe@example.com
jane.smith@gmail.com
max.well@example.com
mwell@gmail.com
SQL> -- Select all email addresses that are active but not primary
SQL> SELECT email FROM email_addresses
WHERE active AND NOT primary;
EMAIL
--------------------------------------------------------------------------------
mwell@gmail.com