MariaDB 关键字

使用 MariaDB RIGHT OUTER JOIN 关键字(或有时称为 RIGHT JOIN)根据列之间的匹配来组合两个表的列值。它返回连接右侧表的所有行以及连接左侧表的匹配行。右侧表中与左侧表中没有匹配的行,结果表将包含 NULL 值。

MariaDB RIGHT OUTER JOIN 关键字

语法

在 MariaDB 中使用 RIGHT OUTER JOIN 关键字的语法如下:

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

示例:

考虑一个包含名为Employee 和 Contact_Info 表的数据库包含以下记录:

表 1:Employee 表

EmpIDNameCityAgeSalary
1JohnLondon253000
2MarryNew York242750
3JoParis272800
4KimAmsterdam303100
5RameshNew Delhi283000
6HuangBeijing282800

表 2:Contact_Info 表

Phone_NumberEmpIDAddressGender
+1-80540980002Brooklyn, New York, USAF
+33-1479961013Grenelle, Paris, FranceM
+31-2011503194Geuzenveld, Amsterdam, NetherlandsF
+86-10997324586Yizhuangzhen, Beijing, ChinaM
+65-672348247Yishun, SingaporeM
+81-3577990728Koto City, Tokyo, JapanM

右外连接基于匹配列EmpID的Employee和Contact_Info表,查询如下。这将从 Employee 表中获取 Name 和 Age 列,并从 Contact_Info 中获取 Address 列

SELECT Employee.Name, Employee.Age, Contact_Info.Address 
FROM Employee
RIGHT OUTER JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID; 

这将产生如下所示的结果:

NameAgeAddress
Marry24Brooklyn, New York, USA
Jo27Grenelle, Paris, France
Kim30Geuzenveld, Amsterdam, Netherlands
Huang28Yizhuangzhen, Beijing, China
Yishun, Singapore
Koto City, Tokyo, Japan

要获取表的所有字段,使用table.* 关键字,例如 - 要获取 Employee 表的所有字段,Employee.* 用于以下 MariaDB 代码:

SELECT Employee.*, Contact_Info.Address 
FROM Employee
RIGHT OUTER JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID; 

以下代码的结果将是:

EmpIDNameCityAgeSalaryAddress
2MarryNew York242750Brooklyn, New York, USA
3JoParis272800Grenelle, Paris, France
4KimAmsterdam303100Geuzenveld, Amsterdam, Netherlands
6HuangBeijing282800Yizhuangzhen, Beijing, China
Yishun, Singapore
Koto City, Tokyo, Japan