SQL - BOOLEAN (BIT) Operator

A Boolean is a universal data type which stores true or false values. It is used when we define a variable in a column of the table.

For instance, a customer wants a pst of all the ‘red’ cars. So, we can find this using the BOOLEAN operator as given in the below table −

Here, ‘IS_RED’ is the BOOLEAN column that returns either TRUE or FALSE values based on the color of the cars.

Boolean in SQL

SQL does not have a Boolean data type (as a keyword). Instead, it provides BIT data type. A bit data type is an integer value that accepts the values 0, 1 and NULL.

The value 0 represents FALSE and 1 represents TRUE.

We can also store NULL values using the bit datatype.

The range of the bit data type is 1 to 64. This means that SQL BOOLEAN requires only a single bit to store values.

The databases pke PostgreSQL and PL/SQL provides the Boolean data type which is abbreviated as BOOL. Whereas the databases pke MySQL and oracle SQL does not have a Boolean data type. To represent Boolean values, they provide TINYINT and BIT data type respectively.

Syntax

Following is the basic syntax of SQL BIT data type −

CREATE TABLE table_name (
   column name BIT, column2 datatype, column 3 datatype …
);

Example

In the following query, we are creating a table with the name ‘CUSTOMERS’ with three columns namely, ‘ID’, ‘NAME’ and ‘AVAILABLITY’.

The ‘AVAILABILITY’ column represents whether the customer is available or not. It stores the bit value 0 (FALSE) if the customer is not available and 1 (TRUE) if the customer is available.

CREATE TABLE CUSTOMERS(
   ID INT NOT NULL,
   NAME VARCHAR(150),
   AVAILABILITY BIT
); 

Output

Following is the result obtained by executing the above query −

Commands completed successfully.

Verification

We can verify whether the changes are reflected in a table by retrieving its contents using the SELECT statement. Following is the query to display the records in the Customers table −

select * from customers;

The table is displayed as follows −

+-----+------+--------------+
| ID  | NAME | AVAILABILITY |
+-----+------+--------------+

Inserting data with SQL BIT data type

Once you have created a column of table with the datatype Boolean, you need to pass 0, 1 or, NULL as values to it. If the value of a column with the datatype BIT is other than these 3 values an error will be generated.

Example

In the following query we are trying to insert data in the CUSTOMERS table created above −

INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (1,  Ramesh , 0);
INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (2,  Khilan , 1);
INSERT INTO CUSTOMERS(ID, NAME, AVAILABILITY) VALUES (4,  Kaushik , NULL);

Output

Following is the result obtained −

(1 row affected)
(1 row affected)
(1 row affected)

Verification

The SELECT statement is used to print the contents of the specified table. To verify if the changes are reflected in the CUSTOMERS table, we can use this statement as shown below −

SELECT ID, NAME, AVAILABILITY
FROM CUSTOMERS;

The table is displayed as follows −

+-----+----------+--------------------+
| ID  | NAME     | AVAILABILITY       |
+-----+----------+--------------------+
|   1 | Ramesh   | 0                  |
|   2 | Khilan   | 1                  |
|   4 | kaushik  | NULL               |
+-----+----------+--------------------+

Replacing BIT 0,1 with TRUE and FALSE

As we saw above, the BIT datatype shows 0 and 1 values instead of TRUE and FALSE. In SQL, we can convert a BIT data type to TRUE and FALSE using the CASE statement.

The SQL CASE statement is a conditional statement that helps us to make decisions based on certain conditions. It evaluates the set of conditions and returns a result when the respective condition is satisfied.

Syntax

Following is the basic syntax of CASE statement −

CASE
   WHEN condition1 THEN value1
   WHEN condition2 THEN value2
   ...
   ELSE default_value
END

Example

To understand it better let us consider the CARS table which contains the details of cars including their manufacturer, type, transmission and color as shown below −

CREATE TABLE CARS (
   MANUFACTURER VARCHAR(20) NOT NULL,
   TYPE VARCHAR(20) NOT NULL,
   TRANSMISSION VARCHAR(20) NOT NULL,
   COLOR VARCHAR(20),
   IS_RED BIT
);

Now, insert values into this table using the INSERT statement as follows −

