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 ) ; 

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 )