數據庫左連接、右連接、全聯接、左外、右外、全外

內聯

SELECT
*
FROM
temployee employees0
INNER JOIN tcustomer customer1 ON ( customer1.id = employees0.id );

左聯

SELECT
*
FROM
temployee employees0
LEFT OUTER JOIN tcustomer customer1 ON ( customer1.id = employees0.id );

右聯

SELECT
*
FROM
temployee employees0
RIGHT OUTER JOIN tcustomer customer1 ON ( customer1.id = employees0.id );

全聯=左聯+右聯(MySql不支持)

SELECT * FROM t_employee te FULL JOIN t_customer tc ON (te.id = tc.id);

左外

SELECT
*
FROM
temployee employees0
LEFT OUTER JOIN tcustomer customer1 ON ( customer1.id = employees0.id )
WHERE
customer1_.id IS NULL;

右外

SELECT *
FROM
temployee employees0
RIGHT OUTER JOIN tcustomer 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, '老黃', '生產部');