Skip to content

boolean.adoc

Latest commit

 

History

History
105 lines (81 loc) · 2.89 KB

boolean.adoc

File metadata and controls

105 lines (81 loc) · 2.89 KB

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

Benefits

The BOOLEAN data type standardizes the storage of "Yes" and "No" values.

Further information