[SQL] 조인

여러 테이블을 하나의 테이블처럼 사용하는 조인

관계형 데이터베이스에는 여러 종류의 데이터를 다양한 테이블에 나누어 저장하는 특성이 있어서 응용 프로그램이나 업무에 사용하는 SQL구문은 대부분 단일 테이블이 아닌 여러테이블의 데이터를 조합하여 출력할 때가 흔함. 이것이 가능한 조회 방식이 조인.

 

집합 연산자와 조인의 차이점

조인(join)은 2개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식

집합 연산자와의 차이점은 집합 연산자를 사용한 결과는 두 개 이상 SELECT문의 결괏값을 세로로 연결한 것이고, 

조인을 사용한 결과는 두 개 이상의 테이블 데이터를 가로로 연결한 것.

 

여러 테이블을 사용할 때의 FROM절

FROM절에는 테이블을 여러 개 지정할 수 있는데, 꼭 테이블이 아니더라도 테이블 형태, 즉 열과 행으로 구성된 데이터 집합이면 모두 FROM절에 지정 할 수 있음. 뷰(view)와 서브쿼리(subquery)등이 이에 해당됨.

-- 기본 형식
SELECT 열1, 열2, ..., 열n
FROM   EMP(테이블 명)
WHERE  조건식
GROUP BY 그룹식
HAVING 그룹 조선식
ORDER BY 정렬식

-- 기본 형식
SELECT 
    FROM 테이블1, 테이블2,...테이블n

-- FROM절에 여러 테이블 지정하기
SELECT *
  FROM EMP, DEPT
ORDER BY EMPNO;
-- 각 집합을 이루는 모든 원소의 순서쌍을 데카르트 곱(카테시안 곱, Cartesian product)이라고하며 
-- 크로스 조인(cross join)또는 교차 조인이라고 함.

 

**조인 조건이 없을 때의 문제점.

조인으로 데이터를 출력할 때는 조인 대상 테이블이 많을수록 조합 데이터 중 정확한 데이터만 뽑아낼 수 있도록 해야하는데, 이때 출력할 행을 선정하는 조건시글 명시하는 WHERE절이 중요하며, 서로 다른 테이블의 같음 이름의 열을 구별하는 방법이 필요한데, 열 앞에 테이블 이름을 지정하여 특정 열이 어느 테이블에 속하는지를 구별 해야함.

-- 열 이름을 비교하는 조건식으로 조인하기
SELECT *
    FROM EMP, DEPT
  WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

---

테이블 별칭 설정

-- 테이블 이름을 별칭으로 표현하기
SELECT *
    FROM EMP E, DEPT D
  WHERE E.DEPTNO = D.DEPTNO
ORDER BY EMPNO;

** 별칭은 출력 결과에 영향 X

** SELECT절의 * 사용

데이터베이스를 사용하는 웹 서비스, 모바일 앱 등 여러 응용 프로그램을 제작하는 프로그래밍에서 SQL 구문을 사용할 때는 각 테이블의 모든 열을 출력할지라고 대부분 *를 사용하지 않고 출력할 열을 하나하나 직접 지정.

---

조인 종류

2개 이상의 테이블을 하나의 테이블처럼 가로로 늘어뜨려 출력할 때 사용하는 조인은 대상 데이터를 어떻게 연결하느냐에 따라 등가 조인, 비등가 조인, 자체 조인, 외부 조인 등으로 구분.

• 등가 조인 (equi join) = 내부 조인(inner join) or 단순 조인(simple join)

등가 조인은 테이블을 연결한 후에 각 테이블의 특정 열과 일치하는 데이터를 기준으로 출력 행을 선정하는 방식.

긍가 조인은 일반적으로 가장 자주 사용하는 조인 방식이며, 외부 조인(outer join)과 같이 이름을 명시하지 않고 ‘조인을 사용한다’는 것은 대부분 등가 조인, 즉 특정 열이 일치하는 결과를 출력하는 방식.

** 여러 테이블의 열 이름이 같을 때 유의점.

등가 조인에서 조인 조건이 되는 각 테이블의 열 이름이 같을 때, 테이블을 구분하지 않고 해당 열 이름을 지정하면 오류가 발생.

이름이 겹치지 않으면 상관 x

-- 열 이름에 각각의 테이블 이름도 함께 명시할 때

SELECT E.EMPNO, E.ENAME, D.DEPTNO, D.DNAME, D.LOC
		FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO
ORDER BY D.DEPTNO, E.EMPNO;

-- WHERE절에 추가로 조건식 넣어 출력하기

