[SQLD] #021 SQL 활용 - 표준 조인

목차

1. STANDARD SQL 개요

2. FROM 절 JOIN 형태

3. INNER JOIN

4. NATURAL JOIN

5. USING 조건절

6. ON 조건절

7. CROSS JOIN

8. OUTER JOIN

9. INNER vs OUTER vs CROSS JOIN 비교

 

 

---

 

 

 

1. STANDARD SQL 개요

1970년 : Dr.E.F.Codd 관계형 DBMS 논문 발표

1974년 : IBM SQL 개발

1979년 : Oracle 상용 DBMS 발표

1980년 : Sybase SQL Server 발표

1983년 : IBM DB2 발표

1986년 : ANSI/ISO SQL 표준 최초 제정 (SQL-86, SQL1)

1992년 : ANSI/ISO SQL 표준 개정 (SQL-92, SQL2)

1993년 : MS SQL Server 발표 

1999년 : ANSI/ISO SQL 표준 개정 (SQL-99, SQL3)

2003년 : ANSI/ISO SQL 표준 개정 (SQL-2003)

2008년 : ANSI/ISO SQL 표준 개정 (SQL-2008)

 

 

 

현재 우리가 사용하는 많은 시스템의 두뇌 역할을 하는 관계형 데이터베이스에 유일하게 접속할 수 있는 언어가 바로 SQL이다.

대표적인 ANSI/ISO 표준 SQL의 기능은 다음 내용을 포함한다. 

 

- STANDARD JOIN 기능 추가 (CROSS, OUTER JOIN 등 새로운 FROM 절 JOIN 기능들)

- SCALAR SUBQUERY, TOP-N QUERY 등의 새로운 SUBQUERY 기능들

- ROLLUP, CUBE, GROUPING SETS 등의 새로운 리포팅 기능

- WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

 

 

 

 

 

1. 일반 집합 연산자

 

현재 사용하는 SQL의 많은 기능이 관계형 데이터베이스의 이론을 수립한 E.F.Codd 박사의 논문에 언급이 되어있다. 논문에 언급된 8가지 관계형 대수는 다시 각각 4개의 일반 집합 연산자와 순수 관계 연산자로 나눌 수 있다. 

 

 

그중에서도 일반집합 연산자를 현재의 SQL과 비교하자면

 

1. UNION 연산 → UNION 기능

2. INTERSECTION 연산 → INTERSECTION 기능

3. DIFFERENCE 연산 → EXCEPT(Oracle은 MINUS) 기능

4. PRODUCT 연산 → CROSS JOIN 기능

 

으로 구현되었다. 

 

 

 

첫번째, UNION 연산은 수학적 합집합으로써 공통 교집합의 중복을 없애기 위한 사전 작업으로 정렬 작업을 하는데, 그 정렬 작업이 시스템에 부하를 준다. 이후 UNION ALL 기능이 추가되었는데, 특별한 요구 사항이 없다면 공통집합을 중복해서 그대로 보여주기 때문에 정렬 작업이 일어나지 않는 장점을 가진다.

 

두번째, INTERSECTION은 수학의 교집합으로써 두 집합의 공통 집합을 추출한다. 

 

세번째, DIFFERENCE는 수학의 차집합으로써 첫번째 집합에서 두번째 집합과의 공통 부분을 제외한 부분이다. 대다수 벤더는 EXCEPT를, Oracle은 MINUS 용어를 사용한다. 

 

네번째, PRODUCT의 경우는 CROSS PRODUCT라고 불리는 곱집합으로, JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 양쪽 집합의 M*N건의 데이터 조합이 발생하며, CARTESIAN PRODUCT라고도 표현한다. 

 

 

 

 

 

 

 

2. 순수 관계 연산자

 

순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 새롭게 만들어진 연산자이다. 

순수 관계 연산자를 현재의 SQL 문장과 비교하면 다음과 같다. 

 

5. SELECT 연산은 WHERE 절로 구현되었다. 

6. PROJECT 연산은 SELECT 절로 구현되었다. 

7. JOIN 연산은 다양한 JOIN 기능으로 구현되었다.

8. DIVIDE 연산은 현재 사용되지 않는다. 

 

 

다섯번째, SELECT 연산은 SQL 문장에서는 WHERE 절의 조건절 기능으로 구현이 되었다. (SELECT 연산과 SELECT 절은 다르다)

 

