SQL FULL JOIN 全联接

SQL FULL JOIN 关键字,不管左表还是右表,只要有匹配,就返回所有匹配的记录。

SQL FULL JOIN 语法

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

SQL FULL JOIN 范例

数据表: Employees

EmployeeId FirstName LastName Department Salary
203 Buucci Queshi Development 78000
204 Ghoie Yaahi Development 45800
205 Huillosihua Philibes Development 57000
308 Axedul Water Sales 57000

数据表: Tickets

TicketID TicketNo EmployeeId
1 1847 203
2 1923 204
3 2004 205
4 2166 206

选择所有的employees和所有的tickets:

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

结果会类似于:

EmployeeId FirstName LastName TicketNo
203 Buucci Queshi 1847
204 Ghoie Yaahi 1923
205 Huillosihua Philibes 2004
308 Axedul Water
2166

注:Employees表中的Axedul并没有一个对应的ticket,而Ticket 2166也没有一个对应的employee,但它们都出现在结果中。