前面介紹 from 子句時已初步接觸過 SQL 的資料來源與一些 join 操作，本章專門來看關聯（relation）到底是什麼。PostgreSQL 文件在「The FROM Clause」一節提供了啟發：table reference 可以是資料表名稱（可帶 schema 前綴），也可以是衍生表——子查詢、JOIN 結構，或它們的複雜組合；FROM 子句列出多個 table reference 時會做 cross join（形成列的笛卡兒積）；FROM 清單的結果是一個中間虛擬資料表，可再經 WHERE、GROUP BY、HAVING 子句轉換，最終成為整個 table expression 的結果。

關聯#

關聯是一組具有共同屬性的資料——也就是一組同屬相同複合資料型別（composite data type）的元素。SQL 標準並未走到用數學意義上的「集合（set）」定義關聯（那將意味著不允許重複），所以更精確地說是袋（bag）：SQL 關聯允許重複列。

資料型別由 create type 或更常見的 create table 語句定義：

~# create table relation(id integer, f1 text, f2 date, f3 point);
CREATE TABLE

~# insert into relation
        values(1,
                'one',
                current_date,
                point(2.349014, 48.864716)
               );
INSERT 0 1

~# select relation from relation;
                 relation
═══════════════════════════════════════════
 (1,one,2017-07-04,"(2.349014,48.864716)")
(1 row)

建立名為 relation 的表時，PostgreSQL 在背後同時建立了同名型別，可供操作或引用——所以這裡的 select 回傳的是複合型別 relation 的 tuple。

SQL 是強型別程式語言：在查詢規劃期，結果集每個欄位的型別都必須已知。任何結果集都被定義為某個已知複合型別的關聯，其中每一列都共享該型別蘊含的共同屬性。

關聯可以事先以 create table／create type（以及 create view 等，之後再談）定義，也可以在合理時由查詢規劃器動態定義。當你在主查詢中使用子查詢——無論是 CTE 還是直接內嵌在 from 子句——你實際上就是在定義一個關聯資料型別；查詢執行期這個關聯被填入資料集，成為一個完整可用的關聯。

**關聯代數（relational algebra）**是對這些東西能做什麼的形式化——簡言之就是 join：

  • 兩個關聯 join 的結果仍是關聯，可以再參與其他 join。
  • from 子句的結果是一個關聯，查詢規劃器以它執行查詢的其餘部分：where 限縮資料集，其他子句依序處理，直到視窗函式與 select 投影計算完成，最終建構出結果集——也是一個關聯。

PostgreSQL 最佳化器會重新安排計算順序以求最高效率，而不是照你寫的順序執行——就像 gcc 施展魔法後你認不出組合語言裡的原意一樣；差別在於 PostgreSQL 的 explain plan 是可以看懂的，而且能對應回你寫的查詢文字。

SQL Join 類型#

Join 是關聯的基本操作，本質是從一對既有關聯建出新關聯。最基本的是 cross join（笛卡兒積），如布林真值表的例子，結果是所有項目的所有可能組合。

其他 join 依 join 條件把兩個關聯的資料關聯起來。條件通常基於等值運算子，但不限於此。例如統計每場比賽中「完賽在當前車手之後」的人數，就是非等值 join 條件的好例子：

    select results.positionorder as position,
           drivers.code,
           count(behind.*) as behind

     from results
              join drivers using(driverid)

          left join results behind
                 on results.raceid = behind.raceid
                and results.positionorder < behind.positionorder

    where results.raceid = 972
      and results.positionorder <= 3

group by results.positionorder, drivers.code
order by results.positionorder;
 position │ code │ behind
══════════╪══════╪════════
        1 │ BOT  │     19
        2 │ VET  │     18
        3 │ RAI  │     17
(3 rows)

這裡使用 positionorder 欄位，因為它也會給未完賽的車手一個名次，正合本查詢所需。

這個查詢在同一個 FROM 中使用了同一個關聯兩次，因此需要不同的別名。你可能想把別名取成 r1、r2——但就像寫程式不會這樣命名變數一樣，最好給查詢中的 SQL 物件有意義的名字（這裡用 behind）。

關聯代數包含集合式操作，SQL 提供的則是 inner join、outer join、cross join 與 lateral join。本章的範例查詢已全數登場，快速總結：

  • Inner join：只保留兩個關聯都滿足 join 條件的列。
  • Outer join：無條件保留參考關聯的資料集，並在 join 條件成立時以另一個關聯的資料加以豐富。要全數保留的那個關聯寫在 join 名稱指示的一側——left join 在左、right join 在右。條件不成立時，保留的已知資料必須配上「不存在的資料」填進結果關聯——這正是 null 大顯身手之處，也是 null 屬於每一個 SQL 資料型別（包括布林）的原因。
  • Full outer join：outer join 的特例，無論是否滿足 join 條件，兩邊資料集的所有列都保留。
  • Lateral join：允許把 join 條件下推進右側的關聯，因為能在 lateral 子查詢裡使用 limit，帶來 top-N 查詢等新語義。

關鍵記憶點：join 以兩個關聯與一個 join 條件為輸入，回傳另一個關聯。這裡的關聯是一袋列（bag of rows），全部共享一個在查詢規劃期即已知的關聯資料型別定義。

Backward 理解視窗函式 資料型別 Forward