[SQL]INNER JOIN과 OUTER JOIN, SELF JOIN, CROSS JOIN 에 대하여

INNER JOIN과 OUTER JOIN, SELF JOIN, CROSS JOIN 에 대하여

INNER JOIN

- Inner Join은 복수 테이블들이 조인 조건을 모두 만족하는 레코드들만 선별하는 조인이다. 

- 조인 조건에 만족하는 행만 검색

OUTER JOIN

- INNER JOIN 결과 + 조인 조건에 만족하지 않은 행도 포함

아래 예시는 아래와 같으나 manager_id가 null인 경우도 출력하여 주는 outer join의 예시이다.

select e.employee_id, e.last_name, e.manager_id, m.last_name
from employees e, employees m
where e.manager_id = m.employee_id(+)
order by e.employee_id asc;

<RIGHT OUTER JOIN>

다음의 두 예시는 같다

SELECT *
FROM EMP E RIGHT OUTER JOIN DEPT D
  ON D.DEPTNO = E.DEPTNO;
  
SELECT *
FROM EMP E, DEPT D
WHERE D.DEPTNO(+) = E.DEPTNO;

<LEFT OUTER JOIN>

다음의 두 예시는 같다

SELECT *
FROM EMP E LEFT OUTER JOIN DEPT D
  ON D.DEPTNO = E.DEPTNO;
  
SELECT *
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO(+);

<FULL OUTER JOIN>

RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과 값을 모두 보고 싶을 때 사용한다.

SELECT *
FROM EMPLOYEES E
FULL JOIN DEPARTMENTS D
  ON D.DEPARTMENT_ID = E.DEPARTMENT_ID;

다음 예시를 참고하자.

SELECT C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID, SUM(S.QUANTITY_SOLD)
FROM CUSTS C
LEFT OUTER JOIN SALES S
ON C.CUST_ID = S.CUST_ID
WHERE C.COUNTRY_ID = 52790 --요놈은 데이터가 있는 쪽에서 비교를  OUTER JOIN을 할때 ON있거나, WHERE에 있거나 상관이 없다.
GROUP BY C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID;

다음 예시를 분석해보자.

SELECT *
FROM (SELECT *
      FROM CUSTS
      WHERE COUNTRY_ID = 52790) C
LEFT OUTER JOIN 
     (SELECT *
      FROM SALES
      WHERE TIME_ID BETWEEN TO_DATE('19981001', 'YYYYMMDD')
                  AND TO_DATE('19981101', 'YYYYMMDD')-1/86400) S
    ON C.CUST_ID = S.CUST_ID;
 
 
SELECT C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID, 
       SUM(S.QUANTITY_SOLD) 
FROM CUSTS C LEFT OUTER JOIN SALES S 
  ON C.CUST_ID = S.CUST_ID 
WHERE C.COUNTRY_ID = 52790
  AND S.TIME_ID BETWEEN TO_DATE('19981001','YYYYMMDD')
                    AND TO_DATE('19981101','YYYYMMDD')-1/86400 
GROUP BY C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID  ; 
--이 경우 OUTER JOIN을 사용했으나 WHERE절의 조건이 추가되어, NULL값이 사라진다. 그러므로
--OUTER JOIN을 하기 위해서는 TIME_ID의 조건을 ON에 넣어야한다.
 
 
SELECT C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID, SUM(S.QUANTITY_SOLD) 
FROM CUSTS C LEFT OUTER JOIN SALES S 
  ON C.CUST_ID = S.CUST_ID 
  AND S.TIME_ID BETWEEN TO_DATE('19981001','YYYYMMDD')
                    AND TO_DATE('19981101','YYYYMMDD')-1/86400 
WHERE C.COUNTRY_ID = 52790
GROUP BY C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID  ; 
 
 
SELECT C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID, SUM(S.QUANTITY_SOLD) 
FROM CUSTS C LEFT OUTER JOIN SALES S 
  ON C.CUST_ID = S.CUST_ID 
  AND S.TIME_ID BETWEEN TO_DATE('19981001','YYYYMMDD')
                    AND TO_DATE('19981101','YYYYMMDD')-1/86400 
  AND C.COUNTRY_ID = 52790
