sql 内连接，左连接，右连接_jeecg left outer join-优快云博客

本文详细介绍了SQL中的不同连接类型，包括隐式内连接、显式内连接、左连接、右连接以及如何通过联合操作实现全外连接。通过具体的示例展示了如何从两个表中获取所需的数据。

有两个表：

DROP DATABASE IF EXISTS `info`;
CREATE DATABASE `info` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `info`;


#
# Source for table user
#


DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `code` varchar(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table user
#
LOCK TABLES `user` WRITE;
/*!40000 ALTER TABLE `user` DISABLE KEYS */;


INSERT INTO `user` VALUES (1,'001','小样');
INSERT INTO `user` VALUES (2,'002','小明');
INSERT INTO `user` VALUES (3,'004','鲁迅');
/*!40000 ALTER TABLE `user` ENABLE KEYS */;
UNLOCK TABLES;


#
# Source for table user_info
#


DROP TABLE IF EXISTS `user_info`;
CREATE TABLE `user_info` (
  `Id` int(11) NOT NULL AUTO_INCREMENT,
  `user_code` varchar(255) DEFAULT NULL,
  `dowatch` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


#
# Dumping data for table user_info
#
LOCK TABLES `user_info` WRITE;
/*!40000 ALTER TABLE `user_info` DISABLE KEYS */;


INSERT INTO `user_info` VALUES (1,'001','逛超市');
INSERT INTO `user_info` VALUES (2,'001','踢足球');
INSERT INTO `user_info` VALUES (3,'002','洗衣服');
INSERT INTO `user_info` VALUES (4,'002','睡觉');
INSERT INTO `user_info` VALUES (5,'003','吃饭');
/*!40000 ALTER TABLE `user_info` ENABLE KEYS */;
UNLOCK TABLES;

表:user:

表二：user_info

执行以下语句显示结果如下：

--隐式内连接
select u.code,u.name,ui.dowatch from user u,user_info ui where u.code =ui.user_code ;

--显式内连接
SELECT u.code, u.name,ui.dowatch from user u inner join user_info ui on u.code=ui.user_code;

--左连接显式全部的左表数据
SELECT u.code, u.name,ui.dowatch from user u left join user_info ui on u.code=ui.user_code;

--右连接显式全部的右表数据
SELECT u.code, u.name,ui.dowatch from user u right join user_info ui on u.code=ui.user_code;

--全外连接 Mysql不支持 full outer join 可以用以下方式实现

SELECT u.code, u.name,ui.dowatch from user u left join user_info ui on u.code=ui.user_code union SELECT u.code, u.name,ui.dowatch from user u right join user_info ui on u.code=ui.user_code;