SQL Join连接

SQL JOIN 连接

SQL join 用于把来自两个或多个表的行结合起来。

SQL JOIN

SQL JOIN 子句用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段。

简单地说，就是先确定一个主表作为结果集，然后，把其他表的行有选择性地”连接”在主表结果集上。

最常见的 JOIN 类型：SQL INNER JOIN（简单的 JOIN）。 SQL INNER JOIN 从多个表中返回满足 JOIN 条件的所有行。

让我们看看选自 “Orders” 表的数据：

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

然后，看看选自 “Customers” 表的数据：

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

请注意，”Orders” 表中的 “CustomerID” 列指向 “Customers” 表中的客户。上面这两个表是通过 “CustomerID” 列联系起来的。

然后，如果我们运行下面的 SQL 语句（包含 INNER JOIN）：

示例：

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

运行结果如下所示：

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	1996-09-18

不同的 SQL JOIN

在我们继续讲解实例之前，我们先列出您可以使用的不同的 SQL JOIN 类型：

INNER JOIN：如果表中有至少一个匹配，则返回行
LEFT JOIN：即使右表中没有匹配，也从左表返回所有的行
RIGHT JOIN：即使左表中没有匹配，也从右表返回所有的行
FULL JOIN：只要其中一个表中存在匹配，则返回行
SELF JOIN ：用于将表连接到自己，就好像该表是两个表一样，临时重命名了SQL语句中的至少一个表
CARTESIAN JOIN：从两个或多个连接表返回记录集的笛卡儿积

SQL JOIN 连接详细用法

SQL 连接（JOIN） 子句用于将数据库中两个或者两个以上表中的记录组合起来。连接通过共有值将不同表中的字段组合在一起。

我们来看看”Orders”表中的选择：

OrderID	CustomerID	OrderDate
10308	2	1996-09-18
10309	37	1996-09-19
10310	77	1996-09-20

然后，查看”Customers”表中的选择：

CustomerID	CustomerName	ContactName	Country
1	Alfreds Futterkiste	Maria Anders	Germany
2	Ana Trujillo Emparedados y helados	Ana Trujillo	Mexico
3	Antonio Moreno Taquería	Antonio Moreno	Mexico

请注意，”Orders”表中的”客户ID”列是指”CustomerID”表中的”客户ID”。上面两个表格之间的关系是”CustomerID”列。

然后，我们可以创建下面的SQL语句（包含一个INNER JOIN），它选择两个表中具有匹配值的记录：

**　代码示例：**

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;

它会产生这样的东西：

OrderID	CustomerName	OrderDate
10308	Ana Trujillo Emparedados y helados	9/18/1996
10365	Antonio Moreno Taquería	11/27/1996
10383	Around the Horn	12/16/1996
10355	Around the Horn	11/15/1996
10278	Berglunds snabbköp	8/12/1996

考虑下面两个表，（a）CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan   | 25 | Delhi     | 1500.00 |
| 3 | kaushik  | 23 | Kota      | 2000.00 |
| 4 | Chaitali | 25 | Mumbai    | 6500.00 |
| 5 | Hardik   | 27 | Bhopal    | 8500.00 |
| 6 | Komal    | 22 | MP        | 4500.00 |
| 7 | Muffy    | 24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

（b）另一个表是 ORDERS 表：

+-----+---------------------+-------------+--------+
|OID | DATE                | CUSTOMER_ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 |   1500 | | 101 | 2009-11-20 00:00:00 |           2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 |   2060 | +-----+---------------------+-------------+--------+

现在，让我们用 SELECT 语句将这个两张表连接（JOIN）在一起：

SQL> SELECT ID, NAME, AGE, AMOUNT
        FROM CUSTOMERS, ORDERS
        WHERE  CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

上述语句的运行结果如下所示：

+----+----------+-----+--------+
| ID | NAME     | AGE | AMOUNT | +----+----------+-----+--------+ |  3 | kaushik |  23 | 3000 |
| 3 | kaushik  | 23 |   1500 | |  2 | Khilan |  25 | 1560 |
| 4 | Chaitali | 25 |   2060 | +----+----------+-----+--------+

