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;