JoSQL User Manual - Where Clause

Where Clause

The general syntax for the WHERE clause is:

WHERE Expression [ AND | OR Expression ]*

An Expression is basically any statement that will evaluate to a result. In general Expressions generate a Boolean result, however it is also possible for none Boolean result expressions to be used in a WHERE clause.

For example:

WHERE name = 'Hello World'

It should be pretty obvious here that the = will cause a Boolean result of true to be generated if the value from the name accessor is equal to the value "Hello World".

JoSQL supports the following "standard" SQL operators:

=
<=
>=
!=
>
<
[ NOT ] IN [ LIKE ] [ ALL ]
[ NOT ] BETWEEN
IS [ NOT ] NULL
[ NOT ] LIKE
*
-
+
/
%
AND
OR

In addition, JoSQL also provides the ability to prefix LIKE, = and != with $ to indicate that a case-insensitive string comparison should be made (i.e. $LIKE, $= and $!=). It should be noted that the use of $ will force JoSQL to perform a string comparison, in practice this doesn't matter much but if you are mixing types beware!

It is also possible to use non-Boolean operators to return a Boolean result, for example in JoSQL:

WHERE 10000 * 1

Will evaulate to true. JoSQL (like C) assumes that a non-zero result is true, and zero is false. Similarly non-NULL values evaluate to true and NULL values evaluate to false, for example:

WHERE upper ('Hello World')

Will evaulate to true, but:

WHERE upper (NULL)

Will evaluate to false.

JoSQL uses lazy-evaluation when evaluating the WHERE clause (and the HAVING clause), and evaluates top-down, so it is generally better to put clauses that will limit the set of records "higher" up in the list. JoSQL supports grouping of clauses via brackets and the use of AND and OR to provide grouping. For example:

WHERE 1000 = 1000
AND   ('a' != 'b'
      OR :x $= 'Hello World')

There is also special handling of NULL. In JoSQL the following are equivalent:

WHERE :x = NULL
AND   :x = 'null'
AND   :x IS NULL

(This is because I'm used to doing = null in Java and got sick of having to write IS NULL in SQL!)

Wherever possible JoSQL makes use of the Comparable interface, that is if 2 objects that need to be compared are Comparable then the Comparable.compareTo(Object,Object) method will be used. If they objects don't implement the Comparable interface then they are converted to strings and then compared. It should be noted that numbers are handled slightly differently because (for some reason!) sub-classes of Number are not comparable. In this way you can then perform custom comparisons on your objects.

Save Values, Bind Variables (Normal and Special), Functions, Number constants, Accessors and the constants TRUE, FALSE and NULL can all be used (in conjunction with the operators listed above) in the formation of an Expression.

For the Save Values, it should be noted that only those that have been saved due to the EXECUTE ON ALL clause are available for use.

Special Cases

There are also a number of special cases that JoSQL handles that are worth a mention. The first is that dividing a number by 0 will cause 0 to be returned.

If you use the + operator with 2 strings then a concatenated string is the result, for example:

WHERE 'x' + y'

Will return true. However,

WHERE 'XY' = 'x' + 'y'

Will return false.

Finally, it is possible to use Maps, Lists and Collections (yes I know that a List is also a Collection but they are distinguished here due to the potential differences in iteration methods) with the IN operator, for example:

WHERE 'x' IN (:myListOfStrings)

Then if x is one of the values in the list of strings provided by the bind variable: myListOfStrings then the WHERE clause will evaluate to true. A Map can also be provided and then the value will be checked for in the keys of the Map. It is also possible to mix and match list and maps.