여섯번째, PROJECT 연산은 SQL 문장에서는 SELECT 절의 칼럼 선택 기능으로 구현이 되었다. 

 

일곱번째, JOIN 연산은 WHERE 절의 INNER JOIN 조건과 함께 FROM 절의 NATURAL JOIN, INNER JOIN, OUTER JOIN, USING 조건절, ON 조건절 등으로 가장 다양하게 발전하였다. 

 

여덟번째, DIVIDE 연산은 나눗셈과 비슷한 개념으로 왼쪽의 집합을 'XZ'로 나누었을 때, 즉 'XZ'를 모두 가지고있는 'A'가 답이 되는 기능으로, 현재는 사용되지 않는다. 

 

 

 

관계형 데이터베이스의 경우 요구사항 분석, 개념적 데이터 모델링, 논리적 데이터 모델링, 물리적 데이터 모델링 단계를 거치게 되는데, 이 단계에서 엔터티 확정 및 정규화 과정, 그리고 M;M 관계를 분해하는 절차를 거치게 된다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

2. FROM 절 JOIN 형태

ANSI/ISO SQL 에서 표시하는 FROM 절의 JOIN 형태는 다음과 같다. 

 

- INNER JOIN

- NATURAL JOIN

- USING 조건절

- ON 조건절

- CROSS JOIN

- OUTER JOIN

 

ANSI/ISO SQL에서 규정한 JOIN 문법은 WHERE 절을 사용하던 기존 JOIN 방식과 차이가 있다. 사용자는 기존 WHERE 절의 검색 조건과 테이블간의 JOIN을 구분 없이 사용하던 방식을 그대로 쓰면서, 추가된 선택 기능으로 테이블간의 JOIN 조건을 FROM 절에서 명시적으로 정의할 수 있게 되었다. 

 

INNER JOIN은 WHERE 절에서부터 사용하던 JOIN의 DEFAULT 옵션으로 JOIN 조건에서 동일한 값이 있는 행만 반환한다. DEFAULT 옵션이므로 생략이 가능하지만, CROSS JOIN, OUTER JOIN과는 같이 사용할 수 없다. 

 

NATURAL JOIN은 두 테이블간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 수행한다. 

 

새로운 SQL JOIN 문장중에서 가장 중요한 문장은 ON 조건절을 사용하는 경우이다. 과거 WHERE 절에서 JOIN 조건과 데이터 검증 조건이 같이 사용되어 용도가 불분명한 경우가 발생할 수 있었는데, WHERE 절의 JOIN 조건을 FROM 절의 ON 조건절로 분리하여 표시함으로써 사용자가 이해하기 쉽도록 한다. 

ON 조건절의 경우 NATURAL JOIN처럼 JOIN 조건이 숨어있지 않고 명시적으로 JOIN 조건을 구분할 수 있다. 또한 NATURAL JOIN이나 USING 조건절처럼 칼럼명이 똑같아야된다는 제약 없이 사용할 수 있다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

3. INNER JOIN

INNER JOIN은 내부 조인이라고 하고, JOIN 조건에서 동일한 값이 있는 행만 반환한다. INNER JOIN 표시는 그동안 WHERE절에서 사용하던 JOIN 조건을 FROM절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야 한다. 

 

 

-- WHERE 절 JOIN 조건 

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

-- FROM 절 JOIN 조건

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP
INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

 위 두개의 SQL은 같은 결과를 얻을 수 있다. 

 

-- INNER는 JOIN의 디폴트 옵션으로, 이래 SQL문과 같이 생략 가능하다. 

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP
JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;

 

 

 

 

 

 

 

 

 

 

 

4. NATURAL JOIN

NATURAL JOIN은 두 테이블간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI JOIN을 수행한다. NATURAL JOIN이 명시되면, 추가로 USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다. 

 

예제) 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾아본다. 

SELECT DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP NATURAL JOIN DEPT;

 

위 SQL은 별도의 JOIN 칼럼을 지정하지 않았지만, 두개의 테이블에서 DEPTNO 라는 공통된 칼럼을 자동으로 인식하여 JOIN을 처리한 것이다. JOIN에 사용된 칼럼들은 같은 데이터 유형이어야 하며, ALIAS나 테이블 명과 같은 접두사를 붙일 수 없다. 

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT;

