ORACLE 04. SELECT_ JOIN _비등가 조인/ 자체조인/카테시안곱

   1. 비등가 조인 (NON EQUAL JOIN)
    매칭시킬 컬럼에 대한 조건 작성시 '=' 등호 사용하지 않음 
    ANSI구문으로 JOIN ON 구문으로만 가능 

>>오라클 구문

SELECT EMP_NAME, SALARY,SAL_LEVEL
FROM EMPLOYEE,SAL_GRADE
--WHERE SALARY>= MIN_SAL AND SALARY<=MAX _SAL;'
WHERE SALARY BETWEEN MIN_SAL AND MAX_SAL;

>> ANSI구문

SELECT EMP_NAME,SALARY, SAL_LEVEL
FROM EMPLOYEE
JOIN SAL_GRADE ON (SALARY BETWEEN MIN_SAL AND MAX_SAL);

 

2.자체 조인 (SELF JOIN)
   같은 테이블을 여러번 조인하는 경우

>> 오라클 구문

SELECT E.EMP_ID"사원번호",E.EMP_NAME"사원명",E.DEPT_CODE"사원부서코드",
       M.EMP_ID"사수사번",M.EMP_NAME"사수명",M.DEPT_CODE"사수부서코드"
FROM EMPLOYEE E, EMPLOYEE M
WHERE E.MANAGER_ID = M.EMP_ID;

 

>> ANSI    

SELECT E.EMP_ID"사원번호",E.EMP_NAME"사원명",E.DEPT_CODE"사원부서코드",
       M.EMP_ID"사수사번",M.EMP_NAME"사수명",M.DEPT_CODE"사수부서코드"
FROM EMPLOYEE E
JOIN EMPLOYEE M ON E.MANAGER_ID = M.EMP_ID;

 

3.  카테시안곱 (CARTESIAN PRODUCT) / 교차조인 (CROSS JOIN)
    모든 테이블의 각 행들이 서로서로 매핑된 데이터 조회됨 (곱집합)
-> 방대한 데이터출력 -> 과부화 위험

 >> 오라클구문 

SELECT EMP_NAME,DEPT_TITLE
 FROM EMPLOYEE,DEPARTMENT;   -- 23*9 = 207행

>> ANSI

SELECT EMP_NAME, DEPT_TITLE
FROM EMPLOYEE
CROSS JOIN DEPARTMENT;