Skip to main content

[PSQL] PostgreSQL JOINS

PostgreSQL Joins

JOINS AND UNION#

PostgreSQL JOINS @ posrgresqltutorial

JOIN 包含了 INNER JOIN, OUTER JOINself-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;

參考#

Last updated on