Oracl_day1(连接查询)

1.不等值连接查询:

--查询EMP表中用户的工资对应SALGRADE表中的工资级别
SELECT ENAME,SAL,GRADE 
FROM EMP E,SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

2.外连接:当条件不成立时,仍然希望在结果中包含不成立的记录

左外连接:where d.deptno = e.deptno当条件不成立的时候,等号左边的表的信息仍然被包含

                                                                                                          where d.deptno = e.deptno(+)

SELECT * FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
--上下两句结果一样
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO(+) = D.DEPTNO

右外连接:where d.deptno = e.deptno当条件不成立的时候,等号右边的表的信息仍然被包含

                                                                                                           where d.deptno(+) = e.deptno

SELECT * FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO
--上下两句结果一样
SELECT * FROM EMP E,DEPT D WHERE E.DEPTNO = D.DEPTNO(+)

3.自连接:

SELECT E.ENAME ||'的老板是'||b.ename
FROM EMP E,EMP B
WHERE E.MGR = B.EMPNO;