What Is Boolean Data Type And What Are Their Uses With Example

Summary: In this tutorial, we will learn what is the Boolean data type and its uses. In the previous article, I have already discussed the data types, if you are not aware then first of all learn the data types.

Introduction of SQL Boolean Data Type

Booleans seem in SQL once a condition is required, such as WHERE clause in form of predicate which is produced by using SQL Server operators such as In operator, comparison operators, IS (NOT) NULL, etc. However, aside from TRUE and FALSE, these operators can even yield a 3rd state, referred to as UNKNOWN, when a comparison with NULL is made.
SQL BOOLEAN data type as an optional feature. once restricted by a NOT NULL constraint, a SQL Boolean behaves such as Booleans in other languages, which can store only TRUE & FALSE values. SQL defines three literals for the BOOLEAN type – TRUE, FALSE, and UNKNOWN. Furthermore, if it’s nullable, then default like all alternative SQL data types, then it can have the special null value also. Therefore, it also says that the NULL BOOLEAN & UNKNOWN “may be used interchangeably to mean exactly the same factor.

Boolean Data Types Representation

The BOOLEAN data type in SQL can store TRUE or FALSE data values as a single byte.
The given below, the following table shows internal and literal representations of the BOOLEAN data type.

Logical Value	Literal Representation	Internal Representation
TRUE	‘t’	\0
FALSE	‘f’	\1
NULL	NULL	Internal Use Only

In SQL, we can compare the two BOOLEAN values to test for equality or inequality. BOOLEAN values are not case-sensitive, Furthermore, you can also compare a BOOLEAN value to the Boolean literals ‘t’ and ‘f’. here, note that ‘t’ is equivalent to ‘T’ and ‘f’ to ‘F’.
We can use a BOOLEAN column for the purpose of storing what a Boolean expression returns. within the following example, the value of boolean_column is ‘t’ if column1 is less than column2, and ‘f’ if column1 is greater than or equal to column2, and NULL in the case of, if the value of either column1 or column2 is unknown:

UPDATE my_table SET boolean_column = lessthan(column1, column2)

Boolean Data Types Uses

BOOLEAN can be used in SQL Server as a data type when be needed to define a column in a table or a variable in a database procedure. Furthermore, its support for the BOOLEAN data type helps migrations from other database products.

Boolean columns accept as input the SQL literals FALSE(‘F’) and TRUE(‘T’). Furthermore, additionally, because of automatic coercion rules, the strings ‘FALSE’ & ‘TRUE’ and therefore the integers 0 and 1 are also acceptable to be used in a Boolean column or variable. Input is not case-sensitive.

The CREATE INDEX statement allows or permits an index to be created on BOOLEAN columns.
Therefore, The terminal Monitor output for a BOOLEAN column shows the literals FALSE & TRUE as unquoted strings.
CASE expressions in SQL can be used with the BOOLEAN columns or literals. For illustration:

CASE expr WHEN cond1 THEN expr2
and
CASE WHEN search_cond1 THEN expr1

CASE expr WHEN cond1 THEN expr2

and

CASE WHEN search_cond1 THEN expr1

Description:- It accepts FALSE or TRUE in condN or search_condN or part thereof, and exprN can include BOOLEAN columns or literals.

Examples

I) Using the BOOLEAN Data Type When Creating A Table or Procedure:

CREATE TABLE example (column1 BOOLEAN NOT NULL);
 
CREATE PROCEDURE example_proc (flag BOOLEAN NOT NULL) AS
DECLARE
   var1 BOOLEAN;
BEGIN
    ...
END;

CREATE TABLE example (column1 BOOLEAN NOT NULL);

CREATE PROCEDURE example_proc (flag BOOLEAN NOT NULL) AS

DECLARE

var1 BOOLEAN;

BEGIN

...

END;

II) Using The Literals FALSE And TRUE In An SQL Context

INSERT INTO example VALUES (FALSE);
UPDATE example SET column1 = TRUE;
SELECT * FROM example WHERE column1 IS TRUE;
 ...
var1 = TRUE;
WHILE var1 IS NOT FALSE
 ...

INSERT INTO example VALUES (FALSE);

UPDATE example SET column1 = TRUE;

SELECT * FROM example WHERE column1 IS TRUE;

...

var1 = TRUE;

WHILE var1 IS NOT FALSE

...

Boolean Value Operators

This is the most important term that when we use booleans in a program, then it’s most important to know the boolean operators. which are used for the purpose of conditions and conditional statements that control how the program will behave. Operator examples include AND (&&), OR (||), and NOT (~).

Boolean Operator Examples

Boolean Operator	Description
`>=`	Greater than or equal:- True, if a number is greater than or equal to another
`<=`	Less than or equal:- True, if a number is less than or equal to another.
`==`	Equivalent:- True, if two values are equivalent.
`!=`	Not Equivalent:- True if two values are not equivalent.
`&&`	True, if both values are true.
`\|\|`	This operator is used if either of the values is true.
`!`	This operator is used if the value is false.
`~`	It is used to reverses all of the bits in a variable. for example 0000000000000000 becomes 1111111111111111.

Question And Answer (FAQs)

1) What is a null value?

Answer: Null is the data type that is used to represent objects such as variables, records, etc. that do not currently exist, are unused/unfinished, or are being discarded so that resources will be freed. Furthermore, It can also be used when an expression evaluates to nothing & needs to be given a value. It returns false.

2) Can a Boolean Data Type in SQL have The value of null?

Answer: No, it can’t, however, you can use an undefined boolean value to represent a null value. Therefore, if checked against an operator, it returns false.

3) Which Programming Languages Support SQL Boolean Data Types?

Answer: Booleans are available in most programming languages. If you’re using C, C++, PHP, JavaScript, Java, Python, etc. Furthermore, you should have access to the boolean data type.
If null is allowed in your programming, then both true & false would be allowed as well.

4) What are Boolean Variables in Programming Language?

Answer: The Boolean data type in programming is used to store the values true and false. Furthermore, this data type may be used to store information that allows one of two states, on or off, to be stored.

5) What is a nullable data type?

Answer: A nullable data type is used for the purpose of representing values that may be set to the null (no value) state.

6) What is a Boolean data type in a database explanation in short?

Answer: You know very well that Boolean data types are used to store the values true and false in a database. Furthermore, Booleans data types are used to show if a file exists, or if an action has been performed such as a form submitted.

It is most commonly used in databases for the purpose of representing yes/no, on/off, or other related states. For example, if an account has been turned off then it’s status column may contain false. Therefore, if it’s currently on then true would be stored in the account status boolean.

Conclusion:

In this article, you have learned what is the Boolean data type, Boolean Data Types Representation, Boolean Data Types Uses, and Examples with FAQs. I hope you will enjoy it!