Compare types of joins   比較連接類型

Previously, you explored SQL joins and how to use them to join data from multiple tables when these tables share a common column. You also examined how there are different types of joins, and each of them returns different rows from the tables being joined. In this reading, you'll review these concepts and more closely analyze the syntax needed for each type of join.
先前，你已經探索過 SQL 連接以及如何使用它們來連接多個表格中的數據，當這些表格共享一個共同的欄位時。你也檢視過不同類型的連接，每一種連接會從被連接的表格中返回不同的行。在這篇閱讀中，你將回顧這些概念，並更仔細地分析每種類型連接所需的語法。

Inner joins  內部連接

The first type of join that you might perform is an inner join. INNER JOIN returns rows matching on a specified column that exists in more than one table.
你可能會執行的第一種類型連接是內部連接。INNER JOIN 返回在多個表格中存在的指定欄位上匹配的行。

It only returns the rows where there is a match, but like other types of joins, it returns all specified columns from all joined tables. For example, if the query joins two tables with SELECT *, all columns in both of the tables are returned.
它僅返回有匹配的行，但與其他類型的連接一樣，它會返回所有連接表中指定的所有列。例如，如果查詢使用 SELECT * 連接兩個表，則會返回這兩個表中的所有列。

Note: If a column exists in both of the tables, it is returned twice when SELECT * is used.
注意：如果兩個表中都存在某個欄位，當使用 SELECT * 時，該欄位會返回兩次。

The syntax of an inner join
內部連接的語法

To write a query using INNER JOIN, you can use the following syntax:
要使用 INNER JOIN 撰寫查詢，可以使用以下語法：

SELECT *

FROM employees

INNER JOIN machines ON employees.device_id = machines.device_id;

You must specify the two tables to join by including the first or left table after FROM and the second or right table after INNER JOIN
您必須指定要連接的兩個表，方法是在 FROM 之後包含第一個或左邊的表，然後在 INNER JOIN 之後包含第二個或右邊的表。.

After the name of the right table, use the ON keyword and the = operator to indicate the column you are joining the tables on. It's important that you specify both the table and column names in this portion of the join by placing a period (.) between the table and the column.  
在右邊表的名稱之後，使用 ON 關鍵字和 = 運算符來指示您要在哪個欄位上連接表。重要的是，您必須在這部分的連接中指定表和欄位名稱，方法是在表和欄位之間放置一個句點 (.)。

In addition to selecting all columns, you can select only certain columns.  For example, if you only want the join to return the username, operating_system and device_id columns, you can write this query:
除了選擇所有欄位之外，您還可以只選擇某些欄位。例如，如果您只希望連接返回 username、operating_system 和 device_id 欄位，您可以撰寫以下查詢：

SELECT username, operating_system, employees.device_id

FROM  employees  FROM employees

INNER JOIN machines ON employees.device_id = machines.device_id;

Note: In the example query, username and operating_system only appear in one of the two tables, so they are written with just the column name. On the other hand, because device_id appears in both tables, it's necessary to indicate which one to return by specifying both the table and column name (employees.device_id).
注意：在範例查詢中，username 和 operating_system 僅出現在其中一個表中，因此只需寫出欄位名稱即可。另一方面，由於 device_id 出現在兩個表中，因此需要通過指定表名和欄位名稱（employees.device_id）來指明要返回哪一個。

Outer joins  外部連接

Outer joins expand what is returned from a join. Each type of outer join returns all rows from either one table or both tables.
外部連接擴展了連接返回的內容。每種類型的外部連接都會返回來自一個表或兩個表的所有行。

Left joins  左連接

When joining two tables, LEFT JOIN returns all the records of the first table, but only returns rows of the second table that match on a specified column. 
在連接兩個表時，LEFT JOIN 會返回第一個表的所有記錄，但僅返回第二個表中在指定列上匹配的行。

The syntax for using LEFT JOIN is demonstrated in the following query:
使用 LEFT JOIN 的語法在以下查詢中展示：

SELECT *

FROM employees

LEFT JOIN machines ON employees.device_id = machines.device_id;

