MySQL七种JOIN类型小结

在开始之前，我们创建两个表用于演示将要介绍的其中JOIN类型。

建表

CREATE TABLE `tbl_dept` (   `id` INT(11) NOT NULL AUTO_INCREMENT,   `deptName` VARCHAR(30) DEFAULT NULL,   `locAdd` VARCHAR(40) DEFAULT NULL,   PRIMARY KEY (`id`)) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

七种JOIN

1. A ∩ B

 SELECT < select_list > FROM TableA A INNER JOIN TableB B # 共有 ON A.Key = B.Key

 SELECT < select_list > FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key

 SELECT < select_list > FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key

 SELECT < select_list > FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key # ON时主表保留 WHERE B.Key IS NULL # 筛选A表数据

 SELECT < select_list > FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL

 SELECT < select_list > FROM TableA A FULL OUTER JOIN TableB B ## FULL OUTER 仅oracle支持 ON A.Key = B.Key

 SELECT < select_list > FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL

更多相关文章

随机推荐