CODEWITHSUNDEEP
sql
Marcel Stör
Marcel Stör

Reputation: 23535

Logical AND on boolean columns in SQL

How can we logically AND-connect two boolean columns in SQL in the select clause?

Pseudo-SQL of what I have in mind:

select boolean_col_A AND boolean_col_B as joint_bool from foo where id = 42

Note that I am not limiting the number of records through a boolean predicate but rather create a "synthetic" i.e. surrogate column in the result set.

I feel there must be a simple way to achieve this but I'm not seeing it. It should work for Oracle, Postgres and H2.

Upvotes: 1

Views: 930

Answers (3)

Marcel Stör
Marcel Stör

Reputation: 23535

Thanks for all the feedback. My application uses Liquibase to abstract schema definitions. It maps the boolean type as follows:

  • H2: BOOLEAN
  • Postgres: BOOLEAN
  • Oracle: NUMBER(1)

Source: https://stackoverflow.com/a/28626825/131929

This is also the answer to @codeflush.dev's Q in the comments.

which datatype(s) do you use for each boolean in each RDBMS?

Given the list of effective RDBMS types above one way to go about this surrogate boolean column in the result set is to use BITAND. It is supported in the all database products relevant to this questions.

The more general CASE WHEN approach presented in the other answer is likely ok if you can't use BITAND.

Upvotes: 1

shohel
shohel

Reputation: 44

select 
(case when boolean_col_A =1 and boolean_col_B=1 then 'true'
else 'false'
end  ) joint_bool 
from foo where id = 42

try this.

Upvotes: 0

T. Peter
T. Peter

Reputation: 887

IMHO, use case might be a better option because Oracle is included, To the best of my knowledge Oracle can't have a boolean column so usually we just use a varchar(1) or char(1) with t/f

So instead of having boolean column, you can considerate just use string column with t/f across all database, then a simple case (like below) can work with all of them.

select case when colA = 't' and colB = 't' then 't' else 'f' end as joint_bool 
from [TableName]

Upvotes: 1

Related Questions