SQL语言的三种连接查询测试

SQL语言的三种连接查询测试

 

SQL语言有三种连接查询，左连接（left join），右连接（right join）和内连接（inner join），现有t1,t2两张数据表，如下所示。

t1

id	a1	a2
1	x	y
2	xx	y

 

t2

id	b1	b2
1	x	y
2	x	yy

 

 

 

 

1.left join

运行如下SQL语句

select * from t1 left join t2 on t1.a1=t2.b1

会得到如下结果

表1

id	a1	a2	id	b1	b1
1	x	y	1	x	y
1	x	y	2	x	yy
2	xx	yy	Null	Null	Null

结果集中第一行第二行是t1和t2中满足连接条件的行数做笛卡尔积的结果(1×2)。

结果集中第三行是由于左表的第二行在右表t2中没有找到满足连接条件的行，所以在右表t2的所有字段用空值代替。

 

2.right join测试

我们将上面的SQL中的left改为right即

select * from t1 right join t2 on t1.a1=t2.b1

得到如下的结果

表2

id	a1	a2	id	b1	b1
1	x	y	1	x	y
1	x	y	2	x	yy

 

 

 

 

对比上面的结果集，少了第三行，因为现在做的是右连接，以右表为准，而右表的每行记录都在左表t1中找到满足连接条件的记录，故没有了第三行。

如果将SQL改为

select * from t1 right join t2 on t1.a2=t2.b2

得到如下结果

 

 

 

 

表3

id	a1	a2	id	b1	b1
1	x	y	1	x	y
2	xx	y	1	x	y
Null	Null	Null	2	x	yy

 

 

 

 

 

结果集中第一行第二行是t1和t2中满足连接条件的行数做笛卡尔积的结果(2×1)。第三行出现空值的原因与上面出现空值的原因是样的。

注意观察背景为灰色的列。

 

3.inner join

运行语句

select * from t1 inner join t2

注意：left join 和right join 必须有on连接条件，如果没有就是语法错误，但是inner join可以不要连接条件。

得到如下结果

表4

 

id	a1	a2	id	b1	b1
1	x	y	1	x	y
2	xx	y	1	x	y
1	x	y	2	x	yy
2	xx	y	2	x	yy

 

 

 

 

 

 

这个结果就是将t1和t2直接做笛卡尔积的结果（2×2）。

 

运行SQL语句

select * from t1 inner join t2 on t1.a1=t2.b1

得到如下结果

表5

id	a1	a2	id	b1	b1
1	x	y	1	x	y
1	x	y	2	x	yy

 

 

 

 

和上面的结果比较一下，其实这个结果就是将表4中不满足连接条件的行过滤掉了。

再将这个结果和表1相比较，表1比表5多出红颜色背景的那一行。

4.总结

使用left join时，结果集 是 两张表中满足连接条件的记录的笛卡尔积 与 左表的其他记录（不满足连接条件的记录）的 并集。

使用right join时，结果集 是 两张表中满足连接条件的记录的笛卡尔积 与 右表的其他记录（不满足连接条件的记录）的 并集。

使用inner join时，结果集 是 两张表中满足连接条件的记录的笛卡尔积。

 

（语言表达能力较差，不足之处见谅)

有兴趣可以用下面的sql创建数据表试一下

-- 表的结构 `t1`
--

CREATE TABLE `t1` (
  `id` int(11) unsigned NOT NULL auto_increment,
  `a1` varchar(10) NOT NULL,
  `a2` varchar(10) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- 导出表中的数据 `t1`
--

INSERT INTO `t1` VALUES (1, 'x', 'y');
INSERT INTO `t1` VALUES (2, 'xx', 'y');

-- --------------------------------------------------------

--
-- 表的结构 `t2`
--

CREATE TABLE `t2` (
  `id` int(11) NOT NULL auto_increment,
  `b1` varchar(10) NOT NULL,
  `b2` varchar(16) default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ;

--
-- 导出表中的数据 `t2`
--

INSERT INTO `t2` VALUES (1, 'x', 'y');
INSERT INTO `t2` VALUES (2, 'x', 'yy');