SQL92语法
select ename,dname,grade from emp e,dept d, salgrade s
where e.deptno = d.deptno and e.sal between s.lowsal and s.hisal and //连接条件
job <> 'CLERK'; //过滤条件
cross join 是笛卡儿乘积 就是一张表的行数乘以另一张表的行数
left (outer) join 左外连接 第一张表的连接列在第二张表中没有匹配是,第二张表中的值返回null
right join 右外连接 第二张表的连接列在第一张表中没有匹配是,第一张表中的值返回null
full join 全外连接 返回两张表中的行 left join+right join
inner join 内连只返回两张表连接列的匹配项
cross join 笛卡儿乘积
sql92:
select ename,dname from emp,dept;
sql99: cross join 交叉连接--迪科尔承接
select ename,dnam e from emp cross join dept;
ENAME DNAME
SMITH ACCOUNTING
ALLEN ACCOUNTING
WARD ACCOUNTING
JONES ACCOUNTING
MARTIN ACCOUNTING
BLAKE ACCOUNTING
CLARK ACCOUNTING
SCOTT ACCOUNTING
KING ACCOUNTING
TURNER ACCOUNTING
ADAMS ACCOUNTING
JAMES ACCOUNTING
FORD ACCOUNTING
MILLER ACCOUNTING
SMITH RESEARCH
ALLEN RESEARCH
WARD RESEARCH
JONES RESEARCH
MARTIN RESEARCH
BLAKE RESEARCH
CLARK RESEARCH
SCOTT RESEARCH
KING RESEARCH
TURNER RESEARCH
等值连接
sql92:
select ename,dname from emp,dept where emp.deptno=dept.deptno;
sql99:
select ename,dname from emp join dept on (emp.deptno=dept.deptno);
也可以用using关键字简写(不推荐,这里有很多假设)
select ename,dname from emp join dept usring(deptno);
不等值连接
sql99:
select ename from emp e join salgrade s on(e.sal between s.losal and s.hisal);
三表连接
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%';
求人名字和他经理人名字
select e1.ename, e2.ename from emp e1
join emp e2 on (e1.mgr= e2.empno);
内连接 因为king没有上司mgrno所以不显示
ENAME ENAME
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
用左外连接
select e1.ename, e2.ename from emp e1
left join emp e2 on (e1.mgr= e2.empno);
ENAME ENAME
FORD JONES
SCOTT JONES
JAMES BLAKE
TURNER BLAKE
MARTIN BLAKE
WARD BLAKE
ALLEN BLAKE
MILLER CLARK
ADAMS SCOTT
CLARK KING
BLAKE KING
JONES KING
SMITH FORD
KING
select ename,dname from emp e
join dept d on(e.deptno = d.deptno);
d表中有个部门在e表中没人
ENAME DNAME
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
已选择14行。
select ename,dname from emp e
right join dept d on(e.deptno = d.deptno);
ENAME DNAME
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING
OPERATIONS
已选择15行。