[sql]JOIN, ANSI JOIN

SQL의 JOIN은 두 개 이상의 테이블을 결합하여 관련된 데이터를 조회하는 방법이다. JOIN을 사용하면 서로 다른 테이블에 저장된 정보를 연결하여 보다 유용한 데이터를 생성할 수 있다. 

내부조인(INNER JOIN)

SELECT EMPLOYEE_ID 직원번호,
       DEPARTMENT_NAME 부서명
FROM EMPLOYEES, DEPARTMENTS ;

비교 조건이 없어서 모든 직원을 모든 부서와 연결하여 출력한다.

SELECT EMPLOYEE_ID 직원번호, 
       DEPARTMENT_NAME 부서명 
FROM EMPLOYEES, DEPARTMENTS
WHERE DEPARTMENT_ID=60;

 

⚠️ORA-00918: 열의 정의가 애매합니다 00918. 00000 -  "column ambiguously defined"

 ==>WHERE문에서 DEPARTMENT_ID가 어느 테이블에 있는 것을 말하는지 정의가 안되있기 때문에 에러가 났다.  이와 같이 두테이블에서 동일한 값을 가진 컬럼을 사용하고 싶을때 JOIN을 쓴다.

 

①등가 조인 (Equi Join,동등조인)

일반적으로 INNER JOIN의 일종으로 간주된다.등가 조인은 주로 두 테이블에서 동일한 값을 가진 컬럼을 기준으로 데이터를 결합한다. 이때 사용되는 비교 연산자는 =이다. 결과적으로, 두 테이블의 관련된 행만 포함된 결과 집합을 생성한다.

SELECT E.EMPLOYEE_ID 직원번호, 
       E.FIRST_NAME ||' '|| E.LAST_NAME 이름,
       D.DEPARTMENT_NAME 부서명 
FROM EMPLOYEES E, DEPARTMENTS D --alias(별칭)를 붙인다
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

②비등가 조인 (NON-EQUI JOIN)

동등조인과 동일하게 일반적으로 INNER JOIN의 일종으로 간주된다.비등가 조인은 주로 두 테이블에서 상이한 값을 가진 컬럼을 기준으로 데이터를 결합한다. 이때 사용되는 비교 연산자는  <, >, <=, >=, <> 등이다. 다음 예시에서 자세하게 알아보자.

SALGRADE 테이블 추가

CREATE TABLE SALGRADE
(
  GRADE VARCHAR2(3) PRIMARY KEY,
  LOSAL NUMBER(10),
  HISAL NUMBER(10)
);
INSERT INTO SALGRADE VALUES ('S',20001,9999999999); 
INSERT INTO SALGRADE VALUES ('A',15001,20000); 
INSERT INTO SALGRADE VALUES ('B',10001,15000); 
INSERT INTO SALGRADE VALUES ('C',5001,10000); 
INSERT INTO SALGRADE VALUES ('D',3001,5000); 
INSERT INTO SALGRADE VALUES ('E',0,3000);
COMMIT; --INSERT,CREATE,UPDATE에만 커밋

그다음 WHERE문에서 EMPLOYEES와 SALGRADE를 BETWEEN으로 연결한다.

  SELECT    E.EMPLOYEE_ID                        직원번호,
            E.FIRST_NAME || ' ' || E.LAST_NAME   직원명,
            E.SALARY                             월급,
            SG.GRADE                             등급
   FROM     EMPLOYEES  E,  SALGRADE SG
   WHERE    E.SALARY  BETWEEN  SG.LOSAL  AND  SG.HISAL;  --107

③셀프 조인(SELF JOIN)

셀프 조인(Self Join)은 동일한 테이블을 두 번 연결하여 데이터를 조인하는 방식.

즉, 한 테이블을 두 개의 별칭으로 사용하여 서로를 참조하는 형태이다. 셀프 조인은 주로 같은 테이블 내의 여러 행 간의 관계를 분석할 때 유용하다.

