聯結類型 - AWS Clean Rooms
INNER左側 【外部 】右 【 外部 】完整 【外部】 【 左側 】 SEMI CROSS JOIN ANTI 加入 NATURAL

本文為英文版的機器翻譯版本，如內容有任何歧義或不一致之處，概以英文版為準。

聯結類型

INNER

這是預設聯結類型。傳回兩個資料表參考中具有相符值的資料列。

INNER JOIN 是 SQL 中使用的最常見聯結類型。這是根據通用資料欄或一組資料欄合併多個資料表資料的強大方式。

語法:

SELECT column1, column2, ..., columnn
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

下列查詢將傳回客戶和訂單資料表之間存在相符 customer_id 值的所有資料列。結果集將包含 customer_id、name、order_id 和 order_date 資料欄。

SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;

下列查詢是 LISTING 資料表和 SALES 資料表之間的內部聯結 (沒有 JOIN 關鍵字)，其中 LISTING 資料表中的 LISTID 是介於 1 和 5 之間。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示 LISTID 1、4 和 5 符合條件。

select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing, sales
where listing.listid = sales.listid
and listing.listid between 1 and 5
group by 1
order by 1;

listid | price  |  comm
-------+--------+--------
     1 | 728.00 | 109.20
     4 |  76.00 |  11.40
     5 | 525.00 |  78.75

下列範例是一個內部聯結搭配 ON 子句。在此情況下，不會傳回 NULL 資料列。

select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from sales join listing
on sales.listid=listing.listid and sales.eventid=listing.eventid
where listing.listid between 1 and 5
group by 1
order by 1;

listid | price  |  comm
-------+--------+--------
     1 | 728.00 | 109.20
     4 |  76.00 |  11.40
     5 | 525.00 |  78.75

以下查詢為 FROM 子句中兩個子查詢的內部聯結。查詢會尋找不同類別活動 (演奏會和表演) 的已售出和未售出票券數目。FROM 子句子查詢是資料表子查詢，可傳回多個資料欄和資料列。

select catgroup1, sold, unsold
from
(select catgroup, sum(qtysold) as sold
from category c, event e, sales s
where c.catid = e.catid and e.eventid = s.eventid
group by catgroup) as a(catgroup1, sold)
join
(select catgroup, sum(numtickets)-sum(qtysold) as unsold
from category c, event e, sales s, listing l
where c.catid = e.catid and e.eventid = s.eventid
and s.listid = l.listid
group by catgroup) as b(catgroup2, unsold)

on a.catgroup1 = b.catgroup2
order by 1;

catgroup1 |  sold  | unsold
----------+--------+--------
Concerts  | 195444 |1067199
Shows     | 149905 | 817736

左側 【外部 】

傳回左側資料表參考的所有值，以及右側資料表參考的相符值，如果沒有相符項目，則附加 NULL。它也稱為左側外部聯結。

它會傳回左側 （第一個） 資料表的所有資料列，以及右側 （第二個） 資料表的相符資料列。如果右側資料表中沒有相符項目，則結果集將包含右側資料表中資料欄的 NULL 值。您可以省略 OUTER 關鍵字，而且可以直接將聯結寫入 LEFT JOIN。與 LEFT OUTER JOIN 相反的是 RIGHT OUTER JOIN，它會傳回右側資料表的所有資料列，以及左側資料表的相符資料列。

語法:

SELECT column1, column2, ..., columnn
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

下列查詢會傳回客戶資料表中的所有資料列，以及訂單資料表中的相符資料列。如果客戶沒有訂單，結果集仍會包含該客戶的資訊，以及 order_id 和 order_date 資料欄的 NULL 值。

SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
LEFT OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

下列查詢是左側外部聯結。未在另一個資料表中找到相符項目時，左和右外部聯結會保留來自其中一個聯結資料表的值。左側和右側資料表分別是語法中最先和其次列出的資料表。NULL 值會用來填入結果集中的「空處」。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示 LISTIDs2 和 3 未產生任何銷售。

select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing left outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;

listid | price  |  comm
-------+--------+--------
     1 | 728.00 | 109.20
     2 | NULL   | NULL
     3 | NULL   | NULL
     4 |  76.00 |  11.40
     5 | 525.00 |  78.75

右 【 外部 】

從右側資料表參考傳回所有值，並從左側資料表參考傳回相符值，如果沒有相符項目，則附加 NULL。它也稱為正確的外部聯結。

