数据库内连接、外连接(左外,右外)、全连接、笛卡尔积
1.首先建立两张表
drop table if exists `u1`;
create table `u1`(
`id` int(4) not null auto_increment,
`name` varchar(20) not null,
primary key (`id`)
)engine = innodb character set = utf8;
insert into u1 values (1,'宋义进'),(2,'高振宁'),(3,'高天意');
drop table if exists `u2`;
create table `u2`(
`id` int(4) not null auto_increment,
`name` varchar(20) not null,
primary key (`id`)
)engine = innodb character set = utf8;
insert into u2 values (1,'宋义进'),(2,'高振宁'),(3,'姜承録');
1.左连接
select * from u1 left join u2 on u1.name=u2.name;
以左表为基础,基于name
字段和右表比较,左表的内容全显示,右表没有的显示为null,见下图
2.右连接
select * from u1 right join u2 on u1.name=u2.name;
右连接和左连接正好相反,见下图
3.内连接
select * from u1 inner join u2 on u1.name=u2.name;
两个表的交集,左表和右表都有的才显示出来,见下图
4.全连接
select * from u1 union (select * from u2);
两张表的并集,不重复,注意和union all 的对比。
select * from u1 union all (select * from u2);
5.笛卡尔积
select * from u1 cross join u2;