SELECT E2.FIRST_NAME||' '||E2.LAST_NAME 직원이름,
       E1.FIRST_NAME||' '||E1.LAST_NAME 상사이름
   FROM EMPLOYEES E1, EMPLOYEES E2
   WHERE E1.EMPLOYEE_ID=E2.MANAGER_ID
   ;

결과

---

기본정보

우리회사 부서수
COUNT(DEPARTMENT_ID) => 27

우리회사 직원수
 COUNT(EMPLOYEE_ID) => 109

직원이 근무하는 부서 수 - DEPARTMENT_ID가 NULL이 아닌 중복제거한 부서번호수
COUNT(DISTINCT DEPARTMENT_ID) => 11

---

외부 조인(OUTER JOIN)

외부 조인(Outer Join)은 SQL에서 두 개 이상의 테이블을 결합할 때, 한 테이블의 모든 데이터를 포함하고, 다른 테이블에서 일치하지 않는 경우에는 NULL 값을 반환하는 조인 방식이다.이때, 기준은 NULL이 없는 쪽이다. 그러나 (+)를 사용하는 방식은 ORACLE에서만 사용가능하므로 아래에 나오는 ANSI JOIN을 사용하는 것이 좋다.

SELECT D.DEPARTMENT_NAME 부서명,
       E.FIRST_NAME ||' '|| E.LAST_NAME 이름
FROM DEPARTMENTS D, EMPLOYEES E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_NAME ASC; --106행 출력

↑일반조인(내부조인):두테이블에서 교집합 부분들만을 출력한다.

WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID

📍WHERE문을 위와같이 변경했을때 LEFT OUTER JOIN이다. DEPARTMENTS테이블은 모든 데이터를 포함하고 직원이 없는 부서는 NULL값을 반환한다. (+)가 없는 D.DEPARTMENT_ID가 OUTER되고 이때 DEPARTMENTS테이블이 FROM문에서 왼쪽에 있기 때문에 LEFT OUTER이다.

SELECT D.DEPARTMENT_NAME 부서명,
       E.FIRST_NAME ||' '|| E.LAST_NAME 이름
FROM EMPLOYEES E, DEPARTMENTS D 
WHERE E.DEPARTMENT_ID(+) = D.DEPARTMENT_ID
ORDER BY D.DEPARTMENT_NAME ASC; --122행 출력

📍 RIGHT OUTER JOIN => (+)없는 값의 테이블이 오른쪽에 있기 때문에 RIGHT OUTER JOIN.

📍OLD문법에는 FULL OUTER JOIN 문법이 없다.
   ∴만들어서 쓴다.

---

ANSI JOIN(신(新)문법)

테이블들을 JOIN으로 연결하고 조건문 ON이 구(舊) SQL의 WHERE을 대체하여 쓰인다. (모든 관계형 DB가 사용)

CROSS JOIN

두 테이블의 모든 조합을 반환한다. 실험실 레벨이며 실무에선 거의 사용하지 않는다.

SELECT D.DEPARTMENT_NAME, E.FIRST_NAME
FROM DEPARTMENTS D CROSS JOIN EMPLOYEES E; --2943

 

INNER JOIN

두 테이블에서 일치하는 행만 반환한다. 즉, 조건에 맞는 데이터가 있는 경우에만 결과에 포함된다. =>두 테이블의 교집합

SELECT D.DEPARTMENT_NAME, E.FIRST_NAME
FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID; --106

LEFT (OUTER) JOIN

왼쪽 테이블의 모든 행을 반환하고, 오른쪽 테이블과 일치하는 행이 없으면 NULL로 표시한다.

SELECT D.DEPARTMENT_NAME, E.FIRST_NAME
FROM DEPARTMENTS D LEFT JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID; --122 (106+27-11)

📍모든 부서를 기준으로 직원이 있으면 직원명 출력, 직원이 없는 부서는 NULL을 출력, 부서가 없는 직원은 출력X

RIGHT (OUTER) JOIN

오른쪽 테이블의 모든 행을 반환하고, 왼쪽 테이블과 일치하는 행이 없으면 NULL로 표시한다.

