MySQL的三条JOIN子句使用指南

【51CTO.com快译】众所周知，关系型数据库中的每张表都包含了唯一或通用的数据，而且各个表之间都存在着逻辑上的联系。例如，相同的列名和数据类型，通常会被保存在作为公共值链接的表中。因此，我们在进行单个SQL查询时，JOIN语句往往被用于从共享着公共字段的多张表中，连接并获取数据。特别是在MySQL中，JOIN被用于聚合那些来自多张表的数据，并将它们整合为单个输出结果。而且，我们可以在SELECT、UPDATE和DELETE命令中，使用到JOIN。

JOIN入门

MySQL的JOIN类型能够指明在查询中，两个表是如何链接的。其中INNER JOIN、OUTER JOIN和CROSS JOIN都是被MySQL支持的三种JOIN子句。而LEFT JOIN和RIGHT JOIN则是两种不同类型的OUTER JOIN。为了更加直观地展示该如何使用JOIN，我们首先需要通过如下方式，创建一个新结构模式(schema)，以便为后续的操作提供数据示例。

MySQL

CREATE TABLE Users ( 
    UserID INT, 
    UserName VARCHAR(255), 
    Password VARCHAR(255), 
    isActive BOOLEAN 
); 
  
CREATE TABLE Userprofile ( 
    ProfileID INT, 
    LastName VARCHAR(255), 
    FirstName VARCHAR(255), 
    Email VARCHAR(255), 
    Phone VARCHAR(255) 
); 

接下来，我们要做的便是向其中插入一些数据。如下面语句所示，您可以根据自己的偏好，在表中插入任意数量的用户。

MySQL

INSERT INTO Users 
    (UserID, UserName,Password, isActive) 
VALUES 
    (1,'krofax','krofax1234', TRUE); 
  
INSERT INTO userprofile 
    (profileid, lastname, firstname, email, phone) 
VALUES 
    (1,'Ada', 'George', 'adageorge@gmail.com','1290003456'); 

MySQL INNER JOIN子句

我们可以使用INNER JOIN去检索各种常见的匹配性记录。例如，INNER JOIN子句可以通过检索表A和表B中的记录，为需要满足某种连接要求而筛选记录。这也是最常用的JOIN类型。下面的维恩图可以协助您更好地理解INNER JOIN。

以下是基于MySQL语法的INNER JOIN：

SELECT  
    COLUMNS 
FROM  
    tableA  
INNER JOINtableB 
    ON tableA.column = tableB.column; 

MySQL外部连接

与INNER JOIN相比，OUTER JOIN会生成不匹配(non-matching)的记录，以及匹配的数据行。也就是说，如果连接表中的数据行并不匹配的话，则会显示NULL值。如前所述，MySQL有两种不同形式的OUTER JOIN，它们分别是：MySQL LEFT JOIN和MySQL RIGHT JOIN。下面，让我们来详细地了解它们之间的区别。

MySQL LEFT JOIN子句

LEFT JOIN允许您从表A和表B中，获取满足连接条件的所有条目。而且，对于表A中不符合条件的记录，将显示为NULL值。下面的维恩图可以协助您更好地理解LEFT JOIN。

以下是基于MySQL语法的LEFT JOIN子句：

MySQL

COLUMNS 
  
tableA 
JOINtableB 
ON tableA.column = tableB.column; 

如上图所示：LEFT JOIN关键字会返回那些匹配Customers表，而在Orders表中没有匹配项的所有记录。

MySQL RIGHT JOIN子句

而RIGHT JOIN则允许用户获取表B中的所有条目，以及满足连接条件在表A中的条目。也就是说，表B中不符合条件的记录会被显示为NULL值。下面的维恩图可以协助您更好地理解RIGHT JOIN。

以下是基于MySQL语法的RIGHT JOIN子句：

MySQL

SELECT  
    COLUMNS 
FROM  
    tableA 
RIGHTJOINtableB 
    ON tableA.column = tableB.column; 

如上图所示：该RIGHT JOIN关键字返回那些匹配Employees表，而在Orders表中没有匹配项的所有记录。

MySQL CROSS JOIN子句

MySQL CROSS JOIN通常被称为笛卡尔连接(cartesian join)。它返回每个表中所有可能性数据行的组合。也就是说，如果不提供额外的条件，那么可将表A的每一行与表B中的所有行相乘，以得到结果集。下面的维恩图可以协助您更好地理解CROSS JOIN。

那么我们什么时候会需要用到这种JOIN呢?假设您接到一个任务：查找某个产品和颜色的所有可能性组合。那么CROSS JOIN在此时就能够派上用场了。不过，值得注意的是，CROSS JOIN可能会产生相当大的结果集!

以下是基于MySQL语法的CROSS JOIN子句：

MySQL

SELECT  
    COLUMNS 
FROM  
    tableA 
CROSSJOINtableB; 

JOIN的技巧

总的说来，在MySQL中，JOIN能够方便您执行单个JOIN查询，而省去了许多个简单的查询。因此，它能够带来更快的速度、更低的服务器开销、以及更少的MySQL与应用之间的数据传输。与SQL Server不同，MySQL虽然没有用于FULL OUTER JOIN的独特JOIN类型，但是您可以通过LEFT OUTER JOIN和RIGHT OUTER JOIN(请参照如下语句)的组合，以获得与FULL OUTER JOIN相同的输出效果。

MySQL

SELECT  
    *  
FROM  
    tableA 
LEFTJOINtableB  
    ON tableA.id = tableB.id 
UNION 
SELECT  
    *  
FROM  
    tableA 
RIGHTJOINtableB  
    ON tableA.id = tableB.id 

此外，使用MySQL JOIN，您还可以顺利地连接上述两张表。

MySQL

SELECT  
    * 
FROM  
    tableA 
LEFTJOINtableB 
    ON tableA.id = tableB.id 
LEFTJOINtableC 
    ON tableC.id = tableA.id; 

JOINS的实用性

• 更快的速度。在单个查询中，JOINS允许您从两个或多个链接的数据库表中，获取数据。显然，这比通过逐个运行查询，以获得相同的结果，要更加节省时间。
• MySQL的效率更高。由于连接是通过索引来执行的，因此JOINS会让MySQL具有更好的性能。
• 降低了服务器的负载。毕竟JOINS的单次查询执行，能够让服务器更快地输出结果。

为了能够在日常工作中灵活地使用JOIN，不少分析师或数据库管理员(DBA)都会选用Arctype for MySQL之类的工具，去生成复杂、完整的JOIN子句，以避免去记忆那些数百条条列名或别名。此外，此类自动化工具还能够通过丰富的功能，协助用户创建复杂的查询，并能够轻松地管理JOIN的各项条件。

原文A Guide to MySQL JOINs，作者：Blessing Krofegha

【51CTO译稿，合作站点转载请注明原文译者和出处为51CTO.com】