17,377
社区成员
发帖
与我相关
我的任务
分享
select * from A a left join B b on a.id=b.id
select * from A a , B b where a.id=b.id
select * from a,b where a.id=b.id
select * from a inner join b on a.id=b.id
select * from (a inner join b on a.id=b.id) inner join c on a.id=c.id
--给你个例子
--dept通过deptno左连接emp 显示dept表中的所有deptno,即使emp表中没有的deptno
SQL> select empno,ename,dept.dept
2 from dept left join emp
3 on emp.deptno=dept.deptno;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
EMPNO ENAME DEPTNO
---------- ---------- ----------
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30
40
50
60
70
--内连接 显示dept和emp同时存在的deptno
SQL> select empno,ename,dept.deptn
2 from emp,dept
3 where dept.deptno=emp.deptno
4 /
EMPNO ENAME DEPTNO
---------- ---------- ----------
7782 CLARK 10
7839 KING 10
7934 MILLER 10
7369 SMITH 20
7566 JONES 20
7788 SCOTT 20
7876 ADAMS 20
7902 FORD 20
7499 ALLEN 30
7521 WARD 30
7654 MARTIN 30
EMPNO ENAME DEPTNO
---------- ---------- ----------
7698 BLAKE 30
7844 TURNER 30
7900 JAMES 30