-- ERROR: NATURAL JOIN에 사용된 열은 식별자를 가질 수 없음

 

NATURAL JOIN은 JOIN이 되는 테이블의 데이터 성격(도메인)과 칼럼명 등이 동일해야하는 제약조건이 있다. 간혹 모델링 상의 부주의로 인해 동일한 칼럼명이라도 다른 용도의 데이터를 저장하는 경우도 있으므로 주의해서 사용해야 한다. 

 

 

 

 

 

 

 

 

 

 

 

 

 

5. USING 조건절

NATURAL JOIN에서는 모든 일치되는 칼럼들에 대해 JOIN이 이루어지지만 FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수 있다. 

 

예제) 세개의 칼럼명이 모두 같은 DEPT와 DEPT_TEMP 테이블을 DEPTNO 칼럼을 이용한 JOIN의 USING 조건절로 수행한다. 

SELECT * 
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

 

위 SQL의 '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 USING 조건절의 기준이 되는 칼럼이 다른 칼럼보다 먼저 출력된다. (DEPTNO가 첫번째 칼럼이 된다) 이 때 USING JOIN은 JOIN에 사용된 같은 이름의 칼럼을 하나로 처리한다.

-- 잘못된 사례
SELECT DEPT.DEPTNO,
       DEPT.DNAME,
       DEPT.LOC,
       DEPT_TEMP.DNAME,
       DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO);

--ERROR: USING 절의 열 부분은 식별자를 가질 수 없음


-- 바른 사례
SELECT DEPTNO,
       DEPT.DNAME,
       DEPT.LOC,
       DEPT_TEMP.DNAME,
       DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP
USING (DEPTNO);

 

 

 

 

 

 

 

 

 

 

 

 

 

6. ON 조건절

JOIN 서술부(ON 조건절)와 비 JOIN 서술부(WHERE 조건절)를 분리하기 쉽고, 칼럼명이 다르더라도 JOIN 조건을 사용할 수 있다는 장점이 있다. 

 

예제) 사원 테이블과 부서 테이블에서 사원번호와 사원이름, 소속부서 코드, 소속부서 이름을 출력한다. 

SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO);

 

 

NATURAL JOIN의 JOIN 조건은 기본적으로 같은 이름을 가진 모든 칼럼들에 대한 동등 조건이지만, 임의의 JOIN 조건을 지정하거나, 이름이 다른 칼럼명을 JOIN 조건으로 사용하거나, JOIN 칼럼을 명시하기 위해서는 ON 조건절을 사용한다. 

 

USING 조건절을 이용한 JOIN에서는 JOIN 칼럼에 대해 ALIAS나 테이블명과 같은 접두사를 사용하면 SYNTAX ERROR가 발생하지만, 반대로 ON 조건절을 사용한 JOIN의 경우는 ALIAS나 테이블명과 같은 접두사를 사용하여 SELECT에 사용되는 칼럼을 논리적으로 명확하게 지정해주어야 한다. (DEPTNO → E.DEPTNO)

 

 

 

 

 

1. WHERE 절과의 혼용

예제) 부서코드 30인 부서의 소속사원 이름 및 소속 부서 코드, 부서 코드, 부서 이름을 찾아본다. 

SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D 
ON (E.DEPTNO = D.DEPTNO)
WHERE E.DEPTNO = 30;

-- ON 조건절과 WHERE 검색 조건은 충돌 없이 사용할 수 있다.

 

 

 

 

2. ON 조건절 + 데이터 검증 조건 추가

ON 조건절에 JOIN 조건 외에도 데이터 검색 조건을 추가할 수는 있으나, 검색 조건 목적인 경우는 WHERE 절을 사용할 것을 권고한다. 

 

예제) 매니저 사원 번호가 7698번인 사원들의 이름 및 소속 부서 코드, 부서 이름을 찾는다. 

SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D 
ON (E.DEPTNO = D.DEPTNO AND E.MGR = 7698);



SELECT E.ENAME, E.MGR, D.DEPTNO, D.DNAME 
FROM EMP E JOIN DEPT D 
ON (E.DEPTNO = D.DEPTNO) 
WHERE E.MGR = 7698

위의 두 SQL문은 같은 결과를 얻을 수 있다. 

 

 

 

 

 

 

 

 

3. ON 조건절 예제

팀과 스타디움 테이블을 스타디움 ID로 JOIN하여 팀이름, 스타디움 ID, 스타디움 이름을 찾아본다. 

SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME 
FROM TEAM JOIN STADIUM
ON TEAN.STADIUM_ID = STADIUM.STADIUM_ID
ORDER BY STADIUM_ID;

위 SQL은 STADIUM_ID 라는 공통된 칼럼이 있기 때문에 아래처럼 USING 조건절로 구현할 수도 있다. 

SELECT TEAM_NAME, STADIUM_ID, STADIUM_NAME
FROM TEAM JOIN STADIUM
USING (STADIUM_ID)
ORDER BY STADIUM_ID;

또한 위 SQL은 고전적인 방식인 WHERE 절의 INNER JOIN으로 구현할 수도 있다. 

SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME
FROM TEAM, STADIUM
WHERE TEAM.STADIUM_ID = STADIUM.STADIUM_ID
ORDER BY STADIUM_ID;

 

 

 

 

 

 

 

 

4. 다중 테이블 JOIN

예제) 사원과 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보를 출력한다. 

SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME
FROM EMP E JOIN DEPT D
ON (E.DEPTNO = D.DEPTNO)
JOIN DEPT_TEMP T
ON (E.DEPTNO = T.DEPTNO);

 

위 SQL은 고전적인 방식인 WHERE 절의 INNER JOIN으로 구현할 수도 있다. 

SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME New_DNAME
FROM EMP E, DEPT D, DEPT_TEMP T
WHERE E.DEPTNO = D.DEPTNO
AND E.DEPTNO = T.DEPTNO;

 

 

 

 

 

 

 

 

 

 

 

 

 

7. CROSS JOIN

CROSS JOIN은 E.F.Codd 박사가 언급한 일반 집합 연산자의 PRODUCT 개념으로, 테이블간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다. 

두개의 테이블에 대한 CARTESIAN PRODUCT 또는 CROSS PRODUCT와 같은 표현으로, 결과는 양쪽 집합의 M*N 건의 데이터 조합이 발생한다. 

 

예제) 사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 찾는다.

SELECT ENAME, DNAME 
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;

 

NATURAL JOIN의 경우 WHERE 절에서 JOIN 조건을 추가할 수 없지만, CROSS JOIN의 경우 WHERE 절에 JOIN 조건을 추가할 수 있다.  그러나 이 경우는 CROSS JOIN이 아니라 INNER JOIN과 같은 결과를 얻기 때문에 CROSS JOIN을 사용하는 의미가 없어지므로 권고하지 않는다. 

 

 

 

 

 

 

 

 

 

 

8. OUTER JOIN

OUTER JOIN은 JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용된다. 

 

 

위 그림은 TAB1 테이블이 TAB2 테이블을 JOIN 하되, TAB2에 JOIN 데이터가 있는 경우는 TAB2의 데이터를 함께 출력하고, TAB2의 JOIN 데이터가 없는 경우에도 TAB1의 모든 데이터를 표시하고 싶은 경우이다. 

 

TAB1의 모든 값에 대해 TAB2의 데이터가 반드시 존재한다는 보장이 없는 경우 OUTER JOIN을 사용하여 해결 가능하다. 

OUTER JOIN은 JOIN 조건을 FROM 절에서 정의하겠다는 표시이므로 USING 조건절이나 ON 조건절을 필수적으로 사용해야한다. 

 

 

 

 

 

 

1. LEFT OUTER JOIN

JOIN 수행시 먼저 표기된 좌측 테이블에 해당하는 데이터를 먼저 읽은 후, 나중 표기된 우측 테이블에서 JOIN 대상 데이터를 읽어온다. 

즉 테이블 A와 B가 있을 때, A와 B를 비교해서 B의 JOIN 칼럼에서 같은 값이 있을 때 해당 데이터를 가져오고, B의 JOIN 칼럼에서 같은 값이 없는 경우에는 B 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 그리고 LEFT JOIN으로 OUTER 키워드를 생략해서 사용할 수 있다. 

 

예제) STADIUM 에 등록된 운동장중에는 홈팀이 없는 경기장도 있다. STADIUM과 TEAM 을 JOIN하되, 홈팀이 없는 경기장의 정보도 같이 출력하도록 한다. 

SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME
FROM STADIUM
LEFT OUTER JOIN TEAM
ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
ORDER BY HOMETEAM_ID;

 

OUTER는 생략 가능하므로 아래와 같이 쓸수도 있다. 

SELECT STADIUM_NAME, STADIUM.STADIUM_ID, SEAT_COUNT, HOMETEAM_ID, TEAM_NAME
FROM STADIUM
LEFT JOIN TEAM
ON STADIUM.HOMETEAM_ID = TEAM.TEAM_ID
ORDER BY HOMETEAM_ID;

 

 

 

 

 

 

 

 

 

2. RIGHT OUTER JOIN

JOIN 수행시 LEFT JOIN과 반대로 우측 테이블이 기준이 되어 결과를 생성한다. 즉 TABLE A와 B가 있을 때, 둘을 비교해서 A의 JOIN 칼럼에서 같은 값이 있을 때 해당 데이터를 가져오고, A의 JOIN 칼럼에서 같은 값이 없는 경우에는 A 테이블에서 가져오는 칼럼들은 NULL 값으로 채운다. 

 

예제) DEPT에 등록된 부서 중에는 사원이 없는 부서도 있다. DEPT와 EMP를 JOIN하되, 사원이 없는 부서 정보도 같이 출력하도록 한다. 

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
RIGHT OUTER JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

 

OUTER는 생략 가능하므로 아래와 같이 쓸수도 있다. 

SELECT E.ENAME, D.DEPTNO, D.DNAME
FROM EMP E
RIGHT JOIN DEPT D
ON E.DEPTNO = D.DEPTNO;

 

 

 

 

 

 

 

 

 

3. FULL OUTER JOIN

FULL OUTER JOIN은 JOIN 수행시 좌측, 우측 테이블의 모든 데이터를 읽어 JOIN하여 결과를 생성한다. 

즉, TABLE A와 B가 있을 때, RIGHT OUTER JOIN과 LEFT OUTER JOIN의 결과를 합집합으로 처리한 것과 같다. 

단 UNION ALL 이 아닌 UNION 기능과 같으므로 중복되는 데이터는 삭제한다. 

 

예제) DEPTNO 기준으로 DEPT와 DEPT_TEMP 데이터를 FULL OUTER JOIN으로 출력한다.

SELECT *
FROM DEPT
FULL OUTER JOIN DEPT_TEMP
ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO;

 

OUTER는 생략 가능한 키워드이므로 아래와 같이 쓸 수 있다.

SELECT *
FROM DEPT
FULL JOIN DEPT_TEMP
ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO;

 

또한 아래 SQL도 같은 결과를 얻을 수 있다.

SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC
FROM DEPT L
LEFT OUTER JOIN DEPT_TEMP R
ON L.DEPTNO = R.DEPTNO

UNION

SELECT L.DEPTNO, L.DNAME, L.LOC, R.DEPTNO, R.DNAME, R.LOC
FROM DEPT L
RIGHT OUTER JOIN DEPT_TEMP R
ON L.DEPTNO = R.DEPTNO;

 

 

 

 

 

 

 

 

 

 

 

 

 

 

9. INNER vs OUTER vs CROSS JOIN 비교

 

 

 

1. INNER JOIN

양쪽 테이블에 모두 존재하는 키값이 B-B, C-C인 2건이 출력된다. 

 

2. LEFT OUTER JOIN

TAB1을 기준으로 키 값 조합이 B-B, C-C, D-NULL, E-NULL인 4건이 출력된다.

 

3. RIGHT OUTER JOIN

TAB2를 기준으로 키 값 조합이 NULL-A, B-B, C-C인 3건이 출력된다.

 

4. FULL OUTER JOIN

양쪽 테이블을 기준으로 키 값 조합이 NULL-A, B-B, C-C, D-NULL, E-NULL인 5건이 출력된다. 

 

5. CROSS JOIN

- JOIN 가능한 모든 경우를 표시하지만, OUTER JOIN은 제외한다. 

- 양쪽 테이블 TAB1과 TAB2의 데이터를 곱한 개수인 4*3=12 건이 추출된다. 

- 키 값 조합 : B-A, B-B. B-C, C-A, C-B, C-C, D-A, D-B, D-C, E-A, E-B, E-C 

 

 

 

 

 

 

 

 

 

 

---

출처
이 글의 내용은 모두 한국데이터베이스진흥원이 출판한 SQL 전문가 가이드 2013 Edition을 기본으로 한다.