[ ORACLE ] 92, 99 코드를 활용한 세 개 이상의 테이블 JOIN

SELECT USER
FROM DUAL;


-- ● 세 개 이상의 테이블 조인(join)

-- 형식 1(SQL 1992 CODE)
-- 형식 2(SQL 1999 CODE)



-- 형식 1(SQL 1992 CODE)
SELECT A.컬럼명, B.컬럼명, C.컬럼명
FROM A,B,C
WHERE A.컬럼명1 = B.컬럼명1
AND B.컬럼명2 = C.컬럼명2;



-- 형식 2(SQL 1999 CODE)

SELECT A.컬럼명, B.컬럼명, C.컬럼명
FROM A JOIN B
ON A.컬럼명1 = B.컬럼명1
    JOIN C
    ON B.컬럼명2 = C.컬럼명2;
    
    
-- ● HR계정 소유의 테이블 또는 뷰 목록 조회
SELECT *
FROM TAB;
/*
COUNTRIES	            TABLE	
DEPARTMENTS	            TABLE	
EMPLOYEES	            TABLE	
EMP_DETAILS_VIEW	    VIEW	
JOBS	                TABLE	
JOB_HISTORY 	        TABLE	
LOCATIONS	            TABLE	
REGIONS	                TABLE	
*/


-- HR 소유의 JOBS, EMPLOYEES, DEPARTMENT 테이블을 대상으로 
-- 직원들의 FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME (JEED)
-- 항목을 조회한다.

--JOB_ID로 직업구하고 DEPARTMENT_ID 로 부서구하고


SELECT *
FROM JOBS;

SELECT *
FROM EMPLOYEES;


SELECT *
FROM DEPARTMENTS;


-- [형식1]
/*SELECT A.컬럼명, B.컬럼명, C.컬럼명
FROM A,B,C
WHERE A.컬럼명1 = B.컬럼명1
AND B.컬럼명2 = C.컬럼명2;*/

SELECT B.FIRST_NAME, B.LAST_NAME, A.JOB_TITLE, C.DEPARTMENT_NAME
FROM JOBS A, EMPLOYEES B, DEPARTMENTS C
WHERE B.JOB_ID = A.JOB_ID(+)
AND B.DEPARTMENT_ID = C.DEPARTMENT_ID(+);



-- [형식 2(SQL 1999 CODE)]

--SELECT A.컬럼명, B.컬럼명, C.컬럼명
--FROM A JOIN B
--ON A.컬럼명1 = B.컬럼명1
 --   JOIN C
 --   ON B.컬럼명2 = C.컬럼명2;
 
 
 
 SELECT B.FIRST_NAME, B.LAST_NAME, A.JOB_TITLE, C.DEPARTMENT_NAME
 FROM JOBS A RIGHT JOIN EMPLOYEES B
 ON B.JOB_ID = A.JOB_ID
     LEFT JOIN DEPARTMENTS C
     ON B.DEPARTMENT_ID = C.DEPARTMENT_ID;
 
 
 
 
 -- ●EMPLOYEES, DEPARTMENTS, JOBS, LOCATIONS, COUNTRIES, REGIONS 테이블을 대상으로
 -- 직원들의 데이터를 다음과 같이 조회한다.
 
 -- FIRST_NAME, LAST_NAME, JOB_TITLE, DEPARTMENT_NAME, CITY, COUNTRY_NAME, REGION_NAME
 --        E       E            J           D           C          C           R
 
 SELECT *
 FROM EMPLOYEES;
 
  SELECT *
 FROM DEPARTMENTS;
 
 
  SELECT *
 FROM JOBS;
 
   SELECT *
 FROM LOCATIONS;
 
  SELECT *
 FROM COUNTRIES;
 
  SELECT *
 FROM REGIONS;
 --[92코드]
 
 
 SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME,L.CITY, C.COUNTRY_NAME, R.REGION_NAME
 FROM EMPLOYEES E, JOBS J, DEPARTMENTS D, LOCATIONS L, COUNTRIES C, REGIONS R
 WHERE E.JOB_ID = J.JOB_ID(+)
 AND E.DEPARTMENT_ID = D.DEPARTMENT_ID(+)
 AND D.LOCATION_ID=L.LOCATION_ID(+)
 AND L.COUNTRY_ID = C.COUNTRY_ID(+)
 AND C.REGION_ID = R.REGION_ID(+);
 

 
 
 
 
 
-- [99코드]
 
 
SELECT E.FIRST_NAME, E.LAST_NAME, J.JOB_TITLE, D.DEPARTMENT_NAME,L.CITY, C.COUNTRY_NAME, R.REGION_NAME
 FROM EMPLOYEES E LEFT JOIN JOBS J 
 ON E.JOB_ID = J.JOB_ID
    LEFT JOIN DEPARTMENTS D
    ON E.DEPARTMENT_ID = D.DEPARTMENT_ID
        LEFT JOIN LOCATIONS L
        ON D.LOCATION_ID=L.LOCATION_ID
            LEFT JOIN COUNTRIES C
            ON L.COUNTRY_ID = C.COUNTRY_ID
                LEFT JOIN REGIONS R
                ON C.REGION_ID = R.REGION_ID;
 
 
	 --==>> 총 107행
     
/*
Sundita	    Kumar	    Sales Representative            	Sales	        Oxford	United Kingdom	Europe
Ellen	    Abel	    Sales Representative	            Sales	        Oxford	United Kingdom	Europe
Alyssa	    Hutton	    Sales Representative	            Sales	        Oxford	United Kingdom	Europe
Jonathon	Taylor	    Sales Representative	            Sales	        Oxford	United Kingdom	Europe
Jack	    Livingston	Sales Representative	            Sales	        Oxford	United Kingdom	Europe
Charles     	Johnson	    Sales Representative	            Sales	        Oxford	United Kingdom	Europe
Hermann 	Baer	    Public Relations Representative	Public Relations	Munich	Germany	Europe
Kimberely	Grant	    Sales Representative				(null)          (null)  (null)          (null)
*/