MySQL连接查询

一、左连接查询，以左边的表为主，查询的数据包括左边表所有的数据以及左右表有交集的数据(left join)

SELECT * FROM user1 t1 LEFT JOIN user1_copy t2 ON t1.`name`=t2.`name`;

  二、右连接查询，以右边的表为主，查询的数据包括右边表所有的数据以及左右表有交集的数据(right join)

SELECT * FROM user1 t1 RIGHT JOIN user1_copy t2 ON t1.`name`=t2.`name`;

 三、内连接查询，查询左右两个表有交集的数据(inner join)

SELECT * FROM user1 t1 INNER JOIN user1_copy t2 ON t1.`name`=t2.`name`;

四、全连接查询(union和union all)

(SELECT * FROM user1 t1) UNION (SELECT * FROM user1_copy t2); -- 注:union会对相同的结果进行去重
(SELECT * FROM user1 t1) UNION ALL (SELECT * FROM user1_copy t2); -- 注:union all则查询的是两边全部的数据，不会对数据进行去重

 

五、对应数据分享给大家测试用，对应SQL如下所示

USE `test`;

/*Table structure for table `country` */

DROP TABLE IF EXISTS `country`;

CREATE TABLE `country` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(200) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

/*Data for the table `country` */

insert  into `country`(`id`,`name`) values 
(1,'魏国'),
(2,'蜀国'),
(3,'吴国');

/*Table structure for table `user1` */

DROP TABLE IF EXISTS `user1`;

CREATE TABLE `user1` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `countryid` bigint(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;

/*Data for the table `user1` */

insert  into `user1`(`id`,`name`,`countryid`) values 
(1,'曹操',1),
(2,'刘备',2),
(3,'孙权',3),
(4,'司马懿',1),
(5,'诸葛亮',2),
(6,'周瑜',3),
(7,'张飞',2);

/*Table structure for table `user1_copy` */

DROP TABLE IF EXISTS `user1_copy`;

CREATE TABLE `user1_copy` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `countryid` bigint(10) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

/*Data for the table `user1_copy` */

insert  into `user1_copy`(`id`,`name`,`countryid`) values 
(1,'曹操',1),
(2,'刘备',2),
(3,'孙权',3),
(4,'司马懿',1),
(5,'诸葛亮',2),
(6,'周瑜',3),
(7,'关羽',2),
(8,'曹仁',1),
(9,'吕蒙',3);