SQL join 用于根据两个或多个表中的列之间的关系,从这些表中查询数据。
七个JOINs的例子如下:
- INNER JOIN (内连接) = JOIN
- LEFT JOIN (左连接)
- RIGHT JOIN (右连接)
- OUTER JOIN (外连接)
- LEFT JOIN EXCLUDING INNER JOIN (左连接排除内连接结果)
- RIGHT JOIN EXCLUDING INNER JOIN (右连接排除内连接结果)
- OUTER JOIN EXCLUDING INNER JOIN (外连接排除内连接结果)
1.INNER JOIN (内连接)
SELECT <select_list>
FROM Table_A A
INNER JOIN Table_B B
ON A.Key = B.Key
返回表A和表B中所有匹配行的结果。
2.LEFT JOIN (左连接)
有些数据库,称为LEFT OUTER JOIN
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
LEFT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
3.RIGHT JOIN (右连接)
SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
RIGHT JOIN Orders
ON Persons.Id_P=Orders.Id_P
ORDER BY Persons.LastName
在某些数据库中, RIGHT JOIN 称为 RIGHT OUTER JOIN。
和LEFT JOIN相反。 RIGHT JOIN查询会返回所有右表中的记录,不管是否有匹配记录在左表中。它会返回所有右表中的匹配记录(没有匹配的当然会标记成null了)。
4.OUTER JOIN (外连接)
OUTER JOIN也可以当作是FULL OUTER JOIN 或者FULL JOIN(Mysql不支持)。它会返回两个表中所有行,左表A匹配右表B,右表B也匹配左表A (没有匹配的就显示null了)。一般写成下面样子:
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
LEFT JOIN teacher_card tc
ON t.tid = tc.tid
UNION
SELECT t.tid, t.name, tc.tid AS tcid, tc.description
FROM teacher t
RIGHT JOIN teacher_card tc
ON t.tid = tc.tid
5.LEFT Excluding JOIN
它会返回表A中所有不在表B中的行,一般写成:
SELECT <select_list>
FROM Table_A A
LEFT JOIN Table_B B
ON A.Key = B.Key
WHERE B.Key IS NULL
RIGHT Excluding JOIN
返回表B中所有不在表A中的行
SELECT <select_list>
FROM Table_A A
RIGHT JOIN Table_B B
ON A.Key = B.Key
WHERE A.Key IS NULL
多种join的区别