简单连接
实现多个表的简单连接时,如果仅仅通过SELECT子句和FROM子句连
接多个表,那么查询的结果将是一个通过笛卡尔积所生成的表。
所谓笛卡尔积所生成的表,就是一个基本表中每一行与另一个基本表的
每一行连接在一起所生成的表,查询结果的行数是两个基本表的行数的积。
使用等号
在笛卡尔积所生成的表中包含了大量的冗余信息。在检索数据时,为了
避免冗余信息的出现,可以使用WHERE子句限定检索条件。在WHERE子
句中使用等号(=)可以实现表的简单连接,表示第一个表中的列与第二个
表中相应列匹配后才会在结果集中显示。
使用表的别名
在多表查询时,如果多个表之间存在同名的列,则必须使用表名进行限
定。例如使用scott.emp.deptno和scott.dept.deptno的方式,分别表示
scott.emp表中的deptno列和scott.dept表中的deptno列。如果查询内容比较
复杂,那么多次使用表名就会使语句变得繁琐,这时可以使用表别名的方
式解决这个问题。
设置表的别名,只需要在FROM子句中引用该表时,将表别名跟在表的
实际名称后面即可。表别名和表的实际名称之间使用空格进行分隔。
举例子:
select a.*,b.* from emp a,dept b where a.deptno = b.deptno;
我们可以这样查看:
首先查看scott.emp表,select * from emp;共14条记录,其中deptno为10,20,30
再查看scott.dept表,select * from dept;共4条记录,其中deptno为10,20,30,40
再查看,select a.*,b.* from emp a,dept b;共56条记录,想想为什么是56条?
原因:emp14记录 乘以 dept4条记录,这就是emp表和dept表的笛卡尔积所生成的表
然后,select a.*,b.* from emp a,dept b where a.deptno = b.deptno;共14条记录,
明白了什么是笛卡尔积,什么是简单连接吧?就是简单连接=笛卡尔积+查询条件
交叉连接
使用CROSS JOIN实现交叉连接
使用CROSS JOIN关键字,可以实现两个表的交叉连接,所得到的结果
将是这两个表中各行数据的所有组合,即这两个表所有数据行的笛卡尔积。
交叉连接与简单连接操作非常相似,不同的是,使用交叉连接时,在
FROM子句中多个表名之间不是用逗号,而是使用CROSS JOIN关键字隔
开。另外,在交叉连接中不需要使用关键字ON限定连接条件,但是可以添
加WHERE子句设置连接条件。
举例子:
select a.*,b.* from emp a cross join dept b where a.deptno = b.deptno;
我们可以这样查看:
首先查看,select a.*,b.* from emp a cross join dept b;共56条记录,
结果跟
select a.*,b.* from emp a,dept b;
是完全一样的。
再查看,select a.*,b.* from emp a cross join dept b where a.deptno = b.deptno;共14条记录
结果跟
select a.*,b.* from emp a,dept b where a.deptno = b.deptno;
是完全一样的
内连接
使用关键字JOIN进行连接
在连接查询的FROM子句中,多个表之间可以使用英文逗号进行分隔。
除了这种形式以外,SQL还支持使用关键字JOIN进行连接。
在FROM子句中,使用JOIN连接的语法形式如下:
FROM join_table1 join_type join_table2 [ ON ( join_condition ) ]
[ join_type … ON join_condition , … ]
语法说明如下:
join_table1、join_table2 参与连接操作的表名。
join_type 连接类型,连接类型有INNER JOIN(内连接)、OUTER
JOIN(外连接)和CROSS JOIN(交叉连接)。
join_condition 连接条件,由被连接表中的列和比较运算符、逻辑运算
符等构成。可以使用多组join_type … ON join_condition …子句,实现多
个表的连接。其中,cross join不需要on连接条件。
内连接是最常用的连接查询方式,使用INNER JOIN关键字进行指定。
其中INNER关键字可以省略,如果只使用JOIN关键字,默认表示内连接。
内连接使用比较运算符,在连接表的某(些)列之间进行比较操作,并
列出表中与连接条件相匹配的数据行。
根据使用的比较方式不同,内连接又分为等值连接、自然连接和不等连
接。
自然连接
在两个表中寻找列名和数据类型都相同的字段,通过相同的字段将两个表连接在一起
在连接条件中使用等于(=)运算符比较被连接列的列值,但它使用选
择列表指出查询结果集合中所包括的列,并删除连接表中的重复列。
SELECT e.empno , e.ename , e.sal , deptno , d.dname
from scott.emp e NATURAL JOIN scott.dept d
WHERE d.dname = 'ACCOUNTING';
使用自然连接时,需要注意以下几点:
如果自然连接的两个表中有多个字段都满足名称和数据类型相同,那么它们都会被作为自然连接的条件。
如果自然连接的两个表中,仅仅是字段名称相同,而字段的数据类型不同,那么使用该字段进行连接将会返回一个错误。
由于Oracle支持自然连接,那么在设计表时,应该尽量在不同的表中,
将具有相同含义的字段使用相同的名字和数据类型。如果总是对主键和外
键使用相同的名字,那么就可以满足自然连接。
我们可以这样查看:
首先查看,
SELECT e.empno , e.ename , e.sal , deptno , d.dname from scott.emp e NATURAL JOIN scott.dept d;
共14条记录,跟
SELECT e.empno , e.ename , e.sal ,e.deptno , d.dname from scott.emp e , scott.dept d where e.deptno = d.deptno;
查询出来的结果集是一样的。
注意SELECT e.* , d.* from scott.emp e NATURAL JOIN scott.dept d;这样写会报错的。
等值连接
在连接条件中使用等于号(=)运算符比较被连接列的列值,其查询结
果中列出被连接表中的所有列,包括其中的重复列
SELECT empno , ename , sal , d.deptno , dname
FROM scott.emp e [INNER] JOIN scott.dept d
ON e.deptno = d.deptno
WHERE dname = 'ACCOUNTING';
我们可以这样查看:
SELECT empno , ename , sal , d.deptno , dname FROM scott.emp e INNER JOIN scott.dept d;
报错,加上on连接条件后,查询中带有join关键字的查询,都应该有on的条件,cross join不需要on连接条件
SELECT empno , ename , sal , d.deptno , dname FROM scott.emp e INNER JOIN scott.dept d ON e.deptno = d.deptno;
查询出来共14条记录,跟
SELECT e.empno , e.ename , e.sal ,e.deptno , d.dname from scott.emp e , scott.dept d where e.deptno = d.deptno;
结果是完全一样的
不等值连接
不等连接: 在连接条件使用除等于运算符以外的其它比较运算符比较被连接的
列的列值。这些运算符包括>、>=、<=、<、!>、!<和<>。
SELECT empno , ename , sal , grade
FROM scott.emp e [INNER] JOIN scott.salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
外连接
使用内连接进行多表查询时,返回的查询结果集中仅包含符合查询条件
和连接条件的行。内连接消除了与另一个表中的任何行不匹配的行,而外
连接扩展了内连接的结果集,除了返回所有匹配的行外,还会返回一部分
或全部不匹配的行,这就取决于外连接的类型。
对于外连接,Oracle中可以使用加号(+)来表示,也可以使用LEFT、
RIGHT和FULL OUTER JOIN关键字。
外连接可以分为下面这3类:
左外连接(LEFT OUTER JOIN或LEFT JOIN)
右外连接(RIGHT OUTER JOIN或RIGHT JOIN)
全外连接(FULL OUTER JOIN或FULL JOIN)
使用外连接,列出与连接条件相匹配的行,并且列出左表(左外连接
时)、右表(右外连接时)或两个表(全外连接时)中,所有符合检索条
件的数据行。
在左外连接和右外连接时都会以一张表为基表,该表的内容会全部显示,然后加上两张表匹配的内容。
如果基表的数据在另一张表没有记录。 那么在相关联的结果集行中列显示为空值(NULL)。
左外连接
左外连接是在检索结果中除了显示满足连接条件的行外,还显示JOIN关
键字左侧表中所有满足检索条件的行。
这句话是什么意思呢?我们看看下面的结果就明白了:
SELECT e.empno , e.ename , e.sal ,e.deptno, s.grade
FROM scott.emp e LEFT OUTER JOIN scott.salgrade s
ON e.deptno = 10;
如图:
由查询出来的结果可知,当满足连接条件:ON e.deptno = 10;时,salgrade的grade列都有值,很正常,可是不满足连接条件时,emp的列还是查询出来,只是salgrade的grade列值null。
这就是左外连接的特点:左表所有结果 + 右表结果(满足连接条件则有值,不满足则为null)
想想,如果实际应用中这样查询,是不是很浪费资源啊,很多我们不需要的记录都查出来了,
所以我们需要优化连接条件(on ...)和 查询条件(where ...) 。
上面是为了说明左外连接的特点,才这样写的,实际中更可能是这样的需求:
查看每个员工工资的等级:
SELECT e.empno , e.ename , e.sal , s.grade
FROM scott.emp e LEFT OUTER JOIN scott.salgrade s
ON e.sal BETWEEN s.losal AND s.hisal;
Emp表中总共14个员工,上面查询也是查询出14条记录,这样就没有冗余的信息咯,当然我们也可以加上where查询条件做进一步的限制。
再体会下左外连接的特点吧:
SELECT e.last_name, e.deptno, d.dame
FROM emp e
LEFT OUTER JOIN dept d
ON (e.deptno = d.deptno);
等价于
SELECT e.last_name, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno(+);
结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录。
总结:Left join是以左表的记录为基础的,示例中emp可以看成左表,dept可以看成右表,它的结果集是emp表中的数据,再加上emp表和dept表匹配的数据。换句话说,左表(emp)的记录将会全部表示出来,而右表(dept)只会显示符合搜索条件的记录,dept表记录不足的地方均为NULL。
右外连接
右外连接是在结果中除了显示满足连接条件的行外,还显示JOIN右侧表
中所有满足检索条件的行。
SELECT DISTINCT e.deptno , d.deptno
FROM scott.emp e RIGHT OUTER JOIN scott.dept d
ON e.deptno = d.deptno;
等价于
select DISTINCT e.deptno , d.deptno
FROM scott.emp e , scott.dept d
WHERE e.deptno(+) = d.deptno;
查询结果如图所示:
查看结果,跟left join是不是刚好反过来了。
再体会一下吧:
SELECT e.last_name, e.deptno d.dname
FROM empe
RIGHT OUTER JOIN deptd
ON (e.deptno = d.deptno);
等价于
SELECT e.ename, e.deptno d.dname
FROM empe, dept d
WHERE e.deptno(+)=d.deptno;
结果为:所有员工及对应部门的记录,包括没有任何员工的部门记录。
全外连接
全外连接是在结果中除了显示满足连接条件的行外,还显示JOIN两侧表
中所有满足检索条件的行。(就是既满足左外连接又满足右外连接)
SELECT DISTINCT e.deptno , d.deptno
FROM scott.emp e FULL OUTER JOIN scott.dept d
ON e.deptno = d.deptno;
SELECT e.last_name, e.department_id, d.department_name
FROM employees e
FULL OUTER JOIN departments d
ON (e.department_id = d.department_id);
结果为:所有员工及对应部门的记录,包括没有对应部门编号department_id的员工记录和没有任何员工的部门记录。
左表和右表都不做限制,所有的记录都显示,两表不足的地方用null?填充。?
全外连接不支持(+)这种写法。
最后补充一个知识点:使用USING关键字
SQL/92标准可以使用USING关键字来简化连接查询,但是只有在查询
满足下面两个条件时,才能使用USING关键字进行简化:
查询必须是等值连接。
等值连接中的列必须具有相同的名称和数据类型。如下:
select empno , ename , sal , deptno , dname
from scott.emp e INNER JOIN scott.dept d
USING (deptno);
如果对多个表进行检索,就必须多次使用USING关键字进行指定,形式如下:
SELECT … FROM table1
INNER JOIN table2 USING ( column1 )
INNER JOIN table3 USING ( column2 );
上述语句相当于下面的语句:
SELECT … FROM table1 , table2 , table3
WHERE table1.column1 = table2.column1
AND table2.column2 = table3.column2 ;