There are some SQL operators which may take a single argument (for example any
, all
, not
, union
, intersection
, minus
, group-by
, and having
). These are read as calls to the appropriate SQL operator. For example:
[any '(3 4)] --> #<SQL-VALUE-EXP "(ANY (3,4))">
This causes no conflict, however, as it is illegal to use these reserved words as identifiers in SQL. Similarly with two argument operators:
[> [baz] [beep]]
--> #<SQL-RELATIONAL-EXP "(BAZ > BEEP)">
The select
statement itself may be prepared for later query execution using the []
syntax. For example:
[select [person_id] [surname] :from [person]]
This form results in an SQL expression, which could be bound to a Lisp variable and later given to query
to execute. For example:
[select [foo] [bar *]
:from '([baz] [bar])
:where [or [= [foo] 3]
[> [baz.quux] 10]]]
-->
#<SQL-QUERY
"(SELECT FOO,BAR.* FROM BAZ,BAR
WHERE ((FOO = 3)
OR (BAZ.QUUX > 10)))">
Strings can be inserted in place of database identifiers within a select
:
[select [foo bar] [baz]
:from '([foo] [quux])
:where [or [> [baz] 3]
[like [foo bar] "SU%"]]]
-->
#<SQL-QUERY:
"(SELECT FOO.BAR,BAZ
FROM FOO,QUUX
WHERE ((BAZ > 3)
OR (FOO.BAR LIKE 'SU%')))">
Any non-constant included gets filled in at runtime, for example:
[> [foo] x]
(SQL-> #<SQL-IDENT "FOO"> X)
which constructs the actual SQL string at runtime.
Any arguments to an SQL operator that are Lisp constants are translated to the matching SQL construct at compile-time, for example:
"foo" --> "'foo'"
3 --> "3"
'("this" 5 "that") --> "('this', 5, 'that')"
'xyz --> "XYZ"
Other SQL operators which are supported are null
, exists
, *
, +
, /
, -
, like
, and
, or
, in
, ||
, =
, <
, >=
, <=
, count
, max
, min
, avg
, sum
, distinct
, slot-value
, and between
. The general syntax is: [<operator> <operand> ...]
, for instance:
(select [count [*]} :from [emp])