What is the best way to convert an int or null to boolean value in an SQL query?

Translating boolean in SQL context

Trees aren't boolean, but SQL has something close. Since SQL lacks a direct boolean data type (at least in databases like MySQL), representing boolean values requires some cunning. Enter stage right: conditional constructs like CASE WHEN.

NULLs: The falsifiers

For converting INT or NULL to a boolean type, a common approach is to consider non-null values as TRUE and NULL as FALSE. A simple SQL expression will depict this clearly:

SELECT (your_column IS NOT NULL) as boolean_value FROM your_table;


Here, SQL considers (your_column IS NOT NULL) like a true/false question. If your_column is not null, you get 1 (TRUE), otherwise it's 0 (FALSE).

When CASE WHEN becomes your best friend

The IS NOT NULL evaluation fulfills most scenarios, but for the sake of complexity, let's add a couple of layers:

SELECT 
  CASE 
    WHEN your_column IS NULL THEN FALSE 
    WHEN your_column > 0 THEN TRUE 
    ELSE FALSE 
  END AS boolean_value 
FROM your_table;


This code spits out FALSE for NULL and zero values, and TRUE for positive numbers.

Life at zero and above

Presume 0 should be treated as TRUE. Slight tweak to the CASE statement is all it takes:

SELECT 
  CASE 
    WHEN your_column IS NOT NULL AND your_column >= 0 THEN TRUE
    ELSE FALSE 
  END AS boolean_value 
FROM your_table;


Just for fun: A chase of performance

Consider chase as the synonym of CASE. The efficiency of your chase varies with the complexity of the chase sequence. If you're dealing with large datasets, a plain IS NOT NULL check will finish the chase faster than a CASE scenario with a couple of twists and turns.

The Toolshed: Tips and Tricks

COALESCE: Setting default for the unset

In instances where you'd like to set a default TRUE or FALSE ahead of the boolean conversion, the COALESCE function is your go-to tool:

SELECT COALESCE(your_column > 0, FALSE) AS boolean_value FROM your_table;


Logic Operators: Double Duty

Databases that support boolean types, allow you to use:

SELECT your_column IS NOT FALSE AS boolean_value FROM your_table;


Resulting in NULL and FALSE being treated the same, and returning FALSE in both cases.

Comparing query results

Need to compare the boolean result within the same query? Just put the boolean expression directly on the 'wanted' posters (WHERE clause):

SELECT * FROM your_table WHERE (your_column IS NOT NULL);


Designing Pitfalls: A guide to safe SQL masonry

The Unknown Territories

Venturing into three-valued logic? Remember, NULL portrays an unknown status and can shake your conditional checks to the core.

The importance of type uniformity

Ensure your database treats 1 and 0 as synonyms for TRUE and FALSE before adventuring cross-database queries and being greeted with the unexpected.

Database Dialects

SQL dialect varies among its databases. The syntax and behavior might differ slightly, so when in doubt, x-mark your database's documentation.