6.2.1 Data sources, result types and the #:from clause

The #:from clause specifies the source of a query’s data. A source can be one of the following:

• an entity or entity alias;

• an alias of another query;

• a join over other sources;

The from clause also determines the default result type of the query. The three find procedures combine results in different ways:

• find-one returns (U result #f);

• find-all returns (listof result);

• g:find returns (gen-> result).

So, for example, if a query q has a result type of person, (find-all q) will have a return a (listof person).

The #:what clause

The default result type of a query can be overridden using a #:what clause.

6.2.1.1 Entities

The simplest #:from clause is a single entity. For example:

; (listof person)

; Find all people in the database:

(find-all (sql (select #:from person)))

Single-entity queries have results consisting of a single persistent struct. For example, the query above has a result type of person.

6.2.1.2 Joins

Joins on Wikipedia

Joins let you combine data from several entities into a single query. Snooze supports four types of join. The semantics of different join types are beyond the scope of these document. See Wikipedia’s page on joins for more information.

(inner source1 source2 on)

Creates an inner join or "equi-join" between two sources, source1 and source2. The on argument is a Boolean-valued expression specifying the join criteria.

For example, given appropriate persistent struct definitions:

; (listof (list person pet))

; Find all people who have pets plus their pets:

(find-all

(sql (select #:from (inner person

pet

(= person.pet-id pet.id)))))

is equivalent to:

SELECT person.*, pet.*

FROM person INNER JOIN pet ON person.petID = pet.id;

(left source1 source2 on)

Creates a left join between two sources, source1 and source2. The on argument is a Boolean-valued expression specifying the join criteria.

For example, given appropriate persistent struct definitions:

; (listof (list person (U pet #f)))

; Find all people, plus their pets wherever applicable:

(find-all

(sql (select #:from (left person

pet

(= person.pet-id pet.id)))))

is equivalent to:

SELECT person.*, pet.*

FROM person LEFT JOIN pet ON person.petID = pet.id;

(right source1 source2 on)

Creates a right join between two sources, source1 and source2. The on argument is a Boolean-valued expression specifying the join criteria.

For example, given appropriate persistent struct definitions:

; (listof (list (U pet #f) person))

; Find all people, plus their pets wherever applicable:

(find-all

(sql (select #:from (right pet

person

(= person.pet-id pet.id)))))

is equivalent to:

SELECT pet.*, person.*

FROM pet RIGHT JOIN person ON person.petID = pet.id;

(outer source1 source2)

Creates a cross join between two sources, source1 and source2. The on argument is a Boolean-valued expression specifying the join criteria.

For example:

; (listof (list (U pet #f) person))

; Find all people and all pets:

(find-all (sql (select #:from (outer person pet))))

is equivalent to:

SELECT person.*, pet.*

FROM person, pet;

The result type of a join is a flattened list of the result types of the join arguments. For example:

  (sql (select #:from (outer employee employer)))

has a result type of (list employee employer), while:

  (sql (select #:from (outer (outer employee employer) role)))

has a result type of (list employee employer role).

6.2.1.3 Subqueries

One query can use another query as a data source. This is useful when joining sources together, when some query operations should be applied to only one of the sources in the join.

For example, the following query uses the #:limit clause to select the first 10 employers in the database, and joins the employers to their employees. The #:limit cannot be specified in the outer query because there may be more than one employee per employer:

(find-all

(sql (select

#:from (left (select #:from  employer

#:limit 10

#:order ((asc employer.name)))

employee

(= employer.id

employee.employer-id)))))

Ignoring DBMS-specific rules regarding aliases, this query is roughly equivalent to the following SQL:

SELECT employer.*, employee.*

FROM (SELECT *

FROM employer

ORDER BY name ASC

LIMIT 10)

LEFT JOIN employee

ON employer.id = employee.employerID;