As with all joins, you should specify the first or left table as the table that comes after FROM and the second or right table as the table that comes after LEFT JOIN. In the example query, because employees is the left table, all of its records are returned. Only records that match on the device_id column are returned from the right table, machines. 
如同所有的連接操作一樣，你應該將第一個或左邊的表指定為在 FROM 之後的表，第二個或右邊的表指定為在 LEFT JOIN 之後的表。在範例查詢中，因為 employees 是左邊的表，所以它的所有記錄都會被返回。只有在 device_id 欄位上匹配的記錄才會從右邊的表 machines 返回。

Right joins  右連接

When joining two tables, RIGHT JOIN returns all of the records of the second table, but only returns rows from the first table that match on a specified column.
在連接兩個表時，RIGHT JOIN 會返回第二個表的所有記錄，但僅返回第一個表中在指定欄位上匹配的行。

The following query demonstrates the syntax for RIGHT JOIN
以下查詢展示了 RIGHT JOIN 的語法:

SELECT *

FROM employees

RIGHT JOIN machines ON employees.device_id = machines.device_id;

RIGHT JOIN has the same syntax as LEFT JOIN, with the only difference being the keyword RIGHT JOIN instructs SQL to produce different output. The query returns all records from machines, which is the second or right table. Only matching records are returned from employees, which is the first or left table.
RIGHT JOIN 的語法與 LEFT JOIN 相同，唯一的區別是關鍵字 RIGHT JOIN 指示 SQL 產生不同的輸出。查詢返回所有來自 machines 的記錄，這是第二個或右邊的表。只有匹配的記錄會從 employees 返回，這是第一個或左邊的表。

Note:  You can use LEFT JOIN and RIGHT JOIN and return the exact same results if you use the tables in reverse order. The following RIGHT JOIN query returns the exact same result as the LEFT JOIN query demonstrated in the previous section:
注意：如果將表的順序顛倒使用，您可以使用 LEFT JOIN 和 RIGHT JOIN 並返回完全相同的結果。以下的 RIGHT JOIN 查詢返回的結果與前一節中展示的 LEFT JOIN 查詢完全相同：

SELECT *

FROM machines

RIGHT JOIN employees ON employees.device_id = machines.device_id;

All that you have to do is switch the order of the tables that appear before and after the keyword used for the join, and you will have swapped the left and right tables.
你所需要做的就是調換出現在連接關鍵字之前和之後的表格順序，這樣你就完成了左右表格的交換。

Full outer joins   全外部連接

FULL OUTER JOIN returns all records from both tables. You can think of it as a way of completely merging two tables.
FULL OUTER JOIN 會返回兩個表中的所有記錄。你可以將其視為完全合併兩個表的一種方式。

You can review the syntax for using FULL OUTER JOIN in the following query:
你可以在以下查詢中查看使用 FULL OUTER JOIN 的語法：

SELECT *

FROM employees

FULL OUTER JOIN machines ON employees.device_id = machines.device_id;

The results of a FULL OUTER JOIN query include all records from both tables. Similar to INNER JOIN, the order of tables does not change the results of the query.
FULL OUTER JOIN 查詢的結果包含來自兩個表的所有記錄。與 INNER JOIN 類似，表的順序不會改變查詢的結果。

Key takeaways  關鍵要點

When working in SQL, there are multiple ways to join tables.  All joins return the records that match on a specified column. INNER JOIN will return only these records. Outer joins also return all other records from one or both of the tables. LEFT JOIN returns all records from the first or left table, RIGHT JOIN returns all records from the second or right table, and FULL OUTER JOIN returns all records from both tables. 
在使用 SQL 時，有多種方式可以連接資料表。所有的連接都會返回在指定欄位上匹配的記錄。INNER JOIN 只會返回這些匹配的記錄。外部連接還會返回來自一個或兩個資料表的所有其他記錄。LEFT JOIN 返回來自第一個或左邊資料表的所有記錄，RIGHT JOIN 返回來自第二個或右邊資料表的所有記錄，而 FULL OUTER JOIN 則返回來自兩個資料表的所有記錄。