[Oracle] JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, (+)

WHERE 절 JOIN

SELECT t1.ename, t1.sal, T2.* FROM emp t1, salgrade T2 
WHERE t1.sal BETWEEN T2.losal AND T2.hisal;

 

WHERE 절 JOIN하기 옵션 (+) 방법 - 오라클만 가능

select d.dname, l.city 
from dept d, locations l
where d.loc_code = l.loc_code(+);

(+)가 없는 테이블 ROW가 전체 출력 됨,

DEPT  DNAME > INSA 정보까지 보기 위해,  locations 테이블에 (+) 함

table DEPT 

table locations

 LEFT JOIN, RIGHT JOIN 

SELECT D.DNAME, L.LOC_CODE 
FROM DEPT D LEFT JOIN LOCATIONS L
-- FROM DEPT D RIGHT JOIN LOCATIONS L
ON L.LOC_CODE = D.LOC_CODE;

FULL JOIN 두 테이블 칼럼 모두 조회 

SELECT D.DNAME, l.city
FROM DEPT D FULL JOIN LOCATIONS L
ON L.LOC_CODE = D.LOC_CODE;