它會傳回右側 （秒） 資料表中的所有資料列，以及左側 （第一個） 資料表的相符資料列。如果左側資料表中沒有相符項目，則結果集將包含左側資料表中資料欄的 NULL 值。您可以省略 OUTER 關鍵字，而且可以直接將聯結寫入 RIGHT JOIN。RIGHT OUTER JOIN 與 RIGHT OUTER JOIN 相反是 LEFT OUTER JOIN，它會傳回左側資料表的所有資料列，以及右側資料表的相符資料列。

語法:

SELECT column1, column2, ..., columnn
FROM table1
RIGHT [OUTER] JOIN table2
ON table1.column = table2.column;

下列查詢會傳回客戶資料表中的所有資料列，以及訂單資料表中的相符資料列。如果客戶沒有訂單，結果集仍會包含該客戶的資訊，以及 order_id 和 order_date 資料欄的 NULL 值。

SELECT orders.order_id, orders.order_date, customers.customer_id, customers.name
FROM orders
RIGHT OUTER JOIN customers
ON orders.customer_id = customers.customer_id;

下列查詢是右側外部聯結。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示 LISTID 1、4 和 5 符合條件。

select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing right outer join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;

listid | price  |  comm
-------+--------+--------
     1 | 728.00 | 109.20
     4 |  76.00 |  11.40
     5 | 525.00 |  78.75

完整 【外部】

從兩個關係傳回所有值，並在沒有相符項目的那一側附加 NULL 值。它也稱為完整的外部聯結。

它會傳回左側和右側資料表中的所有資料列，無論是否相符。如果沒有相符項目，則結果集將包含資料表中沒有相符資料列之資料欄的 NULL 值。您可以省略 OUTER 關鍵字，而且可以直接將聯結寫入 FULL JOIN。FULL OUTER JOIN 的使用頻率低於 LEFT OUTER JOIN 或 RIGHT OUTER JOIN，但在您需要查看兩個資料表中所有資料的某些情況下，即使沒有相符項目，它可能很有用。

語法:

SELECT column1, column2, ..., columnn
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

下列查詢會傳回客戶和訂單資料表中的所有資料列。如果客戶沒有訂單，結果集仍會包含該客戶的資訊，以及 order_id 和 order_date 資料欄的 NULL 值。如果訂單沒有相關聯的客戶，結果集將包含該訂單，其中包含 customer_id 和名稱欄的 NULL 值。

SELECT customers.customer_id, customers.name, orders.order_id, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

下列查詢是完全聯結。未在另一個資料表中找到相符項目時，完全聯結會保留來自聯結資料表的值。左側和右側資料表分別是語法中最先和其次列出的資料表。NULL 值會用來填入結果集中的「空處」。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。結果顯示 LISTIDs2 和 3 未產生任何銷售。

select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
group by 1
order by 1;

listid | price  |  comm
-------+--------+--------
     1 | 728.00 | 109.20
     2 | NULL   | NULL
     3 | NULL   | NULL
     4 |  76.00 |  11.40
     5 | 525.00 |  78.75

下列查詢是完全聯結。此查詢會比對 LISTING 資料表 (左側資料表) 和 SALES 資料表 (右側資料表) 中 LISTID 資料欄的值。只有不會產生任何銷售 (ListID 2 和 3) 的資料列才會顯示在結果中。

select listing.listid, sum(pricepaid) as price, sum(commission) as comm
from listing full join sales on sales.listid = listing.listid
where listing.listid between 1 and 5
and (listing.listid IS NULL or sales.listid IS NULL)
group by 1
order by 1;

listid | price  |  comm
-------+--------+--------
     2 | NULL   | NULL
     3 | NULL   | NULL

【 左側 】 SEMI

從資料表參考左側傳回與右側相符的值。它也稱為左半聯結。

它只會傳回左側 （第一個） 資料表中具有右側 （第二個） 資料表中相符資料列的資料列。它不會傳回右側資料表中的任何資料欄 - 只會傳回左側資料表中的資料欄。當您想要在一個資料表中找到符合另一個資料表的資料列時，不需要從第二個資料表傳回任何資料，LEFT SEMI JOIN 會很有用。LEFT SEMI JOIN 是將子查詢與 IN 或 EXISTS 子句搭配使用時更有效率的替代方案。

語法:

SELECT column1, column2, ..., columnn
FROM table1
LEFT SEMI JOIN table2
ON table1.column = table2.column;

對於訂單資料表中至少有一個訂單的客戶，下列查詢只會傳回客戶資料表中的 customer_id 和名稱資料欄。結果集不會包含訂單資料表中的任何資料欄。

