inner join, left join, right join, full join 的区别-CSDN博客

本文链接：https://blog.csdn.net/kaige8312/article/details/141125999

1. 初始化表结构

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for t_city
-- ----------------------------
DROP TABLE IF EXISTS `t_city`;
CREATE TABLE `t_city`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `city` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

-- ----------------------------
-- Table structure for t_shool
-- ----------------------------
DROP TABLE IF EXISTS `t_shool`;
CREATE TABLE `t_shool`  (
  `id` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NOT NULL,
  `city_code` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  `school` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci NULL DEFAULT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8mb4 COLLATE = utf8mb4_0900_ai_ci ROW_FORMAT = Dynamic;

SET FOREIGN_KEY_CHECKS = 1;

INSERT INTO `t_city` (`id`, `code`, `city`) VALUES ('1', 'A', '北京');
INSERT INTO `t_city` (`id`, `code`, `city`) VALUES ('2', 'B', '上海');
INSERT INTO `t_city` (`id`, `code`, `city`) VALUES ('3', 'C', '成都');
INSERT INTO `t_city` (`id`, `code`, `city`) VALUES ('4', 'D', '重庆');


INSERT INTO `t_shool` (`id`, `city_code`, `school`) VALUES ('1', 'A', '北京大学');
INSERT INTO `t_shool` (`id`, `city_code`, `school`) VALUES ('2', 'B', '上海大学');
INSERT INTO `t_shool` (`id`, `city_code`, `school`) VALUES ('3', 'C', '成都大学');
INSERT INTO `t_shool` (`id`, `city_code`, `school`) VALUES ('4', 'E', '西南大学');
INSERT INTO `t_shool` (`id`, `city_code`, `school`) VALUES ('5', 'D', '重庆科技大学');
INSERT INTO `t_shool` (`id`, `city_code`, `school`) VALUES ('6', 'A', '清华大学');

innner join


---------------------INNER JOIN---------------------------
SELECT
	* 
FROM
	t_city
	INNER JOIN t_shool ON ( t_city.`code` = t_shool.city_code )
WHERE 1=1

left join

SELECT
	* 
FROM
	t_city
	LEFT JOIN t_shool ON ( t_city.`code` = t_shool.city_code )
WHERE 1=1

right join

SELECT
	* 
FROM
	t_city
	RIGHT JOIN t_shool ON ( t_city.`code` = t_shool.city_code )
WHERE 1=1