MySQL中LEFT JOIN、RIGHT JOIN、INNER JOIN的区别
SQL中的连接查询主要有LEFT JOIN(左连接)、RIGHT JOIN(右连接)、INNER JOIN(内连接)等,但是它们之间区别不是很大,主要是返回的结果集不同。
举例如下:
表person:
表salary:
1、LEFT JOIN 左联接,查询结果为左表中的所有记录和右表中联结字段相等的记录。
SQL:
SELECT T1.pid, T1.Number, T2.sid, T2.money FROM person T1 LEFT JOIN salary T2 ON T1.pid = T2.sid ORDER BY T1.pid ASC;
结果如下:
2、RIGHT JOIN右联接,查询结果为右表中的所有记录和左表中联结字段相等的记录。
SQL:
SELECT T1.pid, T1.Number, T2.sid, T2.money FROM person T1 RIGHT JOIN salary T2 ON T1.pid = T2.sid ORDER BY T1.pid ASC;
结果如下:
3、INNER JOIN等值连接,查询结果为两个表中联结字段相等的行。
SQL:
SELECT T1.pid, T1.Number, T2.sid, T2.money FROM person T1 INNER JOIN salary T2 ON T1.pid = T2.sid ORDER BY T1.pid ASC;
结果如下:
注意,SQL:
SELECT T1.pid, T1.Number, T2.sid, T2.money FROM person T1, salary T2 WHERE T1.pid = T2.sid ORDER BY T1.pid ASC;
与 INNER JOIN的结果是一样的。