内联
SELECT
*
FROM
t_employee employees0_
INNER JOIN t_customer customer1_ ON ( customer1_.id = employees0_.id );
左联
SELECT
*
FROM
t_employee employees0_
LEFT OUTER JOIN t_customer customer1_ ON ( customer1_.id = employees0_.id );
右联
SELECT
*
FROM
t_employee employees0_
RIGHT OUTER JOIN t_customer customer1_ ON ( customer1_.id = employees0_.id );
全联=左联+右联(MySql不支持)
SELECT * FROM t_employee te FULL JOIN t_customer tc ON (te.id = tc.id);
左外
SELECT
*
FROM
t_employee employees0_
LEFT OUTER JOIN t_customer customer1_ ON ( customer1_.id = employees0_.id )
WHERE
customer1_.id IS NULL;
右外
SELECT *
FROM
t_employee employees0_
RIGHT OUTER JOIN t_customer customer1_ ON ( customer1_.id = employees0_.id )
WHERE
employees0_.id IS NULL;
全外=左外+右外(MySql不支持)
SELECT *
FROM
t_employee te
FULL JOIN t_customer tc ON ( te.id = tc.id )
WHERE
te.id IS NULL
OR tc.id IS NULL;
数据库
Employee
Customer
-- ----------------------------
-- Table structure for t_employee
-- ----------------------------
DROP TABLE IF EXISTS `t_employee`;
CREATE TABLE `t_employee` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`employee_name` varchar(255) DEFAULT NULL,
`employee_part` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of t_employee
-- ----------------------------
INSERT INTO `t_employee` VALUES (1, '老潘', '总裁部');
INSERT INTO `t_employee` VALUES (2, '老王', '秘书部');
INSERT INTO `t_employee` VALUES (3, '老张', '设计部');
INSERT INTO `t_employee` VALUES (4, '老李', '运营部');
-- ----------------------------
-- Table structure for t_customer
-- ----------------------------
DROP TABLE IF EXISTS `t_customer`;
CREATE TABLE `t_customer` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`customer_name` varchar(255) DEFAULT NULL,
`customer_part` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
-- ----------------------------
-- Records of t_customer
-- ----------------------------
INSERT INTO `t_customer` VALUES (2, '老王', '秘书部');
INSERT INTO `t_customer` VALUES (3, '老张', '设计部');
INSERT INTO `t_customer` VALUES (4, '老刘', '人事部');
INSERT INTO `t_customer` VALUES (5, '老黄', '生产部');
