[SQLD] 20. 표준조인 (STANDARD JOIN)

1. 표준조인(STANDARD JOIN)

STANDARD SQL 개요

• ANSI/ISO 표준 SQL에서 규정한 INNER JOIN , NATURAL JOIN , USING 조건절 , ON조걸절 , CROSS JOIN, OUTER JOIN 문법을 통해
  사용자는 테이블간의 JOIN 조건을 FROM 절에서 명시적으로 정의할수있다.
• SQL - 관계형 데이터베이스를 유일하게 접속할수있는 언어
• ANSI/ISO SQL3 - 벤더별로 상이했던 SQL문법을 필여한 기능을 정리하고 호환 가능한 여러기준으로 제정한것 (Oracle 8i/9i 최초)

대표적인 ANSI/ISO 표준 SQL 기능

• STANDARD JOIN 기능 추가 (CROSS , OUTER JOIN등 새로운 FROM절 기능들)
• SCALAR SUBQUERY , TOP-N QUERY 등의 새로운 SUBSQUERY 기능들
• ROLLUP, CUBE , CROUPING SETS 등의 새로운 리포트 기능
• WINDOW FUNCTION 같은 새로운 개념의 분석 기능들

가) 일반 집합 연산자.

일반 집합 연산자를 현재의 SQL과 비교

1. UNION 연산은 UNION 기능으로, (수학적 합집합 , UNION ALL은 공통집합을 중복해서 보여주기때문에 정렬 작업이 없는 장점을 가진다.)
2. INTERSECTION 연산은 INTERSECT 기능으로, (수학의 교집합, 두 집합의 공통집합을 추출한다.)
3. DIFFERENCE 연산은 EXCEPT(ORACLE MINUS)기능으로, (대다수 벤더는 EXCEPT [SQL 표준] ORACLE은 MINUS 사용)
4. PRODUCT 연산은 CROSS JOIN 기능으로 구현되었다. (양쪽 집합의 M*N건의 데이터 조합 , CARTESIAN PRODUCT 라고도 표현 )

나) 순수 관계 연산자.

5. SELECT 연산은 WHERE 절로 구현되었다.
6. PROJECT 연산은 SELECT 절로 구현되었다.
7. (NATURAL) JOIN 연산은 다양한 JOIN 기능으로 구현되었다.
8. DIVIDE 연산은 현재 상용하지 않는다.

2. FROM 절 JOIN 형태

ANSI/ISO SQL에서 표시하는 FROM 절의 JOIN 형태

• INNER JON
• NATURAL JOIN
• USING 조건절
• ON 조건절
• CROSS JOIN
• OUTER JOIN

3. INNER JOIN

*JOIN 조건에서 동일한 값이 있는 행만 반환한다
<예제>
WHERE 절 JOIN 조건

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

위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다.

FROM 절 JOIN 조건

SELECT EMP.DEPTNO, EMPNO, ENAME, DNAME 
FROM EMP INNER JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO; 
--INNER는 JOIN의 디폴트 옵션으로 아래 SQL문과 같이 생략 가능하다. 

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

4. NATURAL JOIN

• 두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행한다
• USING 조건절, ON 조건절, WHERE 절에서 JOIN 조건을 정의할 수 없다.
• SQL Server은 미지원

<예제>

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

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

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

아래 '*' 와일드카드처럼 별도의 칼럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼 들이 다른 칼럼보다 먼저 출력된다

SELECT * 
FROM EMP NATURAL JOIN DEPT;

5. USING 조건절

• FROM 절의 USING 조건절을 이용하면 같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 선택적으로 EQUI JOIN을 할 수가 있다.
• SQL Server은 미지원

<예제>

SELECT * 
FROM DEPT JOIN DEPT_TEMP USING (DEPTNO);

SELECT a.DEPTNO, a.DNAME, a.LOC, b.DNAME, b.LOC 
FROM DEPT a JOIN DEPT b USING (DEPTNO); 

-- ORA-25154: 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);

가) WHERE 절과의 혼용

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 조건절 + 데이터 검증 조건 추가

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

--위 SQL과 아래 SQL은 같은 결과를 얻을 수 있다. 

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

다) ON 조건절 예제

SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME 
FROM TEAM JOIN STADIUM ON TEAM.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

라) 다중 테이블 JOIN

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

• 테이블 간 JOIN 조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 말한다

   

SELECT COUNT(*) 
FROM EMP ; -- 14건 

SELECT COUNT(*) 
FROM DEPT ; -- 4건 

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

8. OUTER JOIN

• INNER(내부) JOIN과 대비하여 OUTER(외부) JOIN이라고 불리며, JOIN 조건에서 동일한 값이 없는 행도 반환할 때 사용할 수 있다.
• OUTER는 생략 가능한 키워드

가) LEFT OUTER JOIN

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

나) RIGHT OUTER JOIN

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

다) FULL OUTER JOIN

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

9. INNER vs OUTER vs CROSS JOIN 비교

-- 퀴즈 -- 
다음중 결과값이 다른 쿼리는?

