SQL Operators Supported

This section lists the operators that HoneySQL supports out of the box. There is no operator precedence assumed because SQL expressions are represented in prefix form, just like Clojure expressions.

Operators can be specified as keywords or symbols. Use - in the operator where the formatted SQL would have a space (e.g., :not-like is formatted as NOT LIKE).

and, or

Boolean operators. May take any number of expressions as arguments. nil expressions are ignored which can make it easier to programmatically build conditional expressions (since an expression that should be omitted can simply evaluate to nil instead).

{...
 :where [:and [:= :type "match"]
              (when need-status [:in :status [1 5]])]
 ...}
;; if need-status is truthy:
;;=> ["...WHERE (type = ?) AND (status IN (?, ?))..." "match" 1 5]
;; or, if need-status is falsey:
;;=> ["...WHERE (type = ?)..." "match"]
{...
 :where [:or [:= :id 42] [:= :type "match"]]
 ...}
;;=> ["...WHERE (id = ?) OR (type = ?)..." 42 "match"]

in, not-in

Predicates for checking an expression is or is not a member of a specified set of values.

The two most common forms are:

• [:in :col [val1 val2 ...]] or [:not-in :col [val1 val2 ...]] where the valN can be arbitrary expressions,
• [:in :col {:select ...}] or [:not-in :col {:select ...}] where the SELECT specifies a single column.

:col could be an arbitrary SQL expression (but is most commonly just a column name).

The former produces an inline vector expression with the values resolved as regular SQL expressions (i.e., with literal values lifted out as parameters): col IN [?, ?, ...] or col NOT IN [?, ?, ...]

The latter produces a sub-select, as expected: col IN (SELECT ...) or col NOT IN (SELECT ...)

You can also specify the set of values via a named parameter:

• [:in :col :?values] or [:not-in :col :?values] where :params {:values [1 2 ...]} is provided to format in the options.

In this case, the named parameter is expanded directly when :in (or :not-in) is formatted to obtain the sequence of values (which must be sequential, not a Clojure set). That means you cannot use this approach and also specify :cache -- see cache in All the Options for more details.

Another supported form is checking whether a tuple is in a selected set of values that specifies a matching number of columns, producing (col1, col2) IN (SELECT ...), but you need to specify the columns (or expressions) using the :composite special syntax:

• [:in [:composite :col1 :col2] ...] or [:not-in [:composite :col1 :col2] ...]

This produces (col1, col2) IN ... or (col1, col2) NOT IN ...

Note: This is a change from HoneySQL 1.x which accepted a sequence of column names but required more work for arbitrary expressions.

= <>

Binary comparison operators. These expect exactly two arguments.

not= and != are accepted as aliases for <>.

If either of the arguments evaluates to nil, the resulting SQL condition will use IS NULL or IS NOT NULL as appropriate by default. This behavior can be controlled by the :transform-null-equals option (see options).

(format {:where [:= :id nil]})
;;=> ["WHERE id IS NULL"]  -- default behavior

(format {:where [:= :id nil]} {:transform-null-equals false})
;;=> ["WHERE id = NULL"]   -- with transform disabled

This transformation in HoneySQL may be surprising but dates back to at least 2013, and was originally intended to help avoid accidentally false conditions when a value (parameter) might be nil. As of 2.7.1364, the :transform-null-equals option allows you to disable this transformation for SQL standard compliance.

< > <= >=

Comparison operators. These expect exactly two arguments.

is, is-not

Predicates for NULL and Boolean values:

{...
 :where [:is :id nil]
 ...}
;;=> ["...WHERE col IS NULL..."]
{...
 :where [:is-not :id nil]
 ...}
;;=> ["...WHERE col IS NOT NULL..."]
{...
 :where [:is :col true]
 ...}
;;=> ["...WHERE col IS TRUE..."]
{...
 ;; unlike [:<> :col false], the following will include NULLs:
 :where [:is-not :col false]
 ...}
;;=> ["...WHERE col IS NOT FALSE..."]

Note: is and is-not with a nil argument are equivalent to using = and <> respectively, due to the mapping in = and <>..

xor, + - * / % | & ^

Mathematical and bitwise operators.

like, not like, ilike, not ilike, regexp

Pattern matching operators. regex is accepted as an alias for regexp.

similar-to and not-similar-to are also supported.

with ordinality

The ANSI SQL WITH ORDINALITY expression is supported as an infix operator:

{...
 [:with-ordinality [:jsonb_array_elements :j] [:arr :item :index]]
 ...}
;;=> ["...JSONB_ARRAY_ELEMENTS(j) WITH ORDINALITY ARR(item, index)..."]

||

String concatenation operator.