SELECT
Prev | Functional Data Manipulation Language (FDML) | Next

## Name

SELECT — Executes a query given the supplied constraints.Function

## Syntax

          select &rest identifiers &key all distinct from group-by having limit offset order-by set-operation where result-types field-names flatp refresh caching database => result

## Arguments and Values

`identifiers`

  A set of sql expressions each of which indicates a column to query.

`all`

  A Boolean.

`distinct`

  A Boolean.

`from`

  One or more SQL expression representing tables.

`group-by`

  An SQL expression.

`having`

  An SQL expression.

`limit`

  A non-negative integer.

`offset`

  A non-negative integer.

`order-by`

  An SQL expression.

`set-operation`

  An SQL expression.

`where`

  An SQL expression.

`database`

  A database object. This will default to the value of *default-database*.

`flatp`

  A Boolean whose default value is `NIL`.

`result-types`

  A field type specifier. The default is :auto.

  The purpose of this argument is cause CLSQL to import SQL numeric fields into numeric Lisp objects rather than strings. This reduces the cost of allocating a temporary string and the CLSQL users' inconvenience of converting number strings into number objects.

  A value of :auto causes CLSQL to automatically convert SQL fields into a numeric format where applicable. The default value of `NIL` causes all fields to be returned as strings regardless of the SQL type. Otherwise a list is expected which has a element for each field that specifies the conversion. Valid type identifiers are:

  :int Field is imported as a signed integer, from 8-bits to 64-bits depending upon the field type.
  :double Field is imported as a double-float number.
  t Field is imported as a string.

  If the list is shorter than the number of fields, the a value of t is assumed for the field. If the list is longer than the number of fields, the extra elements are ignored.

`field-names`

  A boolean with a default value of `T`. When `T`, this function returns a second value of a list of field names. When `NIL`, this function only returns one value - the list of rows.

`refresh`

  This value is only considered when CLOS objects are being selected. A boolean with a default value of `NIL`. When the value of the `caching` keyword is `T`, a second equivalent `select` call will return the same view class instance objects. When `refresh` is `T`, then slots of the existing instances are updated as necessary. In such cases, you may wish to override the hook `instance-refresh`.

`caching`

  This value is only considered when CLOS objects are being selected. A boolean with a default value of `*default-caching*`. CLSQL caches objects in accordance with the CommonSQL interface: a second equivalent `select` call will return the same view class instance objects.

`result`

  A list representing the result set obtained. For each tuple in the result set, there is an element in this list, which is itself a list of all the attribute values in the tuple.

## Description

Executes a query on `database`, which has a default value of *default-database*, specified by the SQL expressions supplied using the remaining arguments in `args`. The `select` function can be used to generate queries in both functional and object oriented contexts.

In the functional case, the required arguments specify the columns selected by the query and may be symbolic SQL expressions or strings representing attribute identifiers. Type modified identifiers indicate that the values selected from the specified column are converted to the specified lisp type. The keyword arguments `all`, `distinct`, `from`, `group-by`, `having`, `limit`, `offset`, `order-by`, `set-operation` and `where` are used to specify, using the symbolic SQL syntax, the corresponding components of the SQL query generated by the call to `select`.

`result-types` is a list of symbols which specifies the lisp type for each field returned by the query. If `result-types` is `NIL` all results are returned as strings whereas the default value of :auto means that the lisp types are automatically computed for each field. `field-names` is `T` by default which means that the second value returned is a list of strings representing the columns selected by the query. If `field-names` is `NIL`, the list of column names is not returned as a second value.

In the object oriented case, the required arguments to `select` are symbols denoting View Classes which specify the database tables to query. In this case, `select` returns a list of View Class instances whose slots are set from the attribute values of the records in the specified table. Slot-value is a legal operator which can be employed as part of the symbolic SQL syntax used in the `where` keyword argument to `select`. `refresh` is `NIL` by default which means that the View Class instances returned are retrieved from a cache if an equivalent call to `select` has previously been issued. If `refresh` is true, the View Class instances returned are updated as necessary from the database and the generic function `instance-refreshed` is called to perform any necessary operations on the updated instances.