SELECT customers.customer_id, customers.name
FROM customers
LEFT SEMI JOIN orders
ON customers.customer_id = orders.customer_id;

CROSS JOIN

傳回兩個關係的笛卡爾乘積。這表示結果集將包含來自兩個資料表的所有可能資料列組合，而不會套用任何條件或篩選條件。

當您需要從兩個資料表產生所有可能的組合資料時，例如建立報告以顯示客戶和產品資訊的所有可能組合時，CROSS JOIN 非常有用。CROSS JOIN 與其他聯結類型 (INNER JOIN、LEFT JOIN 等） 不同，因為它在 ON 子句中沒有聯結條件。CROSS JOIN 不需要加入條件。

語法:

SELECT column1, column2, ..., columnn
FROM table1
CROSS JOIN table2;

下列查詢會傳回結果集，其中包含來自客戶和產品資料表的所有可能 customer_id、Customer_name、product_id 和 product_name 組合。如果客戶資料表有 10 個資料列，而產品資料表有 20 個資料列，則 CROSS JOIN 的結果集將包含 10 x 20 = 200 個資料列。

SELECT customers.customer_id, customers.name, products.product_id, products.product_name
FROM customers
CROSS JOIN products;

下列查詢是 LISTING 資料表和 SALES 資料表的交叉聯結或笛卡爾聯結，並且使用述詞來限制結果。此查詢會在 SALES 資料表和 LISTING 資料表中比對 LISTID 資料欄值，以找出兩個資料表中的 LISTID 1、2、3、4 和 5。結果顯示 20 個符合條件的資料列。

select sales.listid as sales_listid, listing.listid as listing_listid
from sales cross join listing
where sales.listid between 1 and 5
and listing.listid between 1 and 5
order by 1,2;

sales_listid | listing_listid
-------------+---------------
1            | 1
1            | 2
1            | 3
1            | 4
1            | 5
4            | 1
4            | 2
4            | 3
4            | 4
4            | 5
5            | 1
5            | 1
5            | 2
5            | 2
5            | 3
5            | 3
5            | 4
5            | 4
5            | 5
5            | 5

ANTI 加入

從左側資料表參考傳回與右側資料表參考不相符的值。它也稱為左反聯結。

當您想要在一個資料表中尋找沒有相符項目的資料列時，ANTI JOIN 是一項有用的操作。

語法:

SELECT column1, column2, ..., columnn
FROM table1
LEFT ANTI JOIN table2
ON table1.column = table2.column;

下列查詢將傳回所有尚未下訂單的客戶。

SELECT customers.customer_id, customers.name
FROM customers
LEFT ANTI JOIN orders
ON customers.customer_id = orders.customer_id
WHERE orders.order_id IS NULL;

NATURAL

指定來自兩個關係的資料列將隱含地符合具有相符名稱的所有資料欄的相等性。

它會自動比對兩個資料表之間具有相同名稱和資料類型的資料欄。它不需要您在 ON 子句中明確指定聯結條件。它將兩個資料表之間的所有相符資料欄合併為結果集。

當您要聯結的資料表具有具有相同名稱和資料類型的資料欄時，NATURAL JOIN 非常方便。不過，通常建議使用更明確的 INNER JOIN ... ON 語法可讓聯結條件更明確且更容易理解。

語法:

SELECT column1, column2, ..., columnn
FROM table1
NATURAL JOIN table2;

下列範例是兩個資料表 employees和 之間的自然聯結departments，具有下列資料欄：

  • employees 資料表：employee_id、first_name、last_name、 department_id

  • departments 資料表：department_id、 department_name

下列查詢會根據資料department_id欄傳回結果集，其中包含兩個資料表之間所有相符資料列的名字、姓氏和部門名稱。

SELECT e.first_name, e.last_name, d.department_name
FROM employees e
NATURAL JOIN departments d;

下列範例是兩個資料表之間的自然聯結。在這種情況下，兩個資料表中的 listid、sellerid、eventid 和 dateid 資料欄會具有相同的名稱和資料類型，因此會被用來作為聯結資料欄。結果限制為 5 個資料列。

select listid, sellerid, eventid, dateid, numtickets
from listing natural join sales
order by 1
limit 5;

listid | sellerid  | eventid | dateid | numtickets
-------+-----------+---------+--------+-----------
113    | 29704     | 4699    | 2075   | 22
115    | 39115     | 3513    | 2062   | 14
116    | 43314     | 8675    | 1910   | 28
118    | 6079      | 1611    | 1862   | 9
163    | 24880     | 8253    | 1888   | 14