数据库连接(JOIN)介绍

在数据库操作中,连接(JOIN)是一种常用的操作,用于将两个或多个表中的数据根据一定的条件组合在一起。本文将介绍内连接(INNER JOIN)的基本概念和使用方法。所有示例均基于MicrosoftSQLServer Management Studio和AdventureWorks2012数据库。

连接(INNER JOIN)的基本概念

内连接用于匹配两个表中的行。匹配的行将保留在结果中,不匹配的行将被排除。匹配条件通常被称为连接条件。当匹配条件涉及相等性时,即完全匹配一个列的内容到另一个列,这种连接被称为等值连接(equijoin)。大多数情况下,使用的连接都是等值连接。一个常见的情况是,需要将一个表的主键连接到另一个表的外键。这在数据反规范化时是必需的。

INNER JOIN的基本结构

以下是一个简单的SELECT语句,包含INNERJOIN子句的例子。

SELECT columnlist FROM maintable INNER JOIN secondtable ON join condition

假设想要创建一个包含人员及其电话号码的目录。为此,需要从Person和PersonPhone表中组合行。实现此目的的SQL语句如下:

SELECT Person.FirstName, Person.LastName, PersonPhone.PhoneNumber FROM Person.Person INNER JOIN Person.PersonPhone ON Person.BusinessEntityID = PersonPhone.BusinessEntityID

这种类型的连接被称为等值连接,因为使用等式作为连接条件。可以说,遇到的绝大多数连接都是等值连接。在某些特殊情况下,使用不等于或大于等其他比较运算符是有意义的。这些类型的连接被称为非等值连接(non equi-joins),稍后将在本文中探讨。一旦数据连接完成,还可以使用熟悉的WHERE和ORDER BY子句进行过滤和排序。子句的顺序如下:

SELECT columnlist FROM maintable INNER JOIN secondtable ON join condition WHERE filter condition ORDER BY columnlist

WHERE子句可以引用连接表中的任何字段。为了清晰起见,最好的做法是使用表名作为前缀。排序也是如此。可以使用任何字段进行排序;然而,通常选择排序的是正在选择的一个或多个字段。以电话目录为例,但只列出以C开头的姓氏的人员。考虑到这个要求,SQL语句变为:

SELECT Person.FirstName, Person.LastName, PersonPhone.PhoneNumber FROM Person.Person INNER JOIN Person.PersonPhone ON Person.BusinessEntityID = PersonPhone.BusinessEntityID WHERE Person.LastName LIKE 'C%' ORDER BY Person.LastName

注意连接条件保持不变。唯一的变化是添加了WHERE和ORDER BY子句。到目前为止,这些应该是熟悉的。

表别名的使用

当创建更复杂的SQL语句时,它们可能会变得难以阅读。这在很大程度上是由于语言的冗长(啰嗦)特性。可以通过使用表的另一个名称来减少这种情况。这是通过在表名的第一次引用后放置别名来完成的。这里有一个简单的例子,将Person表别名为P:

SELECT P.FirstName, P.LastName FROM Person.Person AS P

在这个例子中,使用了字母P。它实际上可以是任何数量的字符,但喜欢保持别名简单。别名在连接中特别有用。让以最后一次连接为例,但这次将Person别名为P,PersonPhone别名为PP。以下是语句:

SELECT P.FirstName, P.LastName, PP.PhoneNumber FROM Person.Person AS P INNER JOIN Person.PersonPhone AS PP ON P.BusinessEntityID = PP.BusinessEntityID WHERE P.LastName LIKE 'C%' ORDER BY P.LastName

关于命名,认为可以将表别名为A、B、C,但这可能会变得难以记住。系统是使用表的第一个字母作为别名。如果表有两个单词,比如PersonPhone,通常会使用每个单词的第一个字母。

多字段连接

在某些情况下,可能需要将两个或更多字段组合在一起。当表的主键由两个或更多列组成时,就会发生这种情况。这可以通过使用AND运算符和额外的连接条件轻松完成。

考虑一个例子,想知道每个教练教授课程的所有日期和时间。在示例中,Class表的主键是两个字段:ClassName和Instructor。为了构建时间表,需要通过匹配ClassName和Instructor来连接Class和Section。

SELECT C.Instructor, C.ClassName, S.Day, S.Hour FROM Class AS C INNER JOIN Section AS S ON C.ClassName = S.ClassName AND C.Instructor = S.Instructor ORDER BY C.Instructor, S.Day, S.Hour

注意:这个例子在AdventureWorks2012数据库中不会工作。

连接三个或更多表

在AdventureWorks2012数据库中,需要连接PhoneNumberType表以了解列出的电话号码是家庭、手机还是办公电话。为了创建一个包含姓名、电话号码类型和电话号码的目录,必须连接三个表。这个关系如下所示:

在查询中包含另一个表就像添加另一个INNER JOIN子句到语句中一样简单。

SELECT P.FirstName, P.LastName, PP.PhoneNumber FROM Person.Person AS P INNER JOIN Person.PersonPhone AS PP ON P.BusinessEntityID = PP.BusinessEntityID INNER JOIN Person.PhoneNumberType AS PT ON PP.PhoneNumberTypeID = PT.PhoneNumberTypeID WHERE P.LastName LIKE 'C%' ORDER BY P.LastName

认为没有硬性规定如何格式化连接子句,倾向于将它们分开放在自己的行上,并且在指定列时,将“连接来自”列放在前面,如下所示:

INNER JOIN ToTable ON FromTable.PKID = ToTable.FKID

对于本文中的所有示例,首先编写了语句,然后使用TidySQL自动格式化它们。

自连接

自连接是指将表连接到它自己。当使用自连接时,给表别名是很重要的。假设想列出所有部门组内的部门。在AdventureWorks2012数据库中,这是通过以下自连接完成的:

SELECT D1.Name, D2.Name FROM HumanResources.Department AS D1 INNER JOIN HumanResources.Department AS D2 ON d1.GroupName = d2.GroupName

由于Department表正在连接到它自己,需要区分Department的两个出现。它们被别名为D1和D2。除了表相同之外,可以看到这种类型的连接没有什么特别的。

非等值连接只是指连接条件中没有等号。可能想知道为什么要使用非等值连接。说实话,可以想象,想出例子可能很难;然而,它们确实存在,当需要这样做时,会感激知道如何使用它们。使用非等值连接的一个原因是当需要验证拥有干净的数据时。考虑AdventureWorks2012 Products表。可能想要检查产品名称对于每个列出的产品是否唯一。做到这一点的一种方法是通过名称自连接产品表。

SELECT P1.ProductID, P1.Name FROM Production.Product AS P1 INNER JOIN Production.Product AS P2 ON P1.Name = P2.Name ORDER BY P1.ProductNumber

这个查询允许比较匹配产品名称的每一行的值。如果名称是唯一的,那么应该只有当ProductID相等时才会有匹配。所以,如果正在寻找重复的产品名称,那么遵循的是,想要找到具有不同ProductID的匹配记录。这在下面的图表中显示:

SELECT P1.ProductID, P1.Name, P1.ProductNumber FROM Production.Product AS P1 INNER JOIN Production.Product AS P2 ON P1.Name = P2.Name AND P1.ProductID <> P2.ProductID ORDER BY P1.ProductNumber
沪ICP备2024098111号-1
上海秋旦网络科技中心:上海市奉贤区金大公路8218号1幢 联系电话:17898875485