SQLite Forum

Are result values always the same?
> Are the result values for this operation always 0 or 1?

A value of NULL means "unknown" (as in one of the arguments to the equality test was NULL), 0 means "false" and a non-zero value means "true".

 > Does the SQL standard say so or is it implementation dependent?

I cannot answer what the SQL Standard says (if anything) about the result of comparison operations.  The actual value obtained for "true" would depend on the underlying hardware, the language in which the "SQL engine" were implemented, and perhaps a myriad of other factors.

However, in the case of SQLite3 the evaluation of a comparison operation returns either 0 for false, 1 for true, or null if either argument is null and the comparison operation is not "is" or "is not".

 > I mean, the result could be 0 and 99, right?

Generally unlikely but yes.  The value 99 is true because it is not 0.  It is more likely that the value would be -1 (all bits on) however.

If you wanted to "assure" that the result were always 1 or 0 then you could use  something like:

UPDATE pltable SET checkbox = (CASE WHEN pl_id == ? THEN 1 ELSE 0 END);

which will always result in checkbox being 1 or 0 and does not have any dependencies on any possible implementation detail -- that is, it is 100% absolutely unambiguous (explicit) and is not subject to "interpretation", and if you pass "NULL" as the parameter, all results will be 0.

That certainty, however, does have a cost.  The more explicit version requires that the machine execute more instructions to obtain those more explicit results.  So if you table had 1 billion rows this might be significant.  If your table only has ten rows, then likely not.

 > Also, should I define an index for this kind of query? If so, what should it be?

There is no point.  You are updating the value of every row in the table and computing the result separately for each row.  Indexes will not help for this particular operation.