SELECT D.DEPARTMENT_NAME, E.FIRST_NAME
FROM DEPARTMENTS D RIGHT JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID; --109 (106+3)

📍 모든 직원을 기준으로 부서가 있으면 부서명 출력, 부서가 없는 직원은 NULL을 출력, 직원이 없는 부서는 출력X

FULL (OUTER) JOIN

두 테이블의 모든 행을 반환한다. 일치하지 않는 경우에는 NULL로 표시한다. => 두 테이블의 합집합

SELECT D.DEPARTMENT_NAME, E.FIRST_NAME
FROM DEPARTMENTS D FULL JOIN EMPLOYEES E
ON D.DEPARTMENT_ID=E.DEPARTMENT_ID; --125 (109(모든 직원)+27(모든부서)-11)

---

응용

🐲서로 다른 세 테이블을 JOIN하는 경우

SELECT E.FIRST_NAME ||' '|| E.LAST_NAME 직원명,
       D.DEPARTMENT_NAME 부서명,
       L.CITY||'.'||L.STATE_PROVINCE||' '||L.STREET_ADDRESS 부서위치
FROM  DEPARTMENTS D,LOCATIONS L,EMPLOYEES E
WHERE D.LOCATION_ID=L.LOCATION_ID
AND D.DEPARTMENT_ID= E.DEPARTMENT_ID
;

일반조인

FROM  EMPLOYEES E JOIN DEPARTMENTS D ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
                  JOIN LOCATIONS L   ON D.LOCATION_ID = L.LOCATION_ID
;

 EMPLOYEES와 DEPARTMENTS를 JOIN하고 조건을 단 JOIN문에 LOCATIONS를 JOIN함.

---

🐲부서명별 월급 평균

SELECT D.DEPARTMENT_NAME 부서명,
       ROUND(AVG(E.SALARY),0) 월급평균      
FROM   DEPARTMENTS D, EMPLOYEES E
WHERE D.DEPARTMENT_ID(+) = E.DEPARTMENT_ID
GROUP BY D.DEPARTMENT_NAME --ALIAS사용 불가능
ORDER BY 부서명 --ALIAS사용 가능
; --12

외부조인

 SELECT        D.DEPARTMENT_NAME         부서명, 
               ROUND(AVG(E.SALARY),3)    월급평균 
   FROM        DEPARTMENTS   D    JOIN    EMPLOYEES     E   
             ON   D.DEPARTMENT_ID  =  E.DEPARTMENT_ID 
   GROUP BY    D.DEPARTMENT_NAME    
   ORDER BY    부서명 ASC;       -- 11

INNER JOIN

SELECT       D.DEPARTMENT_NAME               부서명, 
               ROUND(AVG(E.SALARY),3)          월급평균 
   FROM        DEPARTMENTS   D  RIGHT JOIN  EMPLOYEES   E
     ON        D.DEPARTMENT_ID  =  E.DEPARTMENT_ID 
   GROUP BY    D.DEPARTMENT_NAME    
   ORDER BY    1 ASC, 2;              
   -- 12

RIGHT OUTER JOIN  

  SELECT       D.DEPARTMENT_NAME               부서명, 
               ROUND(AVG(E.SALARY),3)          월급평균 
   FROM        DEPARTMENTS   D  LEFT  JOIN  EMPLOYEES   E
     ON        D.DEPARTMENT_ID  =  E.DEPARTMENT_ID 
   GROUP BY    D.DEPARTMENT_NAME    
   ORDER BY    1 ASC, 2;        -- 27

LEFT OUTER JOIN

  SELECT       D.DEPARTMENT_NAME               부서명, 
               ROUND(AVG(E.SALARY),3)          월급평균 
   FROM        DEPARTMENTS   D  FULL JOIN  EMPLOYEES   E
     ON        D.DEPARTMENT_ID  =  E.DEPARTMENT_ID 
   GROUP BY    D.DEPARTMENT_NAME    
   ORDER BY    1 ASC, 2;         -- 28

FULL OUTER JOIN