16 The SQL Package

select

Function

Summary

Selects data from a database given a number of specified constraints.

Package

sql

Signature

select &rest selections &key all flatp set-operation distinct from where group-by having order-by database => result-list

Arguments

selections: A set of database identifiers or strings.
all: A boolean.
flatp: A boolean.
set-operation: An SQL operation.
distinct: A boolean.
from: An SQL table.
where: An SQL condition.
group-by: An SQL condition.
having: An SQL condition.
order-by: An SQL condition.
database: A database.

Values

result-list: A list of selections.

Description

The functionselect selects data from database, which has a default value of*default-database*, given the constraints specified by the rest of the arguments. It returns a list of objects as specified by selections. By default, the objects will each be represented as lists of attribute values. The argument selections consists either of database-identifiers or literal strings.

The flatp argument, which has a default value ofnil, specifies if full bracketed results should be returned for each matched entry. If flatp isnil, the results are returned as a list of lists. If flatp ist, the results are returned as elements of a list, only if there is only one result per row. See the examples section for an example of the use of flatp.

The arguments all, set-operation, distinct, from, where, group-by, having and order-by have the same function as the equivalent SQL expression.

Theselect function is common across both the functional and object-oriented SQL interfaces. If selections refers to view classes then the select operation becomes object-oriented.This means thatselect returns a list of view-class instances, andslot-valuebecomes a valid SQL operator for use within the where clause.

SQL expressions used in theselect function are specified using the square bracket syntax, once this syntax has been enabled using enable-sql-reader-syntax.

Examples

The following is a potential query and result:

(select [person_id] [surname] :from [person])

=> ((111 "Brown") (112 "Jones") (113 "Smith"))

: In the next example, the flatp argument is set tot, and the result is a simple list of surname values:

(select [surname] :from [person] :flatp t)

=> ("Brown" "Jones" "Smith")

See also
: print-query