postmodern-j (Joins)

[Special Characters][A] [B] [C] [D] [E] [F] [G] [H] [I] [J] [K] [L] [M] [N] [O] [P] [Q] [R] [S] [T] [U] [V] [W] [X] [Y] [Z]

Joins

Cross Join

From the postgresql documentation: "For every possible combination of rows from T1 and T2 (i.e., a Cartesian product), the joined table will contain a row consisting of all columns in T1 followed by all columns in T2. If the tables have N and M rows respectively, the joined table will have N * M rows."

(query (:select '* from 'employee :cross-join 'compensation))

Inner Join

An inner join looks at two tables and only joins the rows from the two tables that match the specified conditions. An inner join is the default and need not be specified.

A sample of standard sql on an inner join could look like this:

(SELECT foo, bar, baz FROM (SELECT foo FROM x WHERE x) AS tmp1 

INNER JOIN (SELECT bar FROM x WHERE x) AS tmp2 ON (tmp1.id = tmp2.id) 

INNER JOIN (SELECT baz FROM x WHERE x) AS tmp3 ON (tmp2.id = tmp3.id))

The same query could be expressed in s-sql as:

(query (:select 'foo 'bar 'baz
                    :from (:as
                           (:select 'foo
                                    :from 'x
                                    :where 'x) 'tmp1)
                    :inner-join (:as
                                 (:select 'bar
                                          :from 'x
                                          :where 'x) 'tmp2)
                    :on (:=
                         'tmp1.id 'tmp2.id)
                    :inner-join (:as
                                 (:select 'baz
                                          :from 'x
                                          :where 'x)
                                 'tmp3)
                    :on (:='tmp2.id 'tmp3.id)))

Outer Join

An outer join not only generates an inner join, it also joins the rows from one table that matches the conditions and adds null values for the joined columns from the second table (which obviously did not match the condition.) Under Postgresql, a "left join", "right join" or "full join" all imply an outer join.

A left join (or left outer join) looks at two tables, keeps the matched rows from both and the unmatched rows from the left table and drops the unmatched rows from the right table. A right outer join keeps the matched rows, the unmatched rows from the right table and drops the unmatched rows from the left table. A full outer join includes the rows that match from each table individually, with null values for the missing matching columns.

Left Join

Example: Here we assume two tables. A countries table and a many-to-many linking table named countries-topics. (There is an implicit third table named topics.) We are looking for records from the countries table which do not have a match in the countries-topics table. In other words, where do we have a note, but not matched it to a topic?

(defun notes-with-no-topics () (query (:order-by (:select 'countries.id 'countries.name :distinct :from 'countries :left-join 'countries-topics :on (:= 'countries.id 'countries-topics.country-id) :where (:is-null 'countries-topics.country-id)) 'countries.id)))
Comments