转载
一、JOIN 使用介绍
下面例子使用的数据表如下：
-- ------------------------------ Table structure for dancing_hobby-- ----------------------------DROP TABLE IF EXISTS `dancing_hobby`;CREATE TABLE `dancing_hobby` ( `id` int(11) NOT NULL COMMENT '主键', `stu_num` int(11) NULL DEFAULT NULL COMMENT '学号', `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `age` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年龄', `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '男生1 女生2', PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of dancing_hobby-- ----------------------------INSERT INTO `dancing_hobby` VALUES (1, 1001, '小红', '18', '1');INSERT INTO `dancing_hobby` VALUES (2, 1002, '小红', '18', '2');INSERT INTO `dancing_hobby` VALUES (3, 1003, '小黑', '18', '1');INSERT INTO `dancing_hobby` VALUES (4, 2001, '大绿', '18', '1');INSERT INTO `dancing_hobby` VALUES (5, 1005, '小紫', '18', '2');INSERT INTO `dancing_hobby` VALUES (6, 2002, '大蓝', '18', '1');INSERT INTO `dancing_hobby` VALUES (7, 1007, '小黄', '18', '1');INSERT INTO `dancing_hobby` VALUES (8, 2003, '大白', '18', '1');INSERT INTO `dancing_hobby` VALUES (9, 1009, '小橙', '18', '2');INSERT INTO `dancing_hobby` VALUES (10, 2004, '大青', '18', '2');-- ------------------------------ Table structure for sing_hobby-- ----------------------------DROP TABLE IF EXISTS `sing_hobby`;CREATE TABLE `sing_hobby` ( `id` int(11) NOT NULL COMMENT '主键', `stu_num` int(11) NULL DEFAULT NULL COMMENT '学号', `name` varchar(30) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '姓名', `age` varchar(3) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '年龄', `sex` varchar(2) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL COMMENT '男生1 女生2', PRIMARY KEY (`id`) USING BTREE) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;-- ------------------------------ Records of sing_hobby-- ----------------------------INSERT INTO `sing_hobby` VALUES (1, 1001, '小红', '18', '1');INSERT INTO `sing_hobby` VALUES (2, 1002, '小红', '18', '2');INSERT INTO `sing_hobby` VALUES (3, 1003, '小黑', '18', '1');INSERT INTO `sing_hobby` VALUES (4, 1004, '小绿', '18', '1');INSERT INTO `sing_hobby` VALUES (5, 1005, '小紫', '18', '2');INSERT INTO `sing_hobby` VALUES (6, 1006, '小蓝', '18', '1');INSERT INTO `sing_hobby` VALUES (7, 1007, '小黄', '18', '1');INSERT INTO `sing_hobby` VALUES (8, 1008, '小白', '18', '1');INSERT INTO `sing_hobby` VALUES (9, 1009, '小橙', '18', '2');INSERT INTO `sing_hobby` VALUES (10, 1010, '小青', '18', '2');
1.INNER JOIN（内连接）
INNER JOIN 一般被译作内连接。内连接查询能将左表（表 A）和右表（表 B）中能关联起来的数据连接后返回。
SELECTFROM Table_A AINNER JOIN Table_B BON A.Key = B.Key
例子：
-- 内连接SELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sexFROM sing_hobby sing inner JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num
2.LEFT JOIN（左连接）
LEFT JOIN 一般被译作左连接，也写作 LEFT OUTER JOIN。左连接查询会返回左表（表 A）中所有记录，不管右表（表 B）中有没有关联的数据。在右表中找到的关联数据列也会被一起返回。
SELECTFROM Table_A ALEFT JOIN Table_B BON A.Key = B.Key
例子：
-- 左连接SELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sexFROM sing_hobby sing LEFT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num
3.RIGHT JOIN（右连接）
RIGHT JOIN 一般被译作右连接，也写作 RIGHT OUTER JOIN。右连接查询会返回右表（表 B）中所有记录，不管左表（表 A）中有没有关联的数据。在左表中找到的关联数据列也会被一起返回。
SELECTFROM Table_A ARIGHT JOIN Table_B BON A.Key = B.Key
例子：
-- 右连接SELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sexFROM sing_hobby sing RIGHT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num
4.FULL OUTER JOIN（外连接）
FULL OUTER JOIN 一般被译作外连接、全连接，实际查询语句中可以写作 FULL OUTER JOIN 或 FULL JOIN。外连接查询能返回左右表里的所有记录，其中左右表里能关联起来的记录被连接后返回。
SELECTFROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.Key
注：mysql不支持外连接，可以使用 UNION ALL 代替，但要注意，如果要查询的两个表不是全部字段，那查询两个表的字段个数要相同，如下例子。
例子：
-- 外连接SELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sex FROM sing_hobby singLEFT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_numUNION ALLSELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sex FROM sing_hobby singRIGHT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_numWHERE sing.stu_num IS NULL-- 外连接SELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sexFROM sing_hobby sing FULL JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num
5.LEFT JOIN EXCLUDING INNER JOIN（左连接-内连接）
返回左表有，右表没有的关联数据记录集。
SELECTFROM Table_A ALEFT JOIN Table_B BON A.Key = B.KeyWHERE B.Key IS NULL
例子：
-- 左内连接SELECT sing.`name` AS sing_name, sing.sex sing_sex, dancing.`name` dancing_name, dancing.sex dancing_sex FROM sing_hobby sing LEFT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num WHERE dancing.stu_num IS NULL
6.RIGHT JOIN EXCLUDING INNER JOIN（右连接-内连接）
返回右表有，左表没有的关联数据记录集。
SELECTFROM Table_A ARIGHT JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULL
例子：
-- 右内连接SELECT sing.`name` AS sing_name, sing.sex sing_sex, dancing.`name` dancing_name, dancing.sex dancing_sex FROM sing_hobby sing RIGHT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num WHERE sing.stu_num IS NULL
7.OUTER JOIN EXCLUDING INNER JOIN（外连接-内连接）
返回右表、左表没有的关联数据记录集。
SELECTFROM Table_A AFULL OUTER JOIN Table_B BON A.Key = B.KeyWHERE A.Key IS NULL OR B.Key IS NULL
例子：
-- 排除内连接的外连接SELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sex FROM sing_hobby singLEFT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_numWHERE sing.stu_num IS NULL OR dancing.stu_num IS NULLUNION ALLSELECT sing.`name` as sing_name,sing.sex sing_sex,dancing.`name` dancing_name,dancing.sex dancing_sex FROM sing_hobby singRIGHT JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_numWHERE sing.stu_num IS NULL OR dancing.stu_num IS NULL-- 排除内连接的外连接SELECT sing.`name` AS sing_name, sing.sex sing_sex, dancing.`name` dancing_name, dancing.sex dancing_sex FROM sing_hobby sing FULL JOIN dancing_hobby dancing ON sing.stu_num = dancing.stu_num WHERE sing.stu_num IS NULL OR dancing.stu_num IS NULL
