在資料庫查詢中，JOIN操作是一個非常重要的技術，可以幫助我們從多個表中擷取所需的數據，並實現數據的關聯查詢。在本文中，會以MySQL方法為主，介紹什麼是JOIN操作，以及如何在SQL查詢中使用它。

什麼是JOIN？

在資料庫中，我們通常會有多個表，每個表中包含不同的數據。當我們需要從多個表中擷取數據時，就需要使用JOIN操作。JOIN操作是一種關聯查詢的技術，可以幫助我們將兩個或多個表中的數據進行關聯，並返回符合條件的數據。通過JOIN操作，我們可以實現數據的關聯查詢，從而獲取更全面的數據信息。

JOIN的種類

在SQL語言中，JOIN操作有多種不同的種類，主要包括以下幾種：

  • INNER JOIN：內連接，返回兩個表中符合條件的數據(交集的概念)。沒有符合條件的數據將不會返回。

  • LEFT JOIN：左外連接，返回左表(SQL語句中先寫的表)中所有的數據，以及右表中符合條件的數據。如果右表中沒有符合條件的數據，則返回NULL。

  • RIGHT JOIN：右外連接，返回右表(SQL語句中後寫的表)中所有的數據，以及左表中符合條件的數據。如果左表中沒有符合條件的數據，則返回NULL。

  • FULL JOIN：全外連接，返回兩個表中所有的數據，不管是否符合條件。(MySQL不支援)

  • CROSS JOIN：交叉連接，返回兩個表中所有的數據的笛卡爾積。(較少使用，因為會造成效能問題)

JOIN的用法

在SQL語言中，JOIN操作的用法非常簡單，只需要在SELECT語句中使用JOIN關鍵字，並指定要連接的表和連接條件即可。下面是一個簡單的例子：

1
2
3
SELECT * FROM table1
INNER JOIN table2
ON table1.id = table2.id;

在這個例子中，我們使用INNER JOIN操作將table1和table2兩個表按照id字段進行連接，返回兩個表中符合條件的數據。

特別說明JOIN的SELECT

  • 使用SELECT *，會取得兩個表中所有的欄位。
  • 使用SELECT table.*，會取得table表中所有的欄位。
  • 如果只有輸入欄位名稱，必須確認此欄位只有在一個表中存在，不能同時有相同名稱的欄位在兩個表中，不然會報錯。

4. JOIN的優點

JOIN操作有以下幾個優點：

  1. 提高查詢的靈活性：JOIN操作可以幫助我們從多個表中擷取所需的數據，提高查詢的靈活性。
  2. 簡化複雜查詢：JOIN操作可以幫助我們簡化複雜的查詢，使查詢更容易閱讀和維護(相較於FROM直接搜尋多表的而造成笛卡爾積的查詢方式來的好閱讀與維護)。

5. JOIN的各類用法

範例表資料

  • members表：

    id name
    1 John
    2 Mary
    3 Tom
    4 Ray

  • points表：

    member_id points
    1 100
    2 50
    3 80
    5 70
    1 50

INNER JOIN(JOIN)

  • 範例：
    假設我有會員表與點數表，我想取得點數最多的會員姓名，可以使用INNER JOIN。

    1
    2
    3
    4
    5
    6
    SELECT *
    FROM members
    INNER JOIN points
    ON members.id = points.member_id
    ORDER BY points DESC
    LIMIT 1;

  • 輸出結果：

    id name member_id points
    1 John 1 100
    1 John 1 50
    3 Tom 3 80
    2 Mary 2 50

  • 說明：

    1. 使用INNER JOIN將members表和points表按照id字段進行連接。
    2. 使用ORDER BY和LIMIT語句取得點數最多的會員姓名。
    3. 可以注意到為什麼John會出現兩次，因為points表中有兩筆John的資料，且都符合條件，因此可以知道JOIN並不會將重複的資料合併，而是有幾筆符合，就會返回幾筆。所以假設需要過濾重複資料，又剛好有唯一值的話，即可使用GROUP BY來過濾；如果沒有，則需要使用DISTINCT來過濾。

LEFT JOIN

  • 範例：
    假設我有會員表與點數表，我想取得所有會員的姓名和點數，可以使用LEFT JOIN。

    1
    2
    3
    4
    SELECT members.name, points.points
    FROM members
    LEFT JOIN points
    ON members.id = points.member_id;

  • 輸出結果：

    name points
    John 100
    John 50
    Mary 50
    Tom 80
    Ray NULL

  • 說明：

    1. 使用LEFT JOIN將members表和points表按照id字段進行連接。
    2. 返回所有會員的姓名和點數，如果會員沒有點數則點數為NULL。
    3. 可以注意到Ray的點數為NULL，因為points表中沒有Ray的資料。
    4. 這邊也一樣有John重複狀況。

RIGHT JOIN

  • 範例：
    假設我有會員表與點數表，我想取得所有點數的會員姓名和點數，可以使用RIGHT JOIN。

    1
    2
    3
    4
    SELECT members.name, points.points
    FROM members
    RIGHT JOIN points
    ON members.id = points.member_id;

  • 輸出結果：

    name points
    John 100
    John 50
    Mary 50
    Tom 80
    NULL 70

  • 說明：

    1. 使用RIGHT JOIN將members表和points表按照id字段進行連接。
    2. 返回所有點數的會員姓名和點數，如果會員沒有姓名則姓名為NULL。
    3. 可以注意到Tom的姓名為NULL，因為members表中沒有Tom的資料。
    4. 這邊也一樣有John重複狀況，由此可知，以上三種方式都會有重複資料的問題。

CROSS JOIN

  • 範例：
    假設我有會員表與點數表，我想取得所有會員的姓名和點數，可以使用CROSS JOIN。

    1
    2
    3
    SELECT members.name, points.points
    FROM members
    CROSS JOIN points;

  • 輸出結果：

    name points
    John 100
    John 50
    John 80
    John 70
    John 50
    Mary 100
    Mary 50
    Mary 80
    Mary 70
    Mary 50
    Tom 100
    Tom 50
    Tom 80
    Tom 70
    Tom 50
    Ray 100
    Ray 50
    Ray 80
    Ray 70
    Ray 50

  • 說明：

    1. 使用CROSS JOIN將members表和points表進行連接。
    2. 返回所有會員的姓名和點數的笛卡爾積。
    3. 可以注意到所有會員的姓名和點數都會出現在結果中，這樣的結果通常是不需要的，因為會導致數據量過大，效能問題。

總結

本文介紹了SQL JOIN操作的基本用法和種類，並通過具體的例子展示了JOIN操作的使用方法。JOIN操作是SQL語言中最重要的操作之一，它可以幫助我們從多個表中擷取所需的數據，提高查詢的靈活性和效率。在使用JOIN操作時，需要根據具體的需求選擇不同的JOIN類型，以實現數據的查詢和分析。