WITH T1 (idx , col1 , col2 ) AS 
( SELECT '1','10','100' 
  FROM DUAL UNION ALL 
  
  SELECT '2','20','200' 
  FROM DUAL UNION ALL 
  
  SELECT '3','30','300' 
  FROM DUAL UNION ALL 
  
  SELECT '4','40','400' 
  FROM DUAL UNION ALL 
  
  SELECT '5','50','500' 
  FROM DUAL ), T2 ( idx , col1 , co2 ) AS 
  ( SELECT '1','10','100' 
  FROM DUAL UNION ALL 
  
  SELECT '2','20','200' 
  FROM DUAL UNION ALL 
  
  SELECT '3','30','300' 
  FROM DUAL ) 
  
 /*-- 1번쿼리 
 SELECT * 
 FROM T1 a LEFT OUTER JOIN T2 b 
    ON a.idx = b.idx 
    AND a.idx IN (1,2) 
    AND b.col1 = 10 
*/ ----------------------------- 

/*-- 2번쿼리 
SELECT * 
FROM T1 a LEFT OUTER JOIN T2 b 
   ON a.idx = b.idx 
WHERE a.idx IN (1,2) 
   AND b.col1 = 10 
*/ ----------------------------- 

/*-- 3번쿼리 
SELECT * 
FROM T1 a LEFT OUTER JOIN T2 b 
   ON a.idx = b.idx 
   AND a.idx IN (1,2) 
WHERE b.col1 = 10 
*/ ----------------------------- 

/*-- 4번 쿼리 
SELECT * 
FROM T1 a , T2 b 
WHERE a.idx = b.idx(+) 
   AND a.idx in (1,2) 
   AND b.col1 = 10 
*/ -------------------------------

 

정리

------1. 표준조인
----가. 일반 집합 연산자(UNION, INERTSECTION, DIFFERENCE, PRODUCT)
--UNION : 합집합
--INTERSECTION : 교집합
--DIFFERENCE : 차집합
--PRODUCT : CROSS JOIN 행렬곱
----나. 순수 관계 연산자(SELECT, PROJECT, (NATURAL) JOIN, DIVIDE

------2. FROM절 JOIN형태
----1. INNER JOIN
--동일한 값이 있는 행만 반환한다.
--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;

----2. NATURAL JOIN
--두 테이블 간의 동일한 이름을 갖는 모든 칼럼들에 대해 EQUI(=) JOIN을 수행
--NATURAL JOIN에 사용된 열은 검색할 수 없음
-- *와일드카드처럼 별도의 컬럼 순서를 지정하지 않으면 NATURAL JOIN의 기준이 되는 칼럼들을 알 수 있다.
SELECT *
FROM EMP NATURAL JOIN DEPT;

----3. USING 조건절
--FROM절의 USING조건절을 이용하면 같은 이름을 가진 컬럼들 중에서 원하는 컬럼에 대해서만 선택적으로
--EQUI JOIN 할 수 있다.
SELECT *
FROM DEPT JOIN DEPT_TEMP USING(DEPTNO);

--USING절의 열 부분은 식별자를 가질 수 없음
--즉, ALIAS나 테이블이름과 같은 접두사를 붙일 수 없다.
SELECT A.DEPTNO, A.DNAME, A.LOC, B.DNAME, B.LOC
FROM DEPT A JOIN DEPT B USING(DEPTNO);

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

--DNAME컬럼을 조인조건으로 USING절 수행
SELECT *
FROM DEPT JOIN DEPT_TEMP USING(DNAME);

----4. ON 조건절
--JOIN서술부(ON조건절)와 NON-JOIN서술부(WHERE조건절) 분리해서 이해가 쉬우며
--컬럼명이 다르더라도 JOIN조건을 사용할 수 있다.
SELECT E.EMPNO, E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO);

--WHERE절과 혼용
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조건절 + 데이터 검증조건 추가
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;

--ON 조건절 예제
SELECT TEAM_NAME, TEAM.STADIUM_ID, STADIUM_NAME
FROM TEAM JOIN STADIUM ON (TEAM.STADIUM_ID = STADIUM.STADIUM_ID)
ORDER BY STADIUM_ID;

SELECT TEAM_NAME, TEAM_ID, STADIUM_NAME
FROM TEAM JOIN STADIUM ON (TEAM.TEAM_ID = STADIUM.HOMETEAM_ID)
ORDER BY TEAM_ID;

--다중 테이블 조인
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);
    
----5. CROSS JOIN
--PRODUCT의 개념으로 테이블간 JOIN조건이 없는 경우 생길 수 있는 모든 데이터의 조합을 의미
--M건의 테이블과 N건의 테이블의 CROSS JOIN은 M*N건의 테이블이 생성된다.
--실무에서는 크게 사용할 일이 없다.
SELECT ENAME, DNAME
FROM EMP CROSS JOIN DEPT
ORDER BY ENAME;

----6. OUTER JOIN
--JOIN조건에서 동일한 값이 없는 행도 반환할 때 사용한다.
--TAB1과 TAB2가 있을 때 TAB1이 기준이고 
--1과 2를 비교해서 2의 JOIN컬럼에서 같은 값이 있을 때 해당데이터 가져오고
--같은 값이 없을 때는 TAB2에서 가져오는 컬럼들은 NULL값으로 채운다.(LEFT OUTER JOIN)
--LEFT OUTER 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;

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

--FULL OUTER JOIN
UPDATE DEPT_TEMP SET DEPTNO = DEPTNO +20;
SELECT *
FROM DEPT_TEMP;

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

 

 

[출처]

wiki.gurubee.net/pages/viewpage.action?pageId=27427158