15 SQL语言——SQL99联合查询

SQL99（SQL1999）多表连接查询

普通方式

我们使用普通方式查询，格式如：

select 列名 
from 表1，表2
[where] 表1.连接列=表2.连接列（通常是主键）

　　其中，列出两个表中都有的列（连接列）时，要使用表名.列名的方式否则会报错。

当我们不使用where时，得到的结果是一个笛卡尔积，如：

select ename,dept.deptno,dname from emp,dept;

　　

结果：

 

 我们看到员工命名不是其它部门的，却重复的列出了该员工在其他部门的记录，这是没有必要的。

所以我们使用：where 表1.表2的主键 = 表2.表2的主键 的方式消除冗余记录。这个例子中是：where emp.deptno = dept.deptno;效果如下：

 

 

join方式

交叉连接

交叉连接语法示例

select ename,emp.deptno,dname 
from emp 
cross join dept;

　　

它与普通连接且没有where过滤的效果一致，都是两个表关于连接列的笛卡尔积。

自然连接

需要两个表有同名字段（相同的列），根据所有的同名字段进行等值连接（所有同名字段的值相等）。

自然连接语法示例

select ename,deptno,dname 
from emp 
natural join dept; 

它的效果与普通连接跟上 'where 表1.表2的主键 = 表2.表2的主键' 的效果一致

Using连接

• using(xx)中可以接多个列，它是一种等值连接，可以转换为 join ... on (x=y and xx=y)；
• using(xx)中的列不可以接表名或别名等限定语，默认就是指共同列，不能指定表名；
• 在使用using的语句中，select * 可以使用，* 被扩展的顺序为using中的列，左表中的其它列，右表中的其它列
• 如果表有别名t，select t.* from table1 t , table2 using(xx)，t.* 不可以使用，using中的列不能加限定词
• left join, right join, inner join, full outer join 可以和using一起使用，它就是简单的等值连接
• 以上5项转自：https://www.cnblogs.com/hyang0/p/10597934.html

用在什么时候呢?答：用在不希望参照两个表的所有同名列进行等值连接时（自然连接就是这么做的）。

语法：

select 列名1，...
from 表名1
join 表名2
using(同名列1，同名列2);

　　

On子句

• 自然连接的条件是基于表中所有同列名的等值连接
• 为了设置任意的连接条件或者指定连接的列，需要使用on子句
• 连接条件与其它的查询条件分开书写
• 使用on子句使查询语句更容易理解

select ename,dname from emp join dept on emp.deptno=dept.deptno where emp.deptno=30;
select ename,dname from emp join dept on (emp.deptno=dept.deptno and emp.deptno=30);

　　上面两句结果一致。

SQL92

外连接

外连接省略了outer关键字，写不写都成。

左外连接:select 内容 from 表名 left outer join 表名 on 筛选条件

• ---查询员工姓名,工作,薪资,部门名称以及没有部门的员工信息
• select * from emp e left outer join dept d on d.deptno=e.deptno;

右外连接:select 内容 from 表名 right outer join 表名 on 筛选条件

• ---查询员工姓名,工作,薪资,部门名称以及没有员工的部门
• select * from emp e right outer join dept d on d.deptno=e.deptno;

全外连接:select 内容 from 表名 fill outer join 表名 on 筛选条件

• select * from emp e full outer join dept d on d.deptno=e.deptno;

自连接

查询员工姓名,工作,薪资,以及上级领导姓名

• select e1.*,e2.ename from emp e1 inner join emp e2 on e1.mgr=e2.empno