MySQL的JOIN

演示数据表

-- ----------------------------
-- Table structure for table_a
-- ----------------------------
DROP TABLE IF EXISTS `table_a`;
CREATE TABLE `table_a`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of table_a
-- ----------------------------
INSERT INTO `table_a` VALUES (1, 1);
INSERT INTO `table_a` VALUES (2, 2);
INSERT INTO `table_a` VALUES (3, 4);
INSERT INTO `table_a` VALUES (4, 5);

-- ----------------------------
-- Table structure for table_b
-- ----------------------------
DROP TABLE IF EXISTS `table_b`;
CREATE TABLE `table_b`  (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `key` int(11) NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 0 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Records of table_b
-- ----------------------------
INSERT INTO `table_b` VALUES (1, 1);
INSERT INTO `table_b` VALUES (2, 2);
INSERT INTO `table_b` VALUES (3, 6);
INSERT INTO `table_b` VALUES (4, 7);

演示操作

-- LEFT JOIN
-- 图1
SELECT * FROM table_a a LEFT JOIN table_b b on a.key = b.key;
-- 图2
SELECT * FROM table_a a LEFT JOIN table_b b on a.key = b.key WHERE b.key IS NOT NULL;
-- RIGHT JOIN
-- 图3
SELECT * FROM table_a a RIGHT JOIN table_b b on a.key = b.key;
-- 图4
SELECT * FROM table_a a RIGHT JOIN table_b b on a.key = b.key WHERE a.key IS NOT NULL;
-- INNER JOIN
-- 图5
SELECT * FROM table_a a INNER JOIN table_b b on a.key = b.key;