14.5.1 The "[...]" Syntax

14.5.1.2 SQL strings representing symbolic expressions

There are some SQL operators which may take a single argument (for exampleany,all,not,union,intersection,minus,group-by, andhaving). 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)">

Theselect 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 toquery 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 aselect:

[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]

when macroexpanded reads as

(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 arenull,exists,*,+,/,-,like,and,or,in,||,=,<,>=,<=,count,max,min,avg,sum,distinct,slot-value, andbetween. The general syntax is:[<operator> <operand> ...], for instance:

(select [count [*]} :from [emp])