6.2.1.2Joins

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.

(innersource1source2on)

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(innerperson

pet

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

is equivalent to:

SELECT person.*, pet.*

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

(leftsource1source2on)

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(leftperson

pet

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

is equivalent to:

SELECT person.*, pet.*

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

(rightsource1source2on)

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(rightpet

person

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

is equivalent to:

SELECT pet.*, person.*

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

(outersource1source2)

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(outerpersonpet))))

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(outeremployeeemployer)))

has a result type of (listemployeeemployer), while:

(sql(select#:from(outer(outeremployeeemployer)role)))

has a result type of (listemployeeemployerrole).

6.2.1.3Subqueries

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#:fromemployer

#:limit10

#:order((ascemployer.name)))

employee

(=employer.id

employee.employer-id)))))

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