oracle 基本语句练习(四) SQL1999 语法规范 cross join ,等值连接,不等值连接,自连接与外连接

1  笛卡尔乘积  cross join

     select  ename ,dname from emp cross join dept;

2  等值连接

   旧语法:

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

  新语法:

   where 语句中 用于描述 过滤条件 , 连接条件不再使用where 语句 .

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

 

 等值连接的简洁写法:

 select ename ,dname from emp join dept using (deptno);

3 不等值连接

  select ename,dname from emp e  join salgrade s on(e.sal between s.losal and s.hisal);

4三个表的连接

 select ename,dname,grade from

 emp e join dept d on (e.deptno = d.deptno)

 join salgrade s on(e.sal between s.losal and s.hisal)

 where ename not like '_A%' ;

5 自连接

 select  e1.ename ,e2.ename from emp e1 join emp e2 on (e1.mgr = e2.empno ) ; 

6 外连接:

左外连接

select  e1.ename ,e2.ename from emp e1 left  join emp e2 on (e1.mgr = e2.empno ) ; 

e1 中 未被连接的ename 项 king 也显示了出来 

   

右外连接:

select  e1.ename ,e2.ename from emp e1 right  join emp e2 on (e1.mgr = e2.empno ) ; 

全外连接:

select  e1.ename ,e2.ename from emp e1 full join emp e2 on (e1.mgr = e2.empno )