跳至主要内容

[PSQL] PostgreSQL JOINS

PostgreSQL Joins

JOINS AND UNION​

PostgreSQL JOINS @ posrgresqltutorial

JOIN 包含了 INNER JOIN, OUTER JOIN 和 self-join。其中 pk 指的是 primary key，fk 指的是 foreign key。

  • INNER JOIN (JOIN)：只會合併表 A 和表 B 中能夠匹配到的 row，其他 row 都不會被保留下來。
  • FULL OUTER JOIN：會合併表 A 和表 B 中能夠匹配到的 row，其他 row 中若有遺失的值都補入 null。如此將可以確保表 A 和表 B 的所有資料都將被保留下來。
  • LEFT OUTER JOIN (LEFT JOIN)：以表 A 為主進行匹配，表 A 中無法與表 B 中匹配到的部分，該 row 會留下，缺漏值補上 null；但表 B 中無法與表 A 相匹配的部分，該 row 不會保留下來。如此將可以確保表 A 的所有資料都將被保留下來。
  • RIGHT OUTER JOIN
  • LEFT OUTER JOIN with WHERE：如此可以取得表 A 中有但表 B 中沒有的資料, e.g., WHERE TableB.id IS null。
  • RIGHT OUTER JOIN with WHERE
  • FULL OUTER JOIN with WHERE：如此可以取得表 A 和表 B 中兩者沒有相互匹配到的部分, e.g., WHERE TableA.id IS null OR TableB.id IS null。

SQL JOINS overview @ evernote from Udemy

INNER JOIN​

keywords: JOIN, INNER JOIN​

在這裡 tableA 是 Main Table：

SELECT tableA.column_1, tableB.column_2 --...
FROM tableA
[INNER] JOIN tableB ON tableA.pk_a = tableB.fk_a;

在多數的 SQL 引擎中，如果只寫 JOIN 預設就會使用 INNER JOIN。

執行這個指令後 PostgreSQL 會先搜尋 B table 來看看有沒有 row 符合 table_a.pk_a = table_b.fk_a。 如果找到的話，它會合併這倆 row 中的 columns，變成一個合併好的 row，並且把合併好的 row 當成結果回傳。

有些時候兩個表中可能有相同名稱的欄位，因此我們必須要透過 table_name.column_name 這種寫法來避免模糊性（沒有重複的欄位名稱時即可不用）。

Example​

-- 因為 customer_id 在兩個 table 中都有，因此需要寫清楚是拿誰的 customer_id, i.e., customer.customer_id
-- 但當這個欄位是 unique 而沒有重複時，可以不用把 table_name 寫出來
SELECT
customer.customer_id, first_name, last_name, email, amount, payment_date
FROM customer
INNER JOIN payment ON payment.customer_id = customer.customer_id
WHERE amount > 0
ORDER BY amount;

PostgreSQL Join - Inner Join

LEFT OUTER JOIN​

keywords: LEFT JOIN, LEFT OUTER JOIN​
SELECT tableA.column_1, tableB.column_2 -- ...
FROM tableA
LEFT [OUTER] JOIN tableB ON tableA.pk_a = tableB.fk_a;

在多數的 SQL 引擎中，可以將 LEFT OUTER JOIN 簡寫為 LEFT JOIN。

Example​

SELECT
inventory_id, store_id, title,
film.film_id AS film_filmId,
inventory.film_id AS inventory_filmId,
FROM film
LEFT JOIN inventory on film.film_id = inventory.film_id
WHERE inventory_id IS null;

RIGHT OUTER JOIN​

keywords: RIGHT JOIN, RIGHT OUTER JOIN​

相對於 LEFT JOIN。

FULL OUTER JOIN​

keywords: FULL OUTER JOIN​

UNION​

UNION 可以將兩個以上透過 SELECT 陳述句篩選出來的結果合併成一個。使用時機通常是有兩個結構很相似的 table，但並沒有完全一樣（not perfectly normalized）。在使用時有幾點需要留意：

  1. 兩個 query 回傳的 columns 「數目」必須相同（數目必須相同但欄位的值不必相同）。
  2. 在 query 中對應到的 columns 其資料型態必須能相同。
  3. UNION 會把所有重複的 rows 移除，除非使用的是 UNION ALL。
SELECT column_1, column_2
FROM table_1
UNION
SELECT column_1, column_2
FROM table_2;

EXAMPLE​

假設現在有兩張表：

imgur

當我們使用：

SELECT * FROM sales2007q1
UNION
SELECT * FROM sales2007q2;

將會得到如下的表：

imgur

留意這裡 Mary 只剩下一筆資料是因為 Mary 在 Q1 和 Q2 的 row 內容相同（i.e., amount 都是 10000），因此兩筆只會保留下一筆（除非使用的是 UNION ALL）。

Self Join​

Self join 用在需要將同一張 table 中的兩個 row 進行合併時使用。

假設有一張 table，我們希望找出和 Joe 相同 location 的其他員工，但因為某些原因並不能直接寫 'New York'，這時候就可以使用 self join。

imgur

雖然我們一樣可以透過 subquery 的方式來解決：

-- subquery
SELECT name FROM employee
WHERE location IN (
  SELECT location FROM employee WHERE name = 'Joe'
)

但透過 self join 可以更有效率。self join 可以用 , 搭配 WHERE；也可以用 JOIN 搭配 ON，效果是一樣的：

-- 使用 self join 的方式: 用 WHERE 和 ,
SELECT e1.name
FROM employee AS e1, employee AS e2
WHERE
e1.location = e2.location
AND
e2.name = 'Joe';

-- 使用 self join 的方式: 用 JOIN 和 ON
SELECT e1.name
FROM employee AS e1
JOIN employee AS e2
ON e1.location = e2.location
AND e2.name = 'Joe';

當我們還沒下 WHERE 時會得到一張像這樣的表：

imgur

接著透過 WHERE 篩選後，最後得到的表會像這樣：

imgur

Examples​

-- 使用 , 搭配 WHERE 達到 self join
SELECT a.first_name, a.last_name, b.first_name, b.last_name
FROM customer AS a, customer AS b
WHERE b.first_name = a.last_name;

-- 使用 JOIN 搭配 ON 達到 self join
SELECT a.first_name, a.last_name, b.first_name, b.last_name
FROM customer AS a
[INNER] JOIN customer AS b
ON b.first_name = a.last_name;

-- 一樣可以使用 LEFT [OUTER] JOIN
SELECT a.first_name, a.last_name, b.first_name, b.last_name
FROM customer AS a
LEFT [OUTER] JOIN customer AS b
ON b.first_name = a.last_name;

參考​