不同类型的SQL联接

SQL 中有多种不同的连接：

内连接（INNER JOIN）：当两个表中都存在匹配时，才返回行。
左连接（LEFT JOIN）：返回左表中的所有行，即使右表中没有匹配的行。
右连接（RIGHT JOIN）：返回右表中的所有行，即使左表中没有匹配的行。
全连接（FULL JOIN）：只要某一个表存在匹配，就返回行。
笛卡尔连接（CARTESIAN JOIN）：返回两个或者更多的表中记录集的笛卡尔积。

内连接

最常用也最重要的连接形式是内连接，有时候也被称作”EQUIJOIN”（等值连接）。

内连接根据连接谓词来组合两个表中的字段，以创建一个新的结果表。SQL 查询会比较逐个比较表 1 和表 2 中的每一条记录，来寻找满足连接谓词的所有记录对。当连接谓词得以满足时，所有满足条件的记录对的字段将会结合在一起构成结果表。

语法：

**　内连接**的基本语法如下所示：

sql

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

示例：

考虑如下两个表格，（a）CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan   | 25 | Delhi     | 1500.00 |
| 3 | kaushik  | 23 | Kota      | 2000.00 |
| 4 | Chaitali | 25 | Mumbai    | 6500.00 |
| 5 | Hardik   | 27 | Bhopal    | 8500.00 |
| 6 | Komal    | 22 | MP        | 4500.00 |
| 7 | Muffy    | 24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

（b）ORDERS 表：

+-----+---------------------+-------------+--------+
| OID | DATE                | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 |   1500 | | 101 | 2009-11-20 00:00:00 |           2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 |   2060 | +-----+---------------------+-------------+--------+

现在，让我们用内连接将这两个表连接在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     INNER JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

上述语句将会产生如下结果：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                | +----+----------+--------+---------------------+ |  3 | kaushik |   3000 | 2009-10-08 00:00:00 |
| 3 | kaushik  | 1500 | 2009-10-08 00:00:00 | |  2 | Khilan |   1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +----+----------+--------+---------------------+

左连接

**　左链接**返回左表中的所有记录，即使右表中没有任何满足匹配条件的记录。这意味着，如果 ON 子句在右表中匹配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自右表的字段都为 NULL。

这就意味着，左连接会返回左表中的所有记录，加上右表中匹配到的记录，或者是 NULL （如果连接谓词无法匹配到任何记录的话）。

语法：

**　左连接**的基本语法如下所示：

SELECT table1.column1, table2.column2...
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;

这里，给出的条件可以是任何根据你的需要写出的条件。

示例：

考虑如下两个表格，（a）CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan   | 25 | Delhi     | 1500.00 |
| 3 | kaushik  | 23 | Kota      | 2000.00 |
| 4 | Chaitali | 25 | Mumbai    | 6500.00 |
| 5 | Hardik   | 27 | Bhopal    | 8500.00 |
| 6 | Komal    | 22 | MP        | 4500.00 |
| 7 | Muffy    | 24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

（b）ORDERS 表：

+-----+---------------------+-------------+--------+
| OID | DATE                | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 |   1500 | | 101 | 2009-11-20 00:00:00 |           2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 |   2060 | +-----+---------------------+-------------+--------+

现在，让我们用左连接将这两个表连接在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

上述语句将会产生如下结果：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                | +----+----------+--------+---------------------+ |  1 | Ramesh |   NULL | NULL |
| 2 | Khilan   | 1560 | 2009-11-20 00:00:00 | |  3 | kaushik |   3000 | 2009-10-08 00:00:00 |
| 3 | kaushik  | 1500 | 2009-10-08 00:00:00 | |  4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
| 5 | Hardik   | NULL | NULL                | |  6 | Komal |   NULL | NULL |
| 7 | Muffy    | NULL | NULL                | +----+----------+--------+---------------------+

右连接

