SQL Server 关键字

SQL Server (Transact-SQL) INNER JOIN 关键字(或有时称为简单 JOIN)用于组合两个表的列值,并在存在一个表时返回两个表中的所有行。列之间的匹配。

SQL Server INNER JOIN 关键字

语法

在 SQL Server (Transact-SQL) 中使用 INNER JOIN 关键字的语法如下:

SELECT table1.column1, table1.column2, table2.column1, table2.column2, ...
FROM table1
INNER 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
INNER JOIN Contact_Info
ON Employee.EmpID = Contact_Info.EmpID; 

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

NameAgeAddress
Marry24Brooklyn, New York, USA
Jo27Grenelle, Paris, France
Kim30Geuzenveld, Amsterdam, Netherlands
Huang28Yizhuangzhen, Beijing, China

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

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

以下代码的结果将是:

EmpIDNameCityAgeSalaryAddress
2MarryNew York242750Brooklyn, New York, USA
3JoParis272800Grenelle, Paris, France
4KimAmsterdam303100Geuzenveld, Amsterdam, Netherlands
6HuangBeijing282800Yizhuangzhen, Beijing, China

连接三个表

要连接三个表,只需重复重复使用 JOIN 关键字。请参阅下面的语法,其中 INNER JOIN 关键字用于连接三个表。任何其他 JOIN 类型都可以以相同的方式使用。同样,它可以用于连接任意数量的表。

语法

在 SQL Server (Transact-SQL) 中使用 INNER JOIN 关键字连接三个表的语法如下:

SELECT table1.column1, table1.column2, ... 
  table2.column1, table2.column2, ...
  table3.column1, table3.column2, ...
FROM table1 INNER JOIN table2
  ON table1.matching_column = table2.matching_column
INNER JOIN table3
  ON table1.matching_column = table3.matching_column; 

示例:

考虑一个包含名为 Employee、Bonus_Paid 和 Contact_Info<的表的数据库/i> 包含以下记录:

表 1:Employee

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

表2:Bonus_Paid表

EmpIDBonus
1500
2400
3450
4550
5400
6600

表3: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、Name、Bonus的记录和地址,可以使用以下查询:

SELECT Employee.EmpID, Employee.Name, 
  Bonus_Paid.Bonus, Contact_Info.Address
FROM Employee INNER JOIN Bonus_Paid
  ON Employee.EmpID = Bonus_Paid.EmpID
INNER JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID; 

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

EmpIDNameBonusAddress
2Marry400Brooklyn, New York, USA
3Jo450Grenelle, Paris, France
4Kim550Geuzenveld, Amsterdam, Netherlands
6Huang600Yizhuangzhen, Beijing, China

要获取上述记录,但条件如 Bonus 应大于等于 500,可以使用以下查询:

SELECT Employee.EmpID, Employee.Name, 
  Bonus_Paid.Bonus, Contact_Info.Gender, 
  Contact_Info.Address
FROM Employee INNER JOIN Bonus_Paid
  ON Employee.EmpID = Bonus_Paid.EmpID
INNER JOIN Contact_Info
  ON Employee.EmpID = Contact_Info.EmpID
WHERE Bonus_Paid.Bonus >= 500; 

以下代码的结果将是:

EmpIDNameBonusAddress
4Kim550Geuzenveld, Amsterdam, Netherlands
6Huang600Yizhuangzhen, Beijing, China