/*- 04_join.sql 0*/

/*
 * # join
 * - 하나 이상의 테이블에서 한번의 질의문으로 원하는 자료를 검색할 때 사용.
 */

/*
 * # equl join
 * - 가장 많이 사용되는 조인 방식으로 조인 대상이 되는 두 테이블에서 
 *   공통으로 존재하는 컬럼의 값이 일치되는 행을 연결해서 결과를 생성.
 */

-- EMP & DEPT 
-- 사원 정보 출력시에 각 사원들이 소속된 부서의 상세 정보 확인.
SELECT * FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO WHERE ENAME = 'JAMES';
-- 위 결과에서 특정 컬럼만 확인
SELECT ENAME,DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO;

-- 이름이 james 인 사람의 부서명 확인
SELECT ENAME,DNAME FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME = 'JAMES';

-- 두 개의 테이블에서 동일하게 존재하는 컬럼을 확인할 때에는, 컬럼명 앞에 테이블 명을 작성.
SELECT ENAME,DNAME, EMP.DEPTNO FROM EMP, DEPT WHERE EMP.DEPTNO=DEPT.DEPTNO AND ENAME = 'JAMES';

-- 테이블에 별칭 지정
-- FROM 절 뒤에 테이블 이름을 명시하고, 공백을 작성한 다음 별칭을 지정.
SELECT E.ENAME,D.DNAME, E.DEPTNO FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO AND E.ENAME = 'JAMES';

/*
 * # Non-Equi join
 * - 조인 조건에 특정 범위 내에 있는지를 비교연산자를 사용해서 JOIN
 */

-- SALGRADE : 급여 등급 레이블
-- DESC SALGRADE;
-- 이름    널? 유형     
-- ----- -- ------ 
-- GRADE    NUMBER 
-- LOSAL    NUMBER 
-- HISAL    NUMBER 

-- 각 사원의 급여 등급 확인
SELECT ENAME, SAL, GRADE
FROM EMP,SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

-- 사원 이름과 소속 부서, 급여의 등급 확인
SELECT E.ENAME, D.DNAME, E.SAL, S.GRADE
FROM EMP E,DEPT D,SALGRADE S
WHERE E.DEPTNO=D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL;

/*
 * # SELFJOIN
 * - 하나의 테이블에서 조인해서 원하는 결과값을 출력.
 */
 
 -- 사원의 담당 MANAGER 확인
SELECT EMPLOYEE.ENAME || '의 MANAGER 는 ' || MANAGER.ENAME || ' 입니다.'
FROM EMP EMPLOYEE, EMP MANAGER
WHERE EMPLOYEE.MGR = MANAGER.EMPNO;
 
 /*
  * # OUTERJOIN
  * - 조인 조건에 만족하지 않는 행도 나타내는 조인.
  * - 조인될 때, 어느 한쪽의 테이블에는 해당하는 데이터가 있지만,
  *   다른 쪽 테이블에는 데이터가 없을 경우 그 데이터가 출력되지 않는 것을 해결.
  * - '+' 기호를 조인 조건에서 정보가 부족한 컬럼 이름 뒤에 붙임.
  */

SELECT EMPLOYEE.ENAME || '의 MANAGER 는 ' || MANAGER.ENAME || ' 입니다.'
FROM EMP EMPLOYEE, EMP MANAGER
WHERE EMPLOYEE.MGR = MANAGER.EMPNO(+);

/* quiz */
-- 'NEW YORK' 에서 근무하는 사원의 이름과 급여를 출력하세요
SELECT E.ENAME, E.SAL 
FROM EMP E,DEPT D 
WHERE E.DEPTNO=D.DEPTNO 
AND D.LOC='NEW YORK';

SELECT E.ENAME, E.SAL 
FROM EMP E 
INNER JOIN DEPT D 
ON E.DEPTNO=D.DEPTNO 
WHERE D.LOC='NEW YORK';

-- SMITH 사원과 동일한 근무지에서 근무하는 사원의 이름을 출력하세요
SELECT E.ENAME 
FROM EMP E,DEPT D 
WHERE E.DEPTNO=D.DEPTNO 
AND D.LOC='DALLAS' 
AND ENAME NOT LIKE '%SMITH%';

SELECT E.ENAME 
FROM EMP E 
INNER JOIN DEPT D ON E.DEPTNO=D.DEPTNO 
WHERE D.LOC='DALLAS' 
AND ENAME NOT LIKE '%SMITH%';

SELECT EA.ENAME, EB.ENAME
FROM EMP EA, EMP EB
WHERE EA.DEPTNO=EB.DEPTNO
AND EA.ENAME='SMITH';
--AND EA.ENAME!=EB.ENAME;

SELECT EA.ENAME, EB.ENAME 
FROM EMP EA 
INNER JOIN EMP EB ON EA.DEPTNO=EB.DEPTNO 
WHERE EA.ENAME='SMITH' 
AND EA.ENAME NOT LIKE EB.ENAME;