SQL INNER JOIN 内联接

SQL INNER JOIN 内联接,用于从两个或以上数据表中选择数据,返回在两个表中同时匹配的记录。

SQL INNER JOIN 语法

SELECT Table1.Column1, Table1.Column2, ... , Table2.Column1, Table2.Column2, ...
FROM Table1
INNER JOIN Table2
ON Table1.ColumnName = Table2.ColumnName

注:SQL INNER JOIN是联接(JOIN)的默认方式。 “JOIN” = “INNER JOIN”

SQL INNER JOIN 范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
203 Mazojys Fxoj Finance 78000
204 Jozzh Lnanyo Finance 45800
205 Syllauu Dfaafk Finance 57000
206 Gecrrcc Srlkrt Finance 62000

数据表: Tickets

TicketID TicketNo EmployeeId
1 2349 203
2 2400 204
3 2438 205

选择有ticket的所有职员(Employee):

SELECT Employees.EmployeeId
           , Employees.FirstName
           , Employees.LastName
           , Tickets.TicketNo
FROM Employees
INNER JOIN Tickets
ON Employees.EmployeeId = Tickets.EmployeeId

结果会类似于:

EmployeeId FirstName LastName TicketNo
203 Mazojys Fxoj 2349
204 Jozzh Lnanyo 2400
205 Syllauu Dfaafk 2438

注:EmployeeId 为206的职员(employee)在表Tickets中没有任何匹配,所以没有出现在结果集中。

前面提过, “JOIN” = “INNER JOIN”,所以下面的语句会产生相同的结果:

SELECT Employees.EmployeeId
           , Employees.FirstName
           , Employees.LastName
           , Tickets.TicketNo
FROM Employees
JOIN Tickets
ON Employees.EmployeeId = Tickets.EmployeeId

在大多数数据库中,我们甚至可以写得更简单:

SELECT Employees.EmployeeId
           , Employees.FirstName
           , Employees.LastName
           , Tickets.TicketNo
FROM Employees, Tickets
WHERE Employees.EmployeeId = Tickets.EmployeeId