**　右链接**返回右表中的所有记录，即是左表中没有任何满足匹配条件的记录。这意味着，如果 ON 子句在左表中匹配到了 0 条记录，该连接仍然会返回至少一条记录，不过返回的记录中所有来自左表的字段都为 NULL。

这就意味着，右连接会返回右表中的所有记录，加上左表中匹配到的记录，或者是 NULL （如果连接谓词无法匹配到任何记录的话）。

语法：

**　右连接**的基本语法如下所示：

SELECT table1.column1, table2.column2...
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;

这里，给出的条件可以是任何根据你的需要写出的条件。

示例：

考虑如下两个表格，（a）CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan   | 25 | Delhi     | 1500.00 |
| 3 | kaushik  | 23 | Kota      | 2000.00 |
| 4 | Chaitali | 25 | Mumbai    | 6500.00 |
| 5 | Hardik   | 27 | Bhopal    | 8500.00 |
| 6 | Komal    | 22 | MP        | 4500.00 |
| 7 | Muffy    | 24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

（b）ORDERS 表：

+-----+---------------------+-------------+--------+
| OID | DATE                | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 |   1500 | | 101 | 2009-11-20 00:00:00 |           2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 |   2060 | +-----+---------------------+-------------+--------+

现在，让我们用右连接将这两个表连接在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

上述语句将会产生如下结果：

+------+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                | +------+----------+--------+---------------------+ |    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
| 3 | kaushik  | 1500 | 2009-10-08 00:00:00 | |    2 | Khilan |   1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+

全连接

**　全连接**将左连接和右连接的结果组合在一起。

语法：

SELECT table1.column1, table2.column2...
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;

这里，给出的条件可以是任何根据你的需要写出的条件。

示例：

考虑如下两个表格，（a）CUSTOMERS 表：

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY |
+----+----------+-----+-----------+----------+
| 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
| 2 | Khilan   | 25 | Delhi     | 1500.00 |
| 3 | kaushik  | 23 | Kota      | 2000.00 |
| 4 | Chaitali | 25 | Mumbai    | 6500.00 |
| 5 | Hardik   | 27 | Bhopal    | 8500.00 |
| 6 | Komal    | 22 | MP        | 4500.00 |
| 7 | Muffy    | 24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

（b）ORDERS 表：

+-----+---------------------+-------------+--------+
| OID | DATE                | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000 |
| 100 | 2009-10-08 00:00:00 | 3 |   1500 | | 101 | 2009-11-20 00:00:00 |           2 | 1560 |
| 103 | 2008-05-20 00:00:00 | 4 |   2060 | +-----+---------------------+-------------+--------+

现在让我们用全连接将两个表连接在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     FULL JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID;

上述语句将会产生如下结果：

+------+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                | +------+----------+--------+---------------------+ |    1 | Ramesh |   NULL | NULL |
| 2 | Khilan   | 1560 | 2009-11-20 00:00:00 | |    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
| 3 | kaushik  | 1500 | 2009-10-08 00:00:00 | |    4 | Chaitali |   2060 | 2008-05-20 00:00:00 |
| 5 | Hardik   | NULL | NULL                | |    6 | Komal |   NULL | NULL |
| 7 | Muffy    | NULL | NULL                | |    3 | kaushik |   3000 | 2009-10-08 00:00:00 |
| 3 | kaushik  | 1500 | 2009-10-08 00:00:00 | |    2 | Khilan |   1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | +------+----------+--------+---------------------+

如果你所用的数据库不支持全连接，比如 MySQL，那么你可以使用 UNION ALL子句来将左连接和右连接结果组合在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     LEFT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID
UNION ALL
     SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS
     RIGHT JOIN ORDERS
     ON CUSTOMERS.ID = ORDERS.CUSTOMER_ID

笛卡尔连接（交叉连接）

**　笛卡尔连接** 或者交叉连接返回两个或者更多的连接表中记录的笛卡尔乘积。也就是说，它相当于连接谓词总是为真或者缺少连接谓词的内连接。

语法：

