How to use BOOLEAN type in SELECT statement

BOOLEAN alternatives in other databases

In some databases, namely Oracle and MySQL, there's no direct support in SELECT for a BOOLEAN type. Instead, they use 1 and 0 to signify true and false respectively. So when performing queries in these environments, adjust accordingly.

Use boolean as integer:

SELECT CASE WHEN is_active THEN 1 ELSE 0 END AS active_flag FROM users;


This demonstrates functionality of is_active which will result in 1 for active users and 0 for those not so lucky!

Convert boolean to strings:

SELECT name, CASE WHEN is_admin THEN 'true' ELSE 'false' END AS admin_status FROM users;


This query makes it easily read, especially if outputs are for mere mortals (non-programmers!) viewing the output.

BOOLEAN handling like a pro

Using BOOLEAN in PL/SQL

In an environment like PL/SQL, you might have existing functions using the BOOLEAN type. To call these in SQL, you could create wrapper functions that transform 1/0 or 'true'/false' to BOOLEAN.

Here's a how-to guide:

CREATE FUNCTION wrap_boolean(p_int IN NUMBER) RETURN BOOLEAN AS
BEGIN
  RETURN sys.diutil.int_to_bool(p_int); --.nextInt()? Nah! nextBoolean? Maybe!!
END wrap_boolean;


Apply this function in SQL queries to use BOOLEAN return types as if they're in a PL/SQL function. Easy, isn't it?

Output-friendly transformation

Despite not directly using the BOOLEAN type within SQL, you can translate a BOOLEAN condition into something more human-readable with IF-THEN or CASE.

SELECT id, name, CASE WHEN expires_at > CURRENT_DATE THEN 'Active' ELSE 'Expired' END AS subscription_status FROM members;


Who needs a GUI when you have SQL, right?

Frequently encountered BOOLEAN glitches

Using "TRUE" keyword in Oracle SQL

Avoid using TRUE directly in an Oracle SQL SELECT statement. Because let's be "true", Oracle is a bit picky and it doesn't recognize this keyword, leading to an ORA-00904:invalid identifier error.

Attempting to insert raw BOOLEAN values

For databases where BOOLEAN data types don't fit, trying to shoe-horn in true or false will result in a mishap. Transform to equivalent integers or strings before the insert operation.

Using BOOLEAN while tools expect SQL-friendly data

In frameworks like Hibernate or Mybatis, where BOOLEAN values are used as 1 (aka true) or 0 (the false one), let's stick to their conventions and SQL queries will be running like a well-oiled machine!

Cross-database sagacity and app alignment

Bridging databases

The way BOOLEAN behaves changes across databases (like shifting goalposts!). If you're trying to write SQL to run on various systems like PostgreSQL, MySQL, and Oracle, know how they each treat BOOLEAN.

Here is the director's commentary on BOOLEAN stardom across different databases:

• PostgreSQL treats 't' and 'f' as true and false.
• MySQL, BOOLEAN is an alter-ego for TINYINT(1). Here 1 is true and 0, you guessed it, false.
• SQLite, any non-zero number is a true story.

Matching with the upper-layer

Make sure your SQL's BOOLEAN handling is on the same page as your app layer. If your app is expecting a true/false string but your SQL is the strong silent type, serving 1/0, consider some in-house adjustments.