In both object oriented and functional contexts, `flatp` has a default value of `NIL` which means that the results are returned as a list of lists. If `flatp` is t and only one result is returned for each record selected in the query, the results are returned as elements of a list.

## Examples

    (select [first-name] :from [employee] :flatp t :distinct t
                         :field-names nil
                         :result-types nil
                         :order-by [first-name])
    => ("Boris" "Josef" "Konstantin" "Leon" "Leonid" "Mikhail" "Nikita" "Vladimir"
        "Yuri")

    (select [first-name] [count [*]] :from [employee]
    	                         :result-types nil
    			         :group-by [first-name]
    			         :order-by [first-name]
    			         :field-names nil)
    => (("Boris" "1") ("Josef" "1") ("Konstantin" "1") ("Leon" "1") ("Leonid" "1")
        ("Mikhail" "1") ("Nikita" "1") ("Vladimir" "2") ("Yuri" "1"))

    (select [last-name] :from [employee]
                        :where [like [email] "%org"]
    		    :order-by [last-name]
    		    :field-names nil
    		    :result-types nil
    		    :flatp t)
    => ("Andropov" "Brezhnev" "Chernenko" "Gorbachev" "Kruschev" "Lenin" "Putin"
        "Stalin" "Trotsky" "Yeltsin")

    (select [max [emplid]] :from [employee]
                           :flatp t
    	               :field-names nil
                           :result-types :auto)
    => (10)

    (select [avg [height]] :from [employee] :flatp t :field-names nil)
    => (1.58999584d0)

    (select [emplid] [last-name] :from [employee] :where [= [emplid] 1])
    => ((1 "Lenin")),
       ("emplid" "last_name")

    (select [emplid :string] :from [employee]
                             :where [= 1 [emplid]]
                             :field-names nil
                             :flatp t)
    => ("1")

    (select [emplid] :from [employee] :order-by [emplid]
                     :where [not [between [* [emplid] 10] [* 5 10] [* 10 10]]]
                     :field-names nil
                     :flatp t)
    => (1 2 3 4)

    (select [emplid] :from [employee]
            :where [in [emplid] '(1 2 3 4)]
            :flatp t
            :order-by [emplid]
            :field-names nil)
    => (1 2 3 4)

    (select [emplid] :from [employee]
            :order-by [emplid]
            :limit 5
            :offset 3
            :field-names nil
            :flatp t)
    => (4 5 6 7 8)

    (select [first-name] [last-name] :from [employee]
            :field-names nil
            :order-by '(([first-name] :asc) ([last-name] :desc)))
    => (("Boris" "Yeltsin") ("Josef" "Stalin") ("Konstantin" "Chernenko")
        ("Leon" "Trotsky") ("Leonid" "Brezhnev") ("Mikhail" "Gorbachev")
        ("Nikita" "Kruschev") ("Vladimir" "Putin") ("Vladimir" "Lenin")
        ("Yuri" "Andropov"))

    (select [last-name] :from [employee]
                     :set-operation [union [select [first-name] :from [employee]
                                                   :order-by [last-name]]]
                     :flatp t
                     :result-types nil
                     :field-names nil)
    => ("Andropov" "Boris" "Brezhnev" "Chernenko" "Gorbachev" "Josef" "Konstantin"
        "Kruschev" "Lenin" "Leon" "Leonid" "Mikhail" "Nikita" "Putin" "Stalin"
        "Trotsky" "Vladimir" "Yeltsin" "Yuri")

## Side Effects

Whatever effects the execution of the SQL query has on the underlying database, if any.

## Affected by

None.

## Exceptional Situations

If the execution of the SQL query leads to any errors, an error of type sql-database-error is signalled.

## See Also

`query`
`print-query`
`do-query`
`map-query`
`loop`
`instance-refreshed`

## Notes

The `select` function is actually implemented in CLSQL with a single &rest parameter (which is subsequently destructured) rather than the keyword parameters presented here for the purposes of exposition. This means that incorrect or missing keywords or values may not trigger errors in the way that they would if `select` had been defined using keyword arguments.

The `field-names` and `result-types` keyword arguments are a CLSQL extension.

`select` is common across the functional and object-oriented data manipulation languages.

Prev | Up | Next
PRINT-QUERY | Home | DO-QUERY