**　笛卡尔连接** 或者说交叉连接的基本语法如下所示：

SELECT table1.column1, table2.column2...
FROM  table1, table2 [, table3 ]

示例：

考虑如下两个表格，（a）CUSTOMERS 表：

    +----+----------+-----+-----------+----------+
    | ID | NAME     | AGE | ADDRESS   | SALARY |
    +----+----------+-----+-----------+----------+
    | 1 | Ramesh   | 32 | Ahmedabad | 2000.00 |
    | 2 | Khilan   | 25 | Delhi     | 1500.00 |
    | 3 | kaushik  | 23 | Kota      | 2000.00 |
    | 4 | Chaitali | 25 | Mumbai    | 6500.00 |
    | 5 | Hardik   | 27 | Bhopal    | 8500.00 |
    | 6 | Komal    | 22 | MP        | 4500.00 |
    | 7 | Muffy    | 24 | Indore    | 10000.00 |
    +----+----------+-----+-----------+----------+

（b）ORDERS 表：

    +-----+---------------------+-------------+--------+
    | OID | DATE                | ID | AMOUNT | +-----+---------------------+-------------+--------+ | 102 | 2009-10-08 00:00:00 |           3 | 3000 |
    | 100 | 2009-10-08 00:00:00 | 3 |   1500 | | 101 | 2009-11-20 00:00:00 |           2 | 1560 |
    | 103 | 2008-05-20 00:00:00 | 4 |   2060 | +-----+---------------------+-------------+--------+

现在，让我用内连接将这两个表连接在一起：

SQL> SELECT  ID, NAME, AMOUNT, DATE
     FROM CUSTOMERS, ORDERS;

上述语句将会产生如下结果：

+----+----------+--------+---------------------+
| ID | NAME     | AMOUNT | DATE                | +----+----------+--------+---------------------+ |  1 | Ramesh |   3000 | 2009-10-08 00:00:00 |
| 1 | Ramesh   | 1500 | 2009-10-08 00:00:00 | |  1 | Ramesh |   1560 | 2009-11-20 00:00:00 |
| 1 | Ramesh   | 2060 | 2008-05-20 00:00:00 | |  2 | Khilan |   3000 | 2009-10-08 00:00:00 |
| 2 | Khilan   | 1500 | 2009-10-08 00:00:00 | |  2 | Khilan |   1560 | 2009-11-20 00:00:00 |
| 2 | Khilan   | 2060 | 2008-05-20 00:00:00 | |  3 | kaushik |   3000 | 2009-10-08 00:00:00 |
| 3 | kaushik  | 1500 | 2009-10-08 00:00:00 | |  3 | kaushik |   1560 | 2009-11-20 00:00:00 |
| 3 | kaushik  | 2060 | 2008-05-20 00:00:00 | |  4 | Chaitali |   3000 | 2009-10-08 00:00:00 |
| 4 | Chaitali | 1500 | 2009-10-08 00:00:00 | |  4 | Chaitali |   1560 | 2009-11-20 00:00:00 |
| 4 | Chaitali | 2060 | 2008-05-20 00:00:00 | |  5 | Hardik |   3000 | 2009-10-08 00:00:00 |
| 5 | Hardik   | 1500 | 2009-10-08 00:00:00 | |  5 | Hardik |   1560 | 2009-11-20 00:00:00 |
| 5 | Hardik   | 2060 | 2008-05-20 00:00:00 | |  6 | Komal |   3000 | 2009-10-08 00:00:00 |
| 6 | Komal    | 1500 | 2009-10-08 00:00:00 | |  6 | Komal |   1560 | 2009-11-20 00:00:00 |
| 6 | Komal    | 2060 | 2008-05-20 00:00:00 | |  7 | Muffy |   3000 | 2009-10-08 00:00:00 |
| 7 | Muffy    | 1500 | 2009-10-08 00:00:00 | |  7 | Muffy |   1560 | 2009-11-20 00:00:00 |
| 7 | Muffy    | 2060 | 2008-05-20 00:00:00 | +----+----------+--------+---------------------+