ANSI SQL은 모든 DB에 공용으로 사용할 수 있는 SQL 표준으로 테이블 조인관계는 FROM ~ ON ~ 에서 기술하고 WHERE절에서는 데이터를 필터링 하는 조건만 기술하므로 SQL구문의 가독성이 좋습니다.
조인이란?
연관된 둘 이상의 테이블에 대해 행들을 컬럼값을 기준으로 비교하여 새로운 행 집합을 만드는 것으로 각 테이블 간 의미있는 행을 연결하는 개념입니다.
조인의 4가지 유형
내부 조인, 완전 외부 조인, 왼쪽 외부 조인 및 오른쪽 외부 조인.
내부조인(Inner Join)
내부 조인은 두 테이블의 교집합을 추출합니다. 양쪽 테이블에서 정확히 조인조건을 만족하는 행들이 추출되는 일반적인 조인 방식 입니다.
실습
EMP, DEPT 테이블 전체를 조인하여 모든 사원의 사원번호, 사원명, 부서명을 출력하세요.
(부서명을 가져오기 위해 DEPT 테이블과 DEPTNO로 조인을 합니다. 사원테이블에서 부서번호가 없는 KING 사원은 출력되지 않습니다.)
사원 테이블과 부서테이블의 데이터를 확인 합니다.
SELECT empno, ename, deptno FROM emp
<실행결과>
empno
ename
deptno
1
7369
SMITH
20
2
7499
ALLEN
30
3
7521
WARD
30
4
7566
JONES
20
5
7654
MARTIN
30
6
7698
BLAKE
30
7
7782
CLARK
10
8
7788
SCOTT
20
9
7839
KING
10
7844
TURNER
30
11
7876
ADAMS
20
12
7900
JAMES
30
13
7902
FORD
20
14
7934
MILLER
10
부서 테이블(DEPT)의 데이터 입니다.
SELECT * FROM dept
<실행결과>
deptno
dname
loc
1
10
ACCOUNTING
NEW YORK
2
20
RESEARCH
DALLAS
3
30
SALES
CHICAGO
4
40
OPERATIONS
BOSTON
a. ANSI SQL 조인형식
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E INNER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
b. 일반적인 조인(모든 DB에서 사용가능)
SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E , DEPT D WHERE E.DEPTNO = D.DEPTNO;
외부조인(Outer Join)
외부조인은 한 테이블에서 모든 값을 가져오지만 다른 테이블에서 해당 테이블과 일치하는 행만 가져오려는 경우에 이용합니다. 왼쪽 외부 조인에서는 왼쪽 테이블의 모든 행과 오른쪽 테이블의 공통 행이 반환됩니다. 반대로, 오른쪽 외부 조인의 경우 오른쪽 테이블의 모든 행과 왼쪽 테이블의 공통 행이 반환됩니다.
왼쪽 외부 조인(Left Outer Join)
EMP, DEPT 테이블 전체를 조인하여 모든 사원의 사원번호, 사원명, 부서명을 출력하세요.
(EMP 테이블에서 부서코드가 없는 KING도 출력 됩니다.)
– ANSI SQL 조인형식 SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E LEFT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO;
– 오라클 조인 형식 SELECT E.EMPNO, E.ENAME, D.DNAME FROM EMP E , DEPT D WHERE E.DEPTNO = D.DEPTNO(+);
그렇다면 2가지 방식의 차이는 무엇일까요?
ANSI SQL 조인 구문에서는 조인조건은 ON 다음에, 데이터를 필터링 하기 위한 조건은 WHERE절에 기술하여 가독성이 좋습니다. 하지만 오라클 조인구문은 WHERE절에 조인조건과 데이터를 필터링하는 조건들을 같이 기술하여 이들의 순서가 잘 안지켜지고 SQL문이 복잡한 경우에는 가독성이 떨어지고 잘못된 결과를 도출할 확률이 높습니다. WHERE절의 기본 용도는 데이터를 필터링 한다는 관점에서 본다면 ANSI 조인구문을 사용하는 것이 바람직 합니다. 또한 ANSI 조인 방식은 어떤 DataBase에도 통용되는 방식 입니다.
물론 위 실습과 같은 내부조인의 경우 두가지 방식 모두 어떤 데이터베이스에서 SQL문을 작성하던지 동일하게 사용가능 하지만 외부조인의 경우 ANSI SQL 조인구문만 공용으로 사용가능하며 오라클 조인 구문은(+) 오라클 DB에서만 사용 가능합니다.
b. 오른쪽 외부 조인(Right Outer Join)
부서이름별 사원수를 조회 합니다. 외부조인을사용하지 않은 경우와 외부 조인을 사용한 경우를 비교하여 실습 합니다.
-- 외부조인을 사용하지 않은 경우, EMP 테이블에 부서원들이 하나도 없는 OPERATIONS -- 부서는 출력되지 않습니다. SELECT DNAME , COUNT(E.EMPNO) AS EMP_CNT FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO GROUP BY DNAME;
-- 외부조인을 이용(ANSI SQL 조인방식) SELECT DNAME , COUNT(E.EMPNO) AS EMP_CNT FROM EMP E RIGHT OUTER JOIN DEPT D ON E.DEPTNO = D.DEPTNO GROUP BY DNAME;
-- 외부조인을 이용(오라클 조인방식) SELECT DNAME , COUNT(E.EMPNO) AS EMP_CNT FROM EMP E, DEPT D WHERE E.DEPTNO(+) = D.DEPTNO GROUP BY DNAME;
c. 전체 외부 조인(Full Outer Join)
조인조건을 만족하지 않는 왼쪽 테이블의 모든 행과 오른쪽 테이블의 모든 행이 반환됩니다.
EMP, DEPT 테이블을 조인하여 사원이름과 부서명을 출력하는데 아래 조건을 만족하도록 작성을 해보겠습니다.
부서가 없는 KING도 출력
부서원이 없는 40번부서도 출력
즉 조인 조건이 맞지 않더라도 부서도 다 출력해야 하고 사원도 다 출력해야 하는 쿼리를 만들어야 하는데요, 이 경우에는 전체 외부조인(FULL OUTER JOIN)을 사용해야 합니다.
[ANSI SQL 조인방식]
SELECT ename , dname FROM emp FULL OUTER JOIN dept ON emp.deptno = dept.deptno
[오라클 조인방식은 지원하지 않습니다.]
SELECT ename , dname FROM emp , dept WHERE emp.deptno(+) = dept.deptno(+)
