MySQL的三条JOIN子句使用指南

众所周知,关系型数据库中的每张表都包含了唯一或通用的数据,而且各个表之间都存在着逻辑上的联系(www.fanma.net)。例如,相同的列名和数据类型,通常会被保存在作为公共值链接的表中。因此,我们在进行单个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

  1. CREATE TABLE Users (
  2. UserID INT,
  3. UserName VARCHAR(255),
  4. Password VARCHAR(255),
  5. isActive BOOLEAN
  6. );
  7. CREATE TABLE Userprofile (
  8. ProfileID INT,
  9. LastName VARCHAR(255),
  10. FirstName VARCHAR(255),
  11. Email VARCHAR(255),
  12. Phone VARCHAR(255)
  13. );

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

MySQL

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

MySQL INNER JOIN子句

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

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

  1. SELECT
  2. COLUMNS
  3. FROM
  4. tableA
  5. INNER JOINtableB
  6. 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

  1. COLUMNS
  2. tableA
  3. JOINtableB
  4. ON tableA.column = tableB.column;

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

MySQL RIGHT JOIN子句

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

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

MySQL

  1. SELECT
  2. COLUMNS
  3. FROM
  4. tableA
  5. RIGHTJOINtableB
  6. 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

  1. SELECT
  2. COLUMNS
  3. FROM
  4. tableA
  5. CROSSJOINtableB;

JOIN的技巧

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

MySQL

  1. SELECT
  2. *
  3. FROM
  4. tableA
  5. LEFTJOINtableB
  6. ON tableA.id = tableB.id
  7. UNION
  8. SELECT
  9. *
  10. FROM
  11. tableA
  12. RIGHTJOINtableB
  13. ON tableA.id = tableB.id

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

MySQL

  1. SELECT
  2. *
  3. FROM
  4. tableA
  5. LEFTJOINtableB
  6. ON tableA.id = tableB.id
  7. LEFTJOINtableC
  8. ON tableC.id = tableA.id;

JOINS的实用性

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

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

公司名称:衡水启瑞机械有限公司