SELECT E.EMPNO, E.ENAME, E.SAL, D.DEPTNO, D.DNAME, D.LOC
		FROM EMP E, DEPT D
	WHERE E.DEPTNO = D.DEPTNO
		AND SAL >= 3000;

모든 데이터를 출력해야 하는 경우는 흔치 않으니 구체적인 데이터를 출력하고자 WHERE절에 조인 조건 외에도 다양한 조건식을 활용.

 

조인 테이블 개수와 조건식 개수의 관계

기본적으로 데카르트 곱 현상이 일어나지 않게 하는 데 필요한 조건식의 최소 개수는 조인 테이블 개수에서 하나를 뺀 값.

WHERE절의 조건식을 사용해 테이블을 조인할 때 각 테이블을 정확히 연결하는 조건식이 전체 테이블 수보다 최소한 하나 적은 수 만큼은 있어야 함.

---

• 비등가 조인(non-equal join)

비등가 조인은 등가 조인 외의 방식을 의미

--급여 범위를 지정하는 조건식으로 조인하기
SELECT *
    FROM EMP E, SALGRADE S
  WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

** 비등가 조인 방식은 등가 조인 방식에 비해 그리 자주 사용하지는 않지만 조인 조건에 특정 열의 일치 여부를 검사하는 방식 외에 다른 방식도 사용할 수 있음.

---

• 자체 조인 (self join)

자체 조인은 하나의 테이블을 여러 개의 테이블처럼 활용하여 조인하는 방식으로, FROM절에 같은 테이블을 여러 번 지정하되 테이블의 별칭만 다르게 지정하는 방식.

-- 같은 테이블을 두 번 사용하여 자체 조인하기
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
    FROM EMP E1, EMP E2
  WHERE E1.MGR = E2.EMPNO;

** 하나의 테이블이지만 SELECT문 내부에서 별칭을 각각 달리하여 논리적으로 다른 테이블인 것 처럼 두 테이블을 조인하는 방식으로, 사용한 자체 조인 역시 2개 테이블에서 지정한 열 중 일치한 데이터를 기준으로 조인하므로 큰 범위에서는 등가 조인으로 볼 수 있음.

---

• 외부 조인(outer join)

두 테이블 간 조인 수행에서 조인 기준 열의 한쪽이 NULL이어도 강제로 출력하는 방식

외부 조인은 좌우를 따로 나누어 지정하는데, WHERE절에 조인 기준 열 중 한쪽에 (+) 기호를 붙여줌.

왼쪽 외부 조인 (left outer join)	WHERE TABLE1.COL1 = TABLE2.COL1(+)
오른쪽 외부 조인 (right outer join)	WHERE TABLE1.COL(+) = TABLE2.COL1

-- 왼쪽 외부 조인 사용하기
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
    FROM EMP E1, EMP E2
  WHERE E1.MGR = E2.EMPNO(+)
ORDER BY E1.EMPNO;
--왼쪽 외부 조인 - 오른쪽 열의 데이터 존재 여부에 상관없이 왼쪽 열을 기준으로 출력하라는 뜻.


-- 오른쪽 외부 조인 사용하기
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
    FROM EMP E1, EMP E2
  WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;
-- 오른쪽 외부 조인 - 왼쪽 열 데이터가 있는지와 상관없이 조건식 오른쪽 열을 지군으로 데이터를 출력하라는 뜻.

외부 조인은 조인 기준 열의 NULL을 처리하는 것을 목적으로 자주 사용하는 조인방식 이지만 (+) 기호를 붙이는 외부 조인 방식으로는 양쪽 모든 열을 외부 조인하는 ‘전체 외부 조인(full outer join)’은 사용할 수 없음.

*기본적으로 양쪽 모두를 외부 조인하는 문법 (+)기호를 사용한 방식으로는 구현할 수 없지만, 왼쪽 외부 조인을 사용한 SELECT문과 오른쪽 외부 조인을 사용한 SELECT문을 집합 연산자 UNION으로 합쳐서 같은 효과를 낼 순 있음.

---

SQL-99 표준 문법 조인

• NATURAL JOIN

등가 조인을 대신해 사용할 수 있는 조인 방식으로, 조인 대상인 두 테이블에서 이름과 자료형이 같은 열을 찾은 후 그 열을 기준으로 등가 조인을 하는 방식.

-- NATURAL JOIN을 사용하여 조인하기

SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
      DEPTNO, D.DNAME, D.LOC
  FROM EMP E NATURAL JOIN DEPT D
ORDER BY DEPTNO, E.EMPNO;

** EMP테이블과 DEPT테이블은 공통 열 DEPTNO가 있으므로 NATURAL JOIN을 사용할 때 자동으로 DEPTNO 열을 기준으로 등가 조인됨.

