MySQL数据库中内连接、外连接用法简介

在关系型数据库中,我们经常是把数据存储到多个相互关联的表中,这些相互关联的表通过指定的列发生联系,下面介绍MySQL数据库中内连接、外连接的用法。

一、内连接

内连接也叫等值连接,通俗点说就是求两个表的交集。

语法:Select * from tableA inner jointableB

On tableA.column1=tableB.column2;

以Customers表和Orders表为例:

Customers表:

Orders表:

如上图所示,第一行是表名,后续各行是列名,从Customers表中的CustomerID画了一条线到Orders表的CustomerID,这就表示两张表之间存在关系,两张表共享了CusomerID列中存储的值。

现在我们给出一条带内连接的语句:

“select * from Customers

inner joinOrders

onCustomers.CustomerID =Orders.CustomerID;” 。

第一行的关键字select表示我们想要从两个表中获取所有的列(*),from子句表示我们想指定的第一个张是Customers;第二行引入了一个新的关键字inner join,该关键字表示我们想要连接的另一张,这个示例中要连接的表是Orders;第三行引入了关键字on,关键字on表示两张表如何准确地连接,此例中两张表是通过CustomerID来连接,在on子句中加上表名以示区分。

上述语句产生的结果如下:

在结果表中,OrderID有Orders表中全部的4行数据,而CustomerID只有3位客户,显然CustomerID为4的客户并不在Orders表中,所以inner join得到的结果是两张表共有的元素。

我们也注意到Natalie Lopez在结果表中有两条数据,而她在Customers表中只出现了一次,在Orders表中有两条记录,这两天记录都与Customers表中的Natalie的行匹配,因此返回了两条记录,即显示了所有可能的匹配。

以上就是关于内连接的介绍,上述语句还可以继续优化,结果表中CustomerID列出现了两次,我们并不想要重复的数据,表名Customers、Orders可以通过as关键字来显式指定,从而指定表的别名,列名同样可以通过as来指定,在这里我们用C表示Customers表,用O表示Orders表,Cust ID 表示CustomerID,Qty表示Quantity,Price表示PricePerItem,优化后的语句是:

“Select

C. CustomerID as 'Cust ID' ,

C.FirstName as 'First Name',

C.LastName as 'Last Name',

O.OrderID as 'Order ID',

O.Quantity as 'Qty' ,

O.PricePerItem as 'Price'

From Customers as C

Inner join

Orders as O

On C.CustomerID=O.CustomerID;”

其结果如下:

我们可以用as来指定列的别名和表的别名,注意as关键字是可选的,即在上述语句中,删去as返回同样的结果,为方便语句的可读性,一般建议保留as关键字。

内连接的另外一种实现方式

前面我们用了inner join来实现内连接,除此之外还可以用where子句来指定内连接,语句是

“Select* from Customers

inner joinOrders

onCustomers.CustomerID =Orders.CustomerID;”

这条语句等价于:

“Select * from Customers,Orders

WhereCustomers.CustomerID =Orders.CustomerID;”

以上两条语句都能实现内连接,建议还是使用inner join和on,它们显式地表示了连接的逻辑。

二、外连接

外连接有三种类型,分别是左外连接(left outer join)、右外连接(right outer join)、全连接(full outer join),一般称为左连接(left outer join)、右连接(right outer join)、全连接(full outer join),下面重点介绍左连接,右连接在概念上和左连接是相同的,不同之处在于连接中列出的表的顺序不同。

1、左(外)连接(left join)

从左表取出所有记录,与右表匹配,如右表没有对应左表的记录,则返回NULL值,表示右表的列。

语法:Select * from tableA left jointableB

On tableA.column1=tableB.column2;

继续以Customers表和Orders表为例:

左连接语句是

“Select * from Customers

left joinOrders

on Customers.CustomerID=Orders.CustomerID;”

返回的结果如下:

Customers表中CustomerID为1-4的记录都出现在上表中,其中CustomerID为2的客户在Orders表中有两条订购记录,在结果表中出现了两次,CustomersID为4的客户在Orders表中没有记录,所有结果表中来自Orders表的四个字段为NULL。

结果表出现了两个CustomerID,同样我们可以通过as关键字来指定表名、列名进行优化,方法参见内连接中的相关内容,此处就不再赘述,把innerjoin改为left join即可 。

2、右(外)连接(right join)

从右表取出所有记录,与左表匹配,如左表没有对应右表的记录,则返回NULL值,表示左表的列。

同样以Customers表和Orders表为例,右连接语句为

“Select * from Customers

right joinOrders

on Customers.CustomerID=Orders.CustomerID;”

返回的结果如下:

Orders表中只有CustomerID为1-3的客户,所以返回的表也只有CustomerID为1-3的记录。

关于左连接、右连接的介绍就到此为止,左连接可以理解为以左表为主表,右表为从表,查找的结果是返回左表中指定列所有的记录,及右表中能匹配到左表的指定列的记录,如不能匹配到的记录,则返回NULL值。右连接的理解与左连接相反。左连接与右连接是可以通用的,只要把列表的位置调换即可。

3、全连接(full join)

前面介绍左连接、右连接都是必须有一张主表,另一张表是可选的,即匹配的从表中的行不一定必须存在。

在全连接中,两个表都是从表,在这种情况下,表A和表B的行匹配,则会返回表A中所有的行,即使它在表B中没有匹配的行,表B中所有的行,即使它在表A中没有匹配的行,即返回表A和表B中所有的行,不论表A或表B中的行在另一张表中是否有匹配。

MySQL数据库中不提供全连接,可用left join和right join将两张表的数据取出,再用union去重。

语法:Select * from tableA left jointableB

OntableA.column1 = tableB.column1

Union

Select * from tableA right join tableB

OntableA.column1 = tableB.column1;

在实际工作中,较少用到全连接,全连接显示了两张表之间双向都没有匹配到的数据。

下一篇将介绍关系型数据库中自连接的用法。

  • 发表于:
  • 原文链接http://kuaibao.qq.com/s/20180324G023UE00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券