本文详细介绍了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;
扫一扫