기존 등가 조인과 다르게 조인 기준열인 DEPTNO를 SELECT절에 명시할 때 테이블 이름을 붙이면 안된다는 특성이 있음!

---

• JOIN ~ USING

JOIN~USING 키워드를 사용한 조인 역시 기존 등가 조인을 대신하는 방식.

NATURAL JOIN이 자동으로 조인 기준 열을 지정하는 것과 달리 USING키워드로 조인 기준으로 사용할 열을 지정.

FROM TABLE1 JOIN TABLE2 USING (조인에 사용할 기준 열)

-- JOIN~USING을 사용하여 조인하기
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
      DEPTNO, D.DNAME, D.LOC
    FROM EMP E JOIN DEPT D USING(DEPTNO)
  WHERE SAL >= 3000
ORDER BY DEPTNO, E.EMPNO;

JOIN ~ USING 키워드를 사용하여 EMP테이블과 DEPT 테이블을 DEPTNO 열 기준으로 등가 조인한다면, 다른 조인 방식과 마찬가지로 조인된 결과 행을 추가로 제한할 때 WHERE절에 조건식을 추가하여 함께 사용할 수 있음. 조인 기준 열로 지정한 열은 SELECT절에서 테이블이름을 붙이지 않고 작성.

 

• JOIN ~ ON

기존 WHERE절에 있는 조인 조건식을 ON 키워드 옆에 작성.

조인 기준 조건식은 ON에 명시하고 그 밖의 출력행은 걸러 내고자 WHERE 조건식을 따로 사용하는 방식.

기존 조인 방식과 크게 다르지 않아 WHERE절에 모두 명시하는 방식을 선호하는 사람도 있음.

FROM TABLE1 JOIN TABLE2 ON (조인 조건식)

-- JOIN ~ ON으로 등가 조인하기
SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM,
      E.DEPTNO,
      D.DNAME, D.LOC
    FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
  WHERE SAL <= 3000
ORDER BY E.DEPTNO, EMPNO;

 

• OUTER JOIN

외부 조인에 사용하며, WHERE절이 아닌 FROM절에서 외부 조인을 선언함.

왼쪽 외부 조인	기존	WHERE TABLE1.COL1 = TABLE2.COL1(+)
left outer join	SQL-99	FROM TABLE1 LEFT OUTER JOIN TABLE2 ON (조인 조건식)
오른쪽 외부 조인	기존	WHERE TABLE1.COL(+) = TABLE2.COL1
right outer join	SQL-99	FROM TABLE1 RIGHT OUTER JOIN TABLE2 ON (조인 조건식)
전체 외부 조인	기존	기본 문법은 없음 (UNION 집합 연산자를 활용)
full outer join	SQL-99	FROM TABLE1 FULL OUTER JOIN TABLE2 ON (조인 조건식)

-- 왼쪽 외부 조인을 표준으로 SQL로 작성하기
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
    FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
  ORDER BY E1.EMPNO;
  -- 왼쪽 외부 조인의 기준열은 MGR열, KING은 직속 상관이 없으므로 NULL값.
  
  
  -- 오른쪽 외부 조인을 표준으로 SQL로 작성하기
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
    FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
  ORDER BY E1.EMPNO;
  
  
  -- 전체 외부 조인을 표준 SQL로 작성하기
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
    FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
  ORDER BY E1.EMPNO;

** 전체 외부 조인 - 왼쪽 오른쪽열이 NULL일때를 모두 출력.

기존 외부 조인으론 UNION 집합 연산자를 사용하여 왼쪽,오른쪽 외부 조인의 결과를 합치는 방법만 가능했는데 FULL OUTER JOIN~ ON 키워드로 양쪽 모두 외부 조인한 결괏값을 출력.

 

*SQL-99조인 방식에서 3개이상의 테이블을 조인할 때

기존 조인 방식은 FROM절에 조인 테이블을 명시하고 조인 관련 조건식을 WHERE절에 지정하므로 테이블이 2개가 넘더라도 아래와 같이 작성하면 아무 문제 없음.

FROM TABLE1, TABLE2, TABLE3
WHERE TABLE1.COL = TABLE2.COL
AND TABLE2.COL = TABLE3.COL

 

하지만 FROM절에 조인 관련 내용을 작성해야 하는 SQL-99방식에서는 테이블이 2개가 넘을 때, FROM절에 두 개 테이블을 조인한 다음에 계속해서 SQL-99방식의 조인을 추가하면 세 개 이상의 테이블도 조인할 수 있음.

FROM TABLE1 JOIN TABLE2 ON (조건식)
	JOIN TABLE3 ON (조건식)