GROUP BY C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID  ; 
 
 
SELECT C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID, SUM(S.QUANTITY_SOLD) 
FROM CUSTS C LEFT OUTER JOIN SALES S 
  ON C.CUST_ID = S.CUST_ID 
  WHERE( S.TIME_ID BETWEEN TO_DATE('19981001','YYYYMMDD')
                    AND TO_DATE('19981101','YYYYMMDD')-1/86400 
        OR S.TIME_ID IS NULL
        AND C.COUNTRY_ID = 52790)
GROUP BY C.CUST_ID, C.CUST_LAST_NAME, C.COUNTRY_ID  ; 
 
SELECT *
FROM (SELECT *
      FROM CUSTS 
      WHERE COUNTRY_ID = 52790) C 
LEFT OUTER JOIN 
     (SELECT *
      FROM SALES 
      WHERE TIME_ID BETWEEN TO_DATE('19981001','YYYYMMDD')
                        AND TO_DATE('19981101','YYYYMMDD')-1/86400) S 
   ON C.CUST_ID = S.CUST_ID ; 

아래 두 예시의 결과는 같다 그다음 밑의 예시와 무엇이 다른지 분석 해보자.

SELECT *
FROM DEPT D LEFT OUTER JOIN EMP E
  ON D.DEPTNO = E.DEPTNO
 AND E.SAL > 3000;
 
 
SELECT *
FROM DEPT D LEFT OUTER JOIN (SELECT *
                            FROM EMP
                            WHERE SAL > 3000) E
  ON D.DEPTNO = E.DEPTNO;
   
 
SELECT *
FROM DEPT D LEFT OUTER JOIN EMP E
  ON D.DEPTNO = E.DEPTNO
WHERE E.SAL > 3000 OR E.SAL IS NULL;

아래 내용은 ORACLE JOIN의 예이다.

SELECT *
 
FROM DEPT D
 
    ,EMP E
 
WHERE D.DEPTNO = E.DEPTNO(+);
 
-- (+)가 붙어있는건 데이터가 없는쪽, 즉 반대편이 데이터를 더 갖고 있다는 뜻.

OUTER JOIN을 할때는 조건을 ON절에 추가할 것인가, 아니면 WHERE절에 추가할 것인가를 잘 생각해야한다.

SELF JOIN

self join은 하나의 테이블이 두 개의 테이블인 것처럼 조인하는 것을 말한다.

다음 예시를 참고하자.

select e.employee_id, e.last_name, e.manager_id, m.last_name  
from employees e, employees m
where e.manager_id = m.employee_id
order by e.employee_id asc;

위의 예시는 사원의 상사사번과, 상사이름을 출력해주는 query문이다.

또한 값(manager_id)이 null인경우 값이 출력되지 않는다.(inner join)

CROSS JOIN

두 집합간에 만날 수 있는 모든 경우의 수를 볼수 있다.

다음의 두 예시는 같다.

SELECT *
FROM EMP E
     , DEPT D;
     
SELECT *
FROM EMP E
CROSS JOIN DEPT D;
--위의 방법 보다는 해당 ANSI JOIN을 사용해라.
--이 문장이 의도된 문장인지, 아닌지를 알기위해 이러한 방법을 사용한다.

다음 세개의 쿼리문은 결과가 같으나, 위에서부터 첫번째와 두번째만 같다고 할 수 있고, 세번짼 다른 것이다.

SELECT * 
FROM (SELECT *
      FROM EMPLOYEES 
      WHERE SALARY > 20000) E 
RIGHT OUTER JOIN DEPARTMENTS D 
  ON D.DEPARTMENT_ID = E.DEPARTMENT_ID ; 
  
SELECT * 
FROM EMPLOYEES E
RIGHT OUTER JOIN DEPARTMENTS D 
  ON D.DEPARTMENT_ID = E.DEPARTMENT_ID 
 AND E.SALARY   > 20000 ; 
  
SELECT * 
FROM EMPLOYEES E
RIGHT OUTER JOIN DEPARTMENTS D 
  ON D.DEPARTMENT_ID = E.DEPARTMENT_ID 
WHERE E.SALARY   > 20000 
   OR E.SALARY IS NULL ;

SQL을 배운지 얼마 되지 않아 잘못된 내용이 있을 수 있습니다. 틀린 내용이있다면, 댓글로 달아주세요.