INSERT INTO CARS VALUES ( Toyota ,  SUV ,  Automatic ,  Red , 1);
INSERT INTO CARS VALUES ( Honda ,  Hatchback ,  Manual ,  Grey , 0);
INSERT INTO CARS VALUES ( Mercedes ,  Sedan ,  Automatic ,  Red , 1);
INSERT INTO CARS VALUES ( Tata ,  Truck ,  Manual ,  Blue , 0);
INSERT INTO CARS VALUES ( Ford ,  Minivan ,  Manual ,  Red , 1);

The table will be created as −

+--------------+-----------+--------------+-------+--------+
| MANUFACTURER | TYPE      | TRANSMISSION | COLOR | IS_RED |
+--------------+-----------+--------------+-------+--------+
| Toyota       | SUV       | Automatic    | Red   | 1      |
| Honda        | Hatchback | Manual       | Grey  | 0      |
| Mercedes     | Sedan     | Automatic    | Red   | 1      |
| Tata         | Truck     | Manual       | Blue  | 0      |
| Ford         | Minivan   | Manual       | Red   | 1      |
+--------------+-----------+--------------+-------+--------+

Now, let us try to display all the cars from the CARS table whose color is red represented by TRUE; otherwise FALSE (BOOLEAN values)−

SELECT *,
   CASE IS_RED
      WHEN 1 THEN  TRUE 
      WHEN 0 THEN  FALSE 
   END AS IS_RED_BOOLEAN
FROM CARS;

Output

Following is the result produced −

+--------------+-----------+--------------+-------+--------+----------------+
| MANUFACTURER | TYPE      | TRANSMISSION | COLOR | IS_RED | IS_RED_BOOLEAN |
+--------------+-----------+--------------+-------+--------+----------------+
| Toyota       | SUV       | Automatic    | Red   | 1      | TRUE           |
| Honda        | Hatchback | Manual       | Grey  | 0      | FALSE          |
| Mercedes     | Sedan     | Automatic    | Red   | 1      | TRUE           |
| Tata         | Truck     | Manual       | Blue  | 0      | FALSE          |
| Ford         | Minivan   | Manual       | Red   | 1      | TRUE           |
+--------------+-----------+--------------+-------+--------+----------------+ 

BIT with stored procedures

We can also use the BIT datatype within a stored procedure in SQL Server. Stored procedures are a set of SQL statements that can be executed multiple times.

A stored procedure uses a BIT parameter to determine whether to perform a certain action or not. Before using a bit type value (or, any other datatype) in a procedure we need to declare variable of this type first and then, use it as a parameter.

Syntax

Following is the basic syntax of stored procedure that takes a BIT parameter −

CREATE PROCEDURE my_Procedure
   @myBit BIT
AS
BEGIN
   IF @myBit = 1
   BEGIN
      -- do something if @myBit is true
   END
   ELSE
   BEGIN
      -- do something if @myBit is false
   END
END

Here, the stored procedure takes a parameter called @myBit of type BIT. It then verifies the value of @myBit using an IF statement and performs different actions depending on whether it s true or false.

Example

In the following query, we are trying to create a stored procedure named REDFlag that fetches the details of CARS table based on the value of the @REDFlag variable.

create procedure REDFlag
(
   @REDFlag bit
)
as
select * from CARS
where IS_RED = @REDFlag

Output

We get the following output while executing the above query −

Commands completed successfully.

Verification

Once the stored procedure is created successfully, we need to execute it using the Exec clause. Since it expects an input value of the type BIT, we are passing 1 (TRUE for color = RED).

exec REDFlag 1

Output

We get the following output after executing the above query. It shows the pst of cars with red color −

+--------------+-----------+--------------+-------+--------+
| MANUFACTURER | TYPE      | TRANSMISSION | COLOR | IS_RED |
+--------------+-----------+--------------+-------+--------+
| Toyota       | SUV       | Automatic    | Red   | 1      |
| Mercedes     | Sedan     | Automatic    | Red   | 1      |
| Ford         | Minivan   | Manual       | Red   | 1      |
+--------------+-----------+--------------+-------+--------+

Verification

If we need to get the pst of cars with color other than red we just need to call the above created procedure by passing 0 −

exec REDFlag 0

Output

The following result is produced −

+--------------+-----------+--------------+-------+--------+
| MANUFACTURER | TYPE      | TRANSMISSION | COLOR | IS_RED |
+--------------+-----------+--------------+-------+--------+
| Honda        | Hatchback | Manual       | Grey  | 0      |
| Tata         | Truck     | Manual       | Blue  | 0      |
+--------------+-----------+--------------+-------+--------+