14.5.1.2 SQL strings representing symbolic expressions

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:

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:

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:

when macroexpanded reads as

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: