STANDARD SQL 개요
1️⃣ 역사와 발전
- 1970년: Dr. E.F.Codd가 관계형 데이터베이스(Relational DB)에 대한 논문 발표
- 1974년: IBM에서 SQL 개발
- 1979년: Oracle에서 상용 DBMS 발표
- 1980년: Sybase SQL Server 발표 (이후 Sybase ASE로 개명)
- 1983년: IBM DB2 발표
- 1986년: ANSI/ISO SQL 표준 최초 제정 (SQL-86, SQL1)
- 1992년: ANSI/ISO SQL 표준 개정 (SQL-92, SQL2)
- 1993년: MS SQL Server 발표 (Windows OS, Sybase Code 활용)
- 1999년: ANSI/ISO SQL 표준 개정 (SQL-99, SQL3)
- 2003년: ANSI/ISO SQL 표준 개정 (SQL-2003)
- 2008년: ANSI/ISO SQL 표준 개정 (SQL-2008)
2️⃣ 관계형 DBMS의 발전
- 오브젝트 개념 통합: 순수 관계형 DBMS에서 객체관계형(Object Relational) DBMS로 발전.
- 표준 SQL: SQL은 관계형 데이터베이스와 상호작용하는 유일한 언어로, SQL 표준의 발전은 사용자와 개발자에게 중요한 관심사.
- SQL 표준의 발전: SQL-86에서 SQL-2008까지 여러 차례 표준이 개정되었으며, DBMS 벤더들은 이 표준을 따라 SQL의 기능을 구현.
ANSI/ISO SQL 표준
1️⃣ SQL-99 (SQL3)
- 호환성: SQL-92 표준의 명세 부족 문제를 해결하기 위해 제정.
- Oracle 8i/9i: ANSI/ISO SQL3의 기능을 가장 먼저 구현.
- 기능 추가: 새로운 기능들이 추가되어 SQL을 더 쉽게 사용하고 튜닝할 수 있게 됨.
2️⃣ SQL-2003
- 기능 개선: SQL-99의 기능을 확장하고, 표준 SQL에 포함된 기능을 다양한 벤더가 구현.
3️⃣ SQL-2008
- 소폭 개정: 사용자가 체감하는 변화는 크지 않으나, 여전히 벤더별로 일부 기능의 개발이 진행 중.
주요 기능들
1️⃣ STANDARD JOIN
- CROSS JOIN: 모든 가능한 데이터 조합을 반환.
- OUTER JOIN: LEFT, RIGHT, FULL OUTER JOIN을 지원.
- INNER JOIN: 두 테이블 간 공통된 데이터를 반환.
2️⃣ SUBQUERY
- SCALAR SUBQUERY: 단일 값을 반환하는 서브쿼리.
- TOP-N QUERY: 상위 N개의 결과를 반환하는 쿼리.
3️⃣ 리포팅 기능
- ROLLUP, CUBE, GROUPING SETS: 다양한 그룹화와 집계 기능 제공.
4️⃣ 분석 기능
- WINDOW FUNCTION: 행을 그룹화하고 분석하는 기능 제공.
일반 집합 연산자
1️⃣ UNION 연산자
- UNION: 두 집합의 합집합을 반환하며, 중복을 제거하기 위해 정렬 작업을 수행.
- UNION ALL: 중복을 제거하지 않고 합집합을 반환하여 성능이 향상됨.
2️⃣ INTERSECTION 연산자
- INTERSECT: 두 집합의 공통 요소를 반환.
3️⃣ DIFFERENCE 연산자
- EXCEPT/MINUS: 첫 번째 집합에서 두 번째 집합과의 차집합을 반환.
- EXCEPT: 대부분의 DBMS에서 사용되는 표준 용어.
- MINUS: Oracle에서 사용되는 용어.
4️⃣ PRODUCT 연산자
- CROSS JOIN: JOIN 조건이 없는 모든 데이터 조합을 반환.
- CARTESIAN PRODUCT: 수학적 용어로 모든 가능한 데이터 쌍을 의미.
순수 관계 연산자
- 순수 관계 연산자는 관계형 데이터베이스를 구현하기 위해 설계된 연산자이다. E.F. Codd가 제안한 관계형 데이터베이스의 이론에 기반하여 SQL에서 구현된 방식과 비교하면 다음과 같다.
- SELECT 연산자: SQL에서 WHERE 절의 조건절로 구현되었다. SELECT 연산자는 관계형 데이터베이스 이론에서 튜플(행)을 선택하는 연산이다.
- PROJECT 연산자: SQL에서 SELECT 절의 칼럼 선택 기능으로 구현되었다. PROJECT 연산자는 특정 속성(열)을 선택하는 연산입니다.
- JOIN 연산자: SQL에서 다양한 JOIN 방식으로 구현되었다.
NATURAL JOIN,INNER JOIN,OUTER JOIN,USING절,ON절 등 다양한 형태로 발전되었다. - DIVIDE 연산자: 나눗셈과 비슷한 개념으로, 왼쪽 집합을 나누어 오른쪽 집합을 모두 포함하는 결과를 반환하는 연산이다.
현재 SQL에서는 사용되지 않는다.
FROM 절 JOIN 형태
ANSI/ISO SQL 표준에서 정의한 FROM 절의 JOIN 형태는 다음과 같다.
- INNER JOIN: 두 테이블 간의 동일한 값이 있는 행만 반환한다. WHERE 절에서도 사용되던 기본적인 JOIN 방식이다.
- 예시:
SELECT * FROM table1 INNER JOIN table2 ON table1.id = table2.id;
- 예시:
- NATURAL JOIN: 동일한 이름을 갖는 모든 칼럼에 대해 EQUI JOIN을 수행합니다. NATURAL INNER JOIN으로도 불리며, 동일한 칼럼명이 있는 경우에만 사용된다.
- 예시:
SELECT * FROM table1 NATURAL JOIN table2;
- 예시:
- USING 조건절: 특정 칼럼명을 명시하여 JOIN 조건을 지정한다.
- 예시:
SELECT * FROM table1 JOIN table2 USING (common_column);
- 예시:
- ON 조건절: JOIN 조건을 명시적으로 정의하여, WHERE 절의 JOIN 조건과 데이터 검증 조건을 분리할 수 있다.
- 예시:
SELECT * FROM table1 JOIN table2 ON table1.id = table2.id;
- 예시:
- CROSS JOIN: 두 테이블의 모든 가능한 조합을 반환한다. 조건이 없는 JOIN으로, 모든 행의 곱집합을 생성한다.
- 예시:
SELECT * FROM table1 CROSS JOIN table2;
- 예시:
- OUTER JOIN: LEFT, RIGHT, FULL OUTER JOIN을 포함하며, 한쪽 테이블에만 있는 데이터도 포함하여 반환한다.
- 예시 (LEFT OUTER JOIN):
SELECT * FROM table1 LEFT OUTER JOIN table2 ON table1.id = table2.id;
- 예시 (LEFT OUTER JOIN):
INNER JOIN
INNER JOIN은 두 테이블 간의 조인 조건에서 동일한 값을 가진 행만 반환한다. 이는 OUTER JOIN과 대조적으로 내부 조인이라고 하며, 조인 조건을 만족하는 데이터만을 반환하는 것이 특징이다. INNER JOIN의 사용법과 특징을 설명하면 다음과 같다.
-- WHERE 절에서의 INNER JOIN
-- 이 방식은 WHERE 절에서 조인 조건을 사용하여 데이터를 결합한다.
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;-- FROM 절에서의 INNER JOIN
-- 이 방식은 FROM 절에서 조인 조건을 명시적으로 정의한다.
-- INNER JOIN은 기본 옵션이므로 생략이 가능하다
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;
SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME
FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;실행 결과는 다음과 같다.
DEPTNO EMPNO ENAME DNAME
------ ----- ------ ---------
20 7369 SMITH RESEARCH
30 7499 ALLEN SALES
30 7521 WARD SALES
20 7566 JONES RESEARCH
30 7654 MARTIN SALES
30 7698 BLAKE SALES
10 7782 CLARK ACCOUNTING
20 7788 SCOTT RESEARCH
10 7839 KING ACCOUNTING
30 7844 TURNER SALES
20 7876 ADAMS RESEARCH
30 7900 JAMES SALES
20 7902 FORD RESEARCH
10 7934 MILLER ACCOUNTINGNATURAL JOIN
EMP 테이블
| EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTNO |
|---|---|---|---|---|---|---|---|
| 7369 | SMITH | CLERK | 7902 | 1980-12-17 | 800 | NULL | 20 |
| 7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 | 1600 | 300 | 30 |
| 7521 | WARD | SALESMAN | 7698 | 1981-02-22 | 1250 | 500 | 30 |
| 7566 | JONES | MANAGER | 7839 | 1981-04-02 | 2975 | NULL | 20 |
| 7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 | 1250 | 1400 | 30 |
| 7698 | BLAKE | MANAGER | 7839 | 1981-05-01 | 2850 | NULL | 30 |
| 7782 | CLARK | MANAGER | 7839 | 1981-06-09 | 2450 | NULL | 10 |
| 7788 | SCOTT | ANALYST | 7566 | 1982-12-09 | 3000 | NULL | 20 |
| 7839 | KING | PRESIDENT | NULL | 1981-11-17 | 5000 | NULL | 10 |
| 7844 | TURNER | SALESMAN | 7698 | 1981-09-08 | 1500 | 0 | 30 |
| 7876 | ADAMS | CLERK | 7788 | 1983-01-12 | 1100 | NULL | 20 |
| 7900 | JAMES | CLERK | 7698 | 1981-12-03 | 950 | NULL | 30 |
| 7902 | FORD | ANALYST | 7566 | 1981-12-03 | 3000 | NULL | 20 |
| 7934 | MILLER | CLERK | 7782 | 1982-01-23 | 1300 | NULL | 10 |
DEPT 테이블의 데이터
| DEPTNO | DNAME | LOC |
|---|---|---|
| 10 | ACCOUNTING | NEW YORK |
| 20 | RESEARCH | DALLAS |
| 30 | SALES | CHICAGO |
| 40 | OPERATIONS | BOSTON |
NATURAL JOIN은 두 테이블 간의 동일한 이름을 가진 모든 칼럼에 대해 EQUI JOIN을 수행한다. NATURAL JOIN은 동일한 칼럼명과 데이터 유형을 가진 칼럼에 대해 자동으로 조인 조건을 생성한다.
-- NATURAL JOIN 사용 예제
SELECT DEPTNO, EMPNO, ENAME, DNAME
FROM EMP NATURAL JOIN DEPT;DEPTNO EMPNO ENAME DNAME
------ ----- ------ ---------
20 7369 SMITH RESEARCH
30 7499 ALLEN SALES
30 7521 WARD SALES
20 7566 JONES RESEARCH
30 7654 MARTIN SALES
30 7698 BLAKE SALES
10 7782 CLARK ACCOUNTING
20 7788 SCOTT RESEARCH
10 7839 KING ACCOUNTING
30 7844 TURNER SALES
20 7876 ADAMS RESEARCH
30 7900 JAMES SALES
20 7902 FORD RESEARCH
10 7934 MILLER ACCOUNTING1️⃣ NATURAL JOIN의 특징과 제약
- 동일한 칼럼명이 있는 경우에만 사용 가능.
- USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 추가로 정의할 수 없음.
- SQL Server에서는 지원되지 않음.
- 같은 이름의 칼럼을 하나로 처리하며, 칼럼 순서에 따라 결과가 달라질 수 있음.
- 동일한 칼럼명이지만 다른 용도의 데이터를 저장하는 경우 주의가 필요함.
2️⃣ NATURAL JOIN과 INNER JOIN의 차이점
NATURAL JOIN은 동일한 칼럼명을 가진 칼럼에 대해 자동으로 조인 조건을 생성하며, 동일한 이름의 칼럼을 하나로 처리한다.
SELECT * FROM EMP NATURAL JOIN DEPT;INNER JOIN은 명시적으로 조인 조건을 정의하며, 동일한 이름의 칼럼을 별개의 칼럼으로 표시한다.
SELECT * FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO;1. 예제: DEPT_TEMP 테이블 생성 및 데이터 변경
CREATE TABLE DEPT_TEMP AS SELECT * FROM DEPT; -- Oracle
SELECT * INTO DEPT_TEMP FROM DEPT; -- SQL Server
UPDATE DEPT_TEMP SET DNAME = 'R&D' WHERE DNAME = 'RESEARCH';
UPDATE DEPT_TEMP SET DNAME = 'MARKETING' WHERE DNAME = 'SALES';
SELECT * FROM DEPT_TEMP;DEPT_TEMP 테이블 데이터는 아래와 같다.
DEPTNO DNAME LOC
------ ---------- ---------
10 ACCOUNTING NEW YORK
20 R&D DALLAS
30 MARKETING CHICAGO
40 OPERATIONS BOSTONNATURAL JOIN 예제
SELECT * FROM DEPT NATURAL JOIN DEPT_TEMP;-- 실행 결과
DEPTNO DNAME LOC
------ ---------- ---------
10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON부서번호 20, 30의 데이터는 DNAME이 변경되어 제외되었다.
INNER JOIN 예제
SELECT * FROM DEPT JOIN DEPT_TEMP
ON DEPT.DEPTNO = DEPT_TEMP.DEPTNO
AND DEPT.DNAME = DEPT_TEMP.DNAME
AND DEPT.LOC = DEPT_TEMP.LOC;-- 실행 결과는 아래와 같다.
DEPTNO DNAME LOC DEPTNO DNAME LOC
------ ---------- --------- ------ ---------- ---------
10 ACCOUNTING NEW YORK 10 ACCOUNTING NEW YORK
40 OPERATIONS BOSTON 40 OPERATIONS BOSTONUSING 조건절
USING 조건절은 NATURAL JOIN과 유사하지만, 선택한 특정 칼럼에 대해서만 EQUI JOIN을 수행할 수 있는 기능을 제공한다. 이 기능은 SQL Server에서는 지원되지 않는다.
- USING 조건절의 특징은 다음과 같다.
- 동일한 이름을 가진 칼럼들 중 선택적으로 EQUI JOIN을 수행.
- NATURAL JOIN과 달리, 원하는 칼럼만 지정하여 JOIN 수행 가능.
- JOIN에 사용된 칼럼은 결과에서 한 번만 나타나고, 나머지 칼럼은 각각의 테이블에서 가져온다.
- USING 조건절을 사용할 때는 칼럼 이름 앞에 테이블 이름이나 별칭을 사용할 수 없다.
예제와 실행 결과
1. DEPT와 DEPT_TEMP 테이블을 DEPTNO 칼럼으로 JOIN
SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);-- 실행 결과
DEPTNO DNAME LOC DNAME LOC
------ ---------- --------- ---------- ---------
10 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH DALLAS R&D DALLAS
30 SALES CHICAGO MARKETING CHICAGO
40 OPERATIONS BOSTON OPERATIONS BOSTON2. 잘못된 예제와 올바른 예제 비교
-- 잘못된 예제
SELECT DEPT.DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);
-- USING 절을 사용할 때는 이러한 식별자를 포함한 열 지정이 허용되지 않는다.
-- ERROR: USING 절의 열 부분은 식별자를 가질 수 없다.-- 올바른 예제
SELECT DEPTNO, DEPT.DNAME, DEPT.LOC, DEPT_TEMP.DNAME, DEPT_TEMP.LOC
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);
-- USING 절을 사용할 때는 이렇게 열 이름만 지정해야 한다.-- 실행 결과
DEPTNO DNAME LOC DNAME LOC
------ ---------- --------- ---------- ---------
10 ACCOUNTING NEW YORK ACCOUNTING NEW YORK
20 RESEARCH DALLAS R&D DALLAS
30 SALES CHICAGO MARKETING CHICAGO
40 OPERATIONS BOSTON OPERATIONS BOSTON3. DEPT와 DEPT_TEMP 테이블을 DNAME 칼럼으로 JOIN
SELECT * FROM DEPT JOIN DEPT_TEMP USING (DNAME);-- 실행 결과
DNAME DEPTNO LOC DEPTNO LOC
---------- ------ --------- ------ ---------
ACCOUNTING 10 NEW YORK 10 NEW YORK
OPERATIONS 40 BOSTON 40 BOSTONDNAME이 바뀐 부서번호 20, 30은 제외되었다.
4. LOC와 DEPTNO 칼럼으로 JOIN
SELECT * FROM DEPT JOIN DEPT_TEMP USING (LOC, DEPTNO);LOC DEPTNO DNAME DNAME
-------- ------ ---------- ----------
NEW YORK 10 ACCOUNTING ACCOUNTING
DALLAS 20 RESEARCH R&D
CHICAGO 30 SALES MARKETING
BOSTON 40 OPERATIONS OPERATIONS5. DEPTNO와 DNAME 칼럼으로 JOIN
SELECT * FROM DEPT JOIN DEPT_TEMP USING (DEPTNO, DNAME);DEPTNO DNAME LOC LOC
------ ---------- --------- ---------
10 ACCOUNTING NEW YORK NEW YORK
40 OPERATIONS BOSTON BOSTONDNAME이 바뀐 부서번호 20, 30은 제외되었다.
USING 조건절 정리
- 기본 사용법: USING 절을 이용하면 특정 칼럼들만을 선택하여 EQUI JOIN을 수행할 수 있다.
- 제약사항: USING 절을 사용할 때는 칼럼 앞에 테이블 이름이나 별칭을 사용할 수 없다.
- 출력 결과: JOIN에 사용된 칼럼은 한 번만 출력되며, 나머지 칼럼은 각 테이블에서 가져온다.
- 예제 실행 결과: 다양한 칼럼 조합으로 USING 절을 사용한 예제와 그 결과를 통해 USING 절의 동작 방식을 확인할 수 있다.
ON 조건절
ON 조건절은 JOIN 서술부로, JOIN 조건을 명확하게 지정하여 이해하기 쉽게 만드는 데 사용된다. ON 조건절을 사용하면 칼럼명이 다르더라도 JOIN 조건을 설정할 수 있다. 이는 JOIN 조건과 비 JOIN 조건을 분리하는 데 도움이 된다.
- 특징
- JOIN 조건과 비 JOIN 조건을 분리하여 SQL문을 명확하게 만든다.
- 칼럼명이 다른 경우에도 JOIN을 할 수 있다.
- NATURAL JOIN과 달리 JOIN 칼럼에 테이블 별칭을 사용할 수 있다.
다음은 사원 테이블과 부서 테이블에서 사원 번호, 사원 이름, 소속 부서 코드, 소속 부서 이름 출력하는 예제이다.
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);-- 실행 결과
EMPNO ENAME DEPTNO DNAME
----- ------- ------ -----------
7369 SMITH 20 RESEARCH
7499 ALLEN 30 SALES
7521 WARD 30 SALES
7566 JONES 20 RESEARCH
7654 MARTIN 30 SALES
7698 BLAKE 30 SALES
7782 CLARK 10 ACCOUNTING
7788 SCOTT 20 RESEARCH
7839 KING 10 ACCOUNTING
7844 TURNER 30 SALES
7876 ADAMS 20 RESEARCH
7900 JAMES 30 SALES
7902 FORD 20 RESEARCH
7934 MILLER 10 ACCOUNTINGNATURAL JOIN vs. ON 조건절
- NATURAL JOIN: 같은 이름을 가진 모든 칼럼에 대해 자동으로 JOIN을 수행.
- ON 조건절: 임의의 JOIN 조건을 지정할 수 있고, 칼럼명이 다른 경우에도 사용 가능. ON 조건절을 사용하면 NATURAL JOIN에서는 사용할 수 없는 칼럼별칭을 사용할 수 있다.
WHERE 절과의 혼용
ON 조건절과 WHERE 절을 함께 사용하면 JOIN 조건과 필터 조건을 분리할 수 있다.
다음은 부서코드 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;-- 실행 결과
ENAME DEPTNO DEPTNO DNAME
------ ------ ------ ------
ALLEN 30 30 SALES
WARD 30 30 SALES
MARTIN 30 30 SALES
BLAKE 30 30 SALES
TURNER 30 30 SALES
JAMES 30 30 SALESON 조건절 + 데이터 검증 조건 추가
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;-- 실행 결과
ENAME MGR DEPTNO DNAME
------ ---- ------ ------
ALLEN 7698 30 SALES
WARD 7698 30 SALES
MARTIN 7698 30 SALES
TURNER 7698 30 SALES
JAMES 7698 30 SALES다중 테이블 JOIN
여러 테이블을 JOIN할 때도 ON 조건절을 사용하여 명확하게 JOIN 조건을 정의할 수 있다.
예제1
사원과 DEPT 테이블의 소속 부서명, DEPT_TEMP 테이블의 바뀐 부서명 정보 출력하는 쿼리문은 다음과 같다.
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME AS New_DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
JOIN DEPT_TEMP T ON (E.DEPTNO = T.DEPTNO);또는
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME AS New_DNAME
FROM EMP E, DEPT D, DEPT_TEMP T
WHERE E.DEPTNO = D.DEPTNO AND E.DEPTNO = T.DEPTNO;-- 실행 결과
EMPNO DEPTNO DNAME NEW_DNAME
----- ------ --------- ----------
7369 20 RESEARCH R&D
7499 30 SALES MARKETING
7521 30 SALES MARKETING
7566 20 RESEARCH R&D
7654 30 SALES MARKETING
7698 30 SALES MARKETING
7782 10 ACCOUNTING ACCOUNTING
7788 20 RESEARCH R&D
7839 10 ACCOUNTING ACCOUNTING
7844 30 SALES MARKETING
7876 20 RESEARCH R&D
7900 30 SALES MARKETING
7902 20 RESEARCH R&D
7934 10 ACCOUNTING ACCOUNTING위 문제의 다중 테이블의 JOIN의 순서는, 첫 번째 JOIN은 EMP와 DEPT 사이에서 이루어지고, 두 번째 JOIN은 그 결과와 DEPT_TEMP 사이에서 이루어진다.
-- 첫 번째 조인
SELECT E.EMPNO, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)-- 두 번째 조인
SELECT E.EMPNO, D.DEPTNO, D.DNAME, T.DNAME AS New_DNAME
FROM (
SELECT E.EMPNO, D.DEPTNO, D.DNAME
FROM EMP E
JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
) AS E_D
JOIN DEPT_TEMP T ON (E_D.DEPTNO = T.DEPTNO)예제2
GK 포지션의 선수별 연고지명, 팀명, 구장명을 출력하는 쿼리문은 다음과 같다.
SELECT P.PLAYER_NAME AS 선수명, P.POSITION AS 포지션, T.REGION_NAME AS 연고지명, T.TEAM_NAME AS 팀명, S.STADIUM_NAME AS 구장명
FROM PLAYER P JOIN TEAM T ON P.TEAM_ID = T.TEAM_ID
JOIN STADIUM S ON T.STADIUM_ID = S.STADIUM_ID
WHERE P.POSITION = 'GK'
ORDER BY 선수명;또는
SELECT P.PLAYER_NAME AS 선수명, P.POSITION AS 포지션, T.REGION_NAME AS 연고지명, T.TEAM_NAME AS 팀명, S.STADIUM_NAME AS 구장명
FROM PLAYER P, TEAM T, STADIUM S
WHERE P.TEAM_ID = T.TEAM_ID
AND T.STADIUM_ID = S.STADIUM_ID
AND P.POSITION = 'GK'
ORDER BY 선수명;-- 실행 결과
선수명 포지션 연고지명 팀명 구장명
----- ---- ------ -------- ----------
강성일 GK 대전 시티즌 대전월드컵경기장
권정혁 GK 울산 울산현대 울산문수경기장
권찬수 GK 성남 일화천마 성남종합운동장
김대희 GK 포항 스틸러스 포항스틸야드
김승준 GK 대전 시티즌 대전월드컵경기장
김용발 GK 전북 현대모터스 전주월드컵경기장
김운재 GK 수원 삼성블루윙즈 수원월드컵경기장
김정래 GK 전남 드래곤즈 광양전용경기장
김준호 GK 포항 스틸러스 포항스틸야드
김창민 GK 전북 현대모터스 전주월드컵경기장
김충호 GK 인천 유나이티드 인천월드컵경기장
남현우 GK 인천 유나이티드 인천월드컵경기장
박유석 GK 부산 아이파크 부산아시아드경기장CROSS JOIN
CROSS JOIN은 테이블 간의 조인 조건이 없는 경우 발생할 수 있는 모든 데이터 조합을 반환하는 JOIN 방식이다. E.F. Codd 박사가 언급한 일반 집합 연산자의 PRODUCT 개념에 해당하며, 두 테이블 간의 모든 행을 결합하여 조합을 생성한다. 이를 CARTESIAN PRODUCT 또는 CROSS PRODUCT라고도 한다. 결과는 두 테이블의 행 수를 곱한 만큼의 데이터 조합이 발생한다.
- 특징
- 모든 가능한 데이터 조합을 반환한다.
- 조인 조건이 없기 때문에 모든 행의 모든 조합이 생성된다.
- 결과는 두 테이블의 행 수의 곱
(M*N)만큼의 행을 포함한다. - 일반적으로 데이터 모델링에서 자주 사용되지는 않지만, 특정 경우에는 유용할 수 있습니다.
예제
예제 1
사원 번호와 사원 이름, 소속부서 코드와 소속부서 이름을 출력하는 쿼리문은 아래와 같다.
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;-- 실행 결과
ENAME DNAME
-------- ---------
ADAMS SALES
ADAMS RESEARCH
ADAMS OPERATIONS
ADAMS ACCOUNTING
ALLEN OPERATIONS
ALLEN RESEARCH
ALLEN ACCOUNTING
ALLEN SALES
BLAKE SALES
BLAKE OPERATIONS
BLAKE RESEARCH
BLAKE ACCOUNTING
CLARK SALES
CLARK RESEARCH
CLARK OPERATIONS
CLARK ACCOUNTINGCROSS JOIN에서 WHERE 절 사용
CROSS JOIN에서 WHERE 절을 사용하여 조건을 추가하면, INNER JOIN과 동일한 결과가 되므로 CROSS JOIN의 의미가 없어진다.
예제 2
WHERE 절을 사용한 CROSS JOIN은 다음과 같다.
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;또는
SELECT ENAME, DNAME
FROM EMP INNER JOIN DEPT
ON EMP.DEPTNO = DEPT.DEPTNO;-- 실행 결과
ENAME DNAME
------- ---------
SMITH RESEARCH
ALLEN SALES
WARD SALES
JONES RESEARCH
MARTIN SALES
BLAKE SALES
CLARK ACCOUNTING
SCOTT RESEARCH
KING ACCOUNTING
TURNER SALES
ADAMS RESEARCH
JAMES SALES
FORD RESEARCH
MILLER ACCOUNTING카타시안 곱
다음과 같이, JOIN문이 나타나지 않은 SELECT문은 카타시안 곱이 나타난다.
SELECT ENAME, DNAME
FROM EMP, DEPT
ORDER BY ENAME;CROSS JOIN의 활용 사례
CROSS JOIN은 일반적인 데이터 모델링에서는 많이 사용되지 않지만, 특정 상황에서는 유용하게 사용할 수 있다.
- 튜닝이나 리포트 작성 시: 특정 데이터를 조합하여 리포트를 작성하거나 성능 튜닝을 위해 고의적으로 CROSS JOIN을 사용할 수 있습니다.
- 데이터 웨어하우스: FACT 테이블과 JOIN하기 전에 DIMENSION 테이블의 모든 가능한 조합을 구하는 데 사용할 수 있다.
- CROSS JOIN은 두 테이블 간의 모든 가능한 데이터 조합을 반환하는 JOIN 방식이다.
- 일반적인 데이터 모델에서는 자주 사용되지 않지만, 특정 상황에서 유용할 수 있다.
- WHERE 절을 사용하여 조건을 추가하면 INNER JOIN과 동일한 결과가 되므로 주의해야 한다.
OUTER JOIN
OUTER JOIN은 INNER JOIN과 달리 JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다. 이를 통해, 두 테이블 간의 관계에서 일치하지 않는 데이터도 포함할 수 있다. OUTER JOIN에는 LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN이 있다.
- OUTER JOIN의 종류
- LEFT OUTER JOIN: 기준 테이블의 모든 행을 반환하며, 일치하는 값이 없는 경우 NULL을 반환한다.
- RIGHT OUTER JOIN: JOIN되는 테이블의 모든 행을 반환하며, 일치하는 값이 없는 경우 NULL을 반환한다.
- FULL OUTER JOIN: 두 테이블의 모든 행을 반환하며, 일치하는 값이 없는 경우 NULL을 반환한다.
1️⃣ LEFT OUTER JOIN
LEFT OUTER JOIN은 기준이 되는 좌측 테이블의 모든 데이터를 반환하며, 우측 테이블에서 일치하는 데이터가 없으면 NULL을 반환한다.
예제1
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;STADIUM_NAME STADIUM_ID SEAT_COUNT HOMETEAM_ID TEAM_NAME
------------------- ---------- ---------- ----------- ----------
울산문수경기장 C04 46102 K01 울산현대
수원월드컵경기장 B04 50000 K02 삼성블루윙즈
포항스틸야드 C06 25000 K03 스틸러스
인천월드컵경기장 B01 35000 K04 유나이티드
전주월드컵경기장 D03 28000 K05 현대모터스
부산아시아드경기장 C02 30000 K06 아이파크
광양전용경기장 D01 20009 K07 드래곤즈
성남종합운동장 B02 27000 K08 일화천마
서울월드컵경기장 B05 66806 K09 FC서울
대전월드컵경기장 D02 41000 K10 시티즌
창원종합운동장 C05 27085 K11 경남FC
광주월드컵경기장 A02 40245 K12 광주상무
강릉종합경기장 A03 33000 K13 강원FC
제주월드컵경기장 A04 42256 K14 제주유나이티드FC
대구월드컵경기장 A05 66422 K15 대구FC
안양경기장 F05 20000 NULL NULL
마산경기장 F04 20000 NULL NULL
일산경기장 F03 20000 NULL NULL
부산시민경기장 F02 30000 NULL NULL
대구시민경기장 F01 30000 NULL NULL홈팀이 없는 경기장도 포함되어 있다.
2️⃣ RIGHT OUTER JOIN
RIGHT OUTER JOIN은 기준이 되는 우측 테이블의 모든 데이터를 반환하며, 좌측 테이블에서 일치하는 데이터가 없으면 NULL을 반환한다.
예제1
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, D.LOC
FROM EMP E
RIGHT JOIN DEPT D ON E.DEPTNO = D.DEPTNO;-- 실행 결과
ENAME DEPTNO DNAME LOC
-------- ------ ----------- --------
CLARK 10 ACCOUNTING NEW YORK
KING 10 ACCOUNTING NEW YORK
MILLER 10 ACCOUNTING NEW YORK
JONES 20 RESEARCH DALLAS
FORD 20 RESEARCH DALLAS
ADAMS 20 RESEARCH DALLAS
SMITH 20 RESEARCH DALLAS
SCOTT 20 RESEARCH DALLAS
WARD 30 SALES CHICAGO
TURNER 30 SALES CHICAGO
ALLEN 30 SALES CHICAGO
JAMES 30 SALES CHICAGO
BLAKE 30 SALES CHICAGO
MARTIN 30 SALES CHICAGO
NULL 40 OPERATIONS BOSTON사원이 없는 부서(부서번호 40)도 포함되어 있다.
3️⃣ FULL OUTER JOIN
FULL OUTER JOIN은 좌측과 우측 테이블의 모든 데이터를 결합하여 반환한다. 일치하지 않는 행은 NULL로 채워진다.
예제
DEPT 테이블과 DEPT_TEMP 테이블을 FULL OUTER JOIN으로 결합하는 쿼리문은 아래와 같다.
UPDATE DEPT_TEMP SET DEPTNO = DEPTNO + 20;
SELECT * FROM DEPT_TEMP;-- DEPT_TEMP 테이블
DEPTNO DNAME LOC
------ ------------ ---------
30 ACCOUNTING NEW YORK
40 R&D DALLAS
50 MARKETING CHICAGO
60 OPERATIONS BOSTON예제1
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;또는, LEFT JOIN과 RIGHT JOIN을 사용하여 구현하면 아래와 같다.
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;-- 실행 결과
DEPTNO L.DNAME L.LOC R.DEPTNO R.DNAME R.LOC
------ ------------ --------- -------- ------------ ---------
10 ACCOUNTING NEW YORK NULL NULL NULL
20 RESEARCH DALLAS NULL NULL NULL
30 SALES CHICAGO 30 ACCOUNTING NEW YORK
NULL NULL NULL 40 R&D DALLAS
NULL NULL NULL 50 MARKETING CHICAGO
NULL NULL NULL 60 OPERATIONS BOSTONDEPT 테이블과 DEPT_TEMP 테이블 모두에 존재하지 않는 부서 정보도 포함되어 있다.
- LEFT OUTER JOIN: 기준 테이블의 모든 데이터를 반환하며, 일치하지 않는 데이터는 NULL로 채워짐.
- RIGHT OUTER JOIN: JOIN되는 테이블의 모든 데이터를 반환하며, 일치하지 않는 데이터는 NULL로 채워짐.
- FULL OUTER JOIN: 두 테이블의 모든 데이터를 결합하여 반환하며, 일치하지 않는 데이터는 NULL로 채워짐.
OUTER JOIN은 데이터 간의 관계를 명확하게 이해하고, 일치하지 않는 데이터를 포함하여 결과를 반환할 때 유용하다. 각각의 JOIN 유형을 적절히 사용하여 필요한 데이터를 효율적으로 추출할 수 있다.
INNER vs OUTER vs CROSS JOIN 비교
1️⃣ INNER JOIN
INNER JOIN은 두 테이블에서 일치하는 키 값을 가진 행만 반환한다. 예를 들어, TAB1과 TAB2가 있다고 가정하자.
-- TAB1
키 값 | 데이터
-----------
B | Data1_B
C | Data1_C
D | Data1_D
E | Data1_E-- TAB1
키 값 | 데이터
-----------
A | Data2_A
B | Data2_B
C | Data2_C아래의 쿼리문을 실행시키면 결과는 아래와 같다.
SELECT TAB1.키 값, TAB1.데이터, TAB2.데이터
FROM TAB1
INNER JOIN TAB2 ON TAB1.키 값 = TAB2.키 값;-- 실행 결과
키 값 | 데이터(TAB1) | 데이터(TAB2)
-------------------------------
B | Data1_B | Data2_B
C | Data1_C | Data2_C2️⃣ LEFT OUTER JOIN
LEFT OUTER JOIN은 좌측 테이블(TAB1)을 기준으로, 우측 테이블(TAB2)에서 일치하지 않는 경우 NULL을 반환한다.
SELECT TAB1.키 값, TAB1.데이터, TAB2.데이터
FROM TAB1
LEFT OUTER JOIN TAB2 ON TAB1.키 값 = TAB2.키 값;-- 실행 결과
키 값 | 데이터(TAB1) | 데이터(TAB2)
-------------------------------
B | Data1_B | Data2_B
C | Data1_C | Data2_C
D | Data1_D | NULL
E | Data1_E | NULL3️⃣ RIGHT OUTER JOIN
RIGHT OUTER JOIN은 우측 테이블(TAB2)을 기준으로, 좌측 테이블(TAB1)에서 일치하지 않는 경우 NULL을 반환한다.
SELECT TAB1.키 값, TAB1.데이터, TAB2.데이터
FROM TAB1
RIGHT OUTER JOIN TAB2 ON TAB1.키 값 = TAB2.키 값;키 값 | 데이터(TAB1) | 데이터(TAB2)
-------------------------------
A | NULL | Data2_A
B | Data1_B | Data2_B
C | Data1_C | Data2_C4️⃣ FULL OUTER JOIN
FULL OUTER JOIN은 양쪽 테이블의 모든 데이터를 결합하여 반환한다. 일치하지 않는 행은 NULL로 채워진다.
SELECT TAB1.키 값, TAB1.데이터, TAB2.데이터
FROM TAB1
FULL OUTER JOIN TAB2 ON TAB1.키 값 = TAB2.키 값;키 값 | 데이터(TAB1) | 데이터(TAB2)
-------------------------------
A | NULL | Data2_A
B | Data1_B | Data2_B
C | Data1_C | Data2_C
D | Data1_D | NULL
E | Data1_E | NULL5️⃣ CROSS JOIN
CROSS JOIN은 두 테이블의 가능한 모든 행의 조합을 반환한다.
SELECT TAB1.키 값, TAB1.데이터, TAB2.키 값, TAB2.데이터
FROM TAB1
CROSS JOIN TAB2;-- 실행 결과
TAB1.키 값 | TAB1.데이터 | TAB2.키 값 | TAB2.데이터
----------------------------------------------
B | Data1_B | A | Data2_A
B | Data1_B | B | Data2_B
B | Data1_B | C | Data2_C
C | Data1_C | A | Data2_A
C | Data1_C | B | Data2_B
C | Data1_C | C | Data2_C
D | Data1_D | A | Data2_A
D | Data1_D | B | Data2_B
D | Data1_D | C | Data2_C
E | Data1_E | A | Data2_A
E | Data1_E | B | Data2_B
E | Data1_E | C | Data2_C6️⃣ 요약
- INNER JOIN: 두 테이블에서 일치하는 키 값을 가진 행만 반환.
- LEFT OUTER JOIN: 좌측 테이블의 모든 행을 반환하고, 일치하지 않는 경우 NULL 반환.
- RIGHT OUTER JOIN: 우측 테이블의 모든 행을 반환하고, 일치하지 않는 경우 NULL 반환.
- FULL OUTER JOIN: 두 테이블의 모든 행을 반환하고, 일치하지 않는 경우 NULL 반환.
- CROSS JOIN: 두 테이블의 가능한 모든 행의 조합을 반환.
신입사원...