오라클SQL JOIN02

조인문장 및 집합 연산자

1. ANSI 조인

ANSI는 미국 표준으로 오라클도 ANSI 표준을 따르고 있지만, 오라클만의 문법을 갖고 있습니다. ANSI SQL 표준을 안다면 다른 DB SQL을 사용할 때 도움이 됩니다.

CROSS JOIN

두 테이블을 상호간의 조합을 생성합니다. 카타시안 곱과 동일합니다. (M X N)

--[ANSI]
--EMP, DEPT 테이블의 CROSS JOIN 결과 조회
SELECT e.ename, d.dname
FROM emp e CROSS JOIN dept d;

--[Oracle]
SELECT e.ename, d.dname
FROM emp e, dept d;

오라클 JOIN에서 WHERE절 즉, 조건을 없이 쓴 것과 동일합니다.

NATURAL JOIN

두 테이블의 동일한 이름을 가진 컬럼을 기준으로 조인합니다. WHERE절에 조건을 주는 것과 동일합니다. 단, 조인조건으로 사용한 컬럼 앞에는 테이블 이름이나 테이블 별칭을 붙일 수 없습니다.

--[ANSI]
--EMP. DEPT 테이블을 이용해 사원이름, 부서번호, 부서명 조회
SELECT e.ename, deptno, d.dname
FROM emp e NATURAL JOIN dept d;

--[Oracle]
SELECT e.ename, e.deptno, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

ANSI표준은 조인한 컬럼명에 테이블명을 붙일 순 없지만, Oracle 문법에서는 테이블명을 붙여야 합니다.

JOIN USING

Natural Join은 이름과 데이터 유형이 일치하는 모든 칼럼을 사용하여 테이블을 조인하지만, USING절은 조인할 특정 컬럼을 선택할 수 있습니다. 두 테이블이 있을 때, 3개의 컬럼이 같을 때 두 개만 조인하고 싶을 때 사용합니다. USING절에 참조되는 컬럼은 SQL문 어디에서도 테이블명이나 별칭을 가질 수 없습니다.

--EMP, DEPT 테이블을 이용해서 사원이름, 부서번호, 부서명 조회
SELECT e.ename, deptno, d.dname
FROM emp e JOIN dpet d USING(deptno)
ORDER BY e.ename DESC;**

JOIN ON

JOIN ON 구문은 조인 조건이 ‘=’이 아닌 경우에도 사용할 수 있습니다. 그래서 가장 많이 쓰이는 조인구문이라 합니다. 조인 조건은 ON 다음에 사용합니다.

-- 사원이름, 연봉, 연봉의 등급 조회
SELECT e.ename, e.sal, s.grade
FROM emp e JOIN SALGRADE s
ON (e.sal BETWEEN s.local AND s.hisal)
ORDER BY e.sal DESC;

2. ANSI LEFT/RIGHT/FULL OUTER JOIN

• INNER JOIN : ANSI JOIN에서 두 테이블을 조인하여 일치하는 열만 반환하는 조인
• LEFT OUTER JOIN : 두 테이블을 조인할 때, 일치하지 않는 조인을 왼쪽 테이블 행을 반환하는 조인
• RIGHT OUTER JOIN : 두 테이블 조인할 때, 일치하지 않는 조인을 오른쪽 테이블 행을 반환하는 조인
• FULL OUTER JOIN : 두 테이블을 조인해서 내부 조인의 결과와 함께 왼쪽, 오른쪾 조인의 결과를 모두 반환하는 조인

--LEFT OUTER JOIN
SELECT d.dname, d.loc_code, l.loc_code, l.city
FROM dept d LEFT OUTER JOIN locations l ON (d.loc_code = l.loc_code);

SELECT d.dname, d.loc_code, l.loc_code, l.city
FROM dept d, locations l
WHERE d.loc_code = l.loc_code(+);

LEFT OUTER JOIN을 기준으로 왼쪽에 있는 값을 모두 보겠다는 소리입니다. 따라서 dept 테이블의 정보를 모두 조회하겠다는 소리입니다. 그러므로 Oracle문법에서l.loc_code(+)로 사용하여 d.loc_code를 기준으로 l.loc_code를 모두 JOIN 하게 됩니다. 만약 일치하는 값이 없으면 NULL로 나타냅니다.

--RIGHT OUTER JOIN
SELECT d.dname, d.loc_code, l.loc_code, l.city
FROM dept d RIGHT OUTER JOIN locations l ON (d.loc_code = l.loc_code);

SELECT d.dname, d.loc_code, l.loc_code, l.city
FROM dept d, locations l
WHERE d.loc_code(+) = l.loc_code;

RIGHT OUTER JOIN을 기준으로 오른쪽 값을 모두 보겠다는 소리입니다. LEFT OUTER JOIN과 반대입니다.

--FULL OUTER JOIN
SELECT d.dname, d.loc_code, l.loc_code, l.city
FROM dept d FULL OUTER JOIN locations l ON (d.loc_code = l.loc_code);

FULL OUTER JOIN는 조인 컬럼 기준으로 양쪽 테이블을 모두 보는 것입니다. 그런데 오라클 문법에서는 양쪽에 (+)를 붙이는 것은 불가능합니다. 이럴 때 ANSI 표준인 FULL OUTER JOIN을 사용해야 합니다.

3. 집합 연산자 개념

SET 연산자는 두 개 이상의 질의 결과를 하나의 결과로 통합하여 보여줍니다. 모든 SET 연산자는 같은 우선순위를 갖지만, ()괄호를 통해서 순서를 표현할 수 있습니다.

SQL의 집합 연산자로 UNION, UNION ALL, INTERSECT, MINUS가 있습니다.

• INTERSECT : 교집합 연산자로 각 테이블 조회 결과 중 공통인 것만 뽑아서 보여줍니다.
• MINUS : 차집합 연산자로 각 테이블 조회 결과 중 MINUS 연산자 기준으로 왼쪽 결과에서 오른쪽 결과를 뺀 컬럼을 보여줍니다.
• UNION : 합집합 연산자로 각 테이블 조회 결과 모든 결과 중 중복을 제거해서 보여줍니다.
• UNION ALL: UNION과 같은 연산을 하지만 중복을 제거하지 않고 모두 보여줍니다.

--UNION
SELECT player_name, team_id, position
FROM player
WHERE position = 'TC'
UNION
SELECT player_name, team_id, position
FROM player
WHERE team_id = 'K10'
ORDER BY 1;

--UNION ALL
--중복 허용
SELECT player_name, team_id, position
FROM player
WHERE position = 'TC'
UNION ALL
SELECT player_name, team_id, position
FROM player
WHERE team_id = 'K10'
ORDER BY 1;

--INTERSECT
SELECT player_name, team_id, position
FROM player
WHERE position = 'TC'
INTERSECT
SELECT player_name, team_id, position
FROM player
WHERE team_id = 'K10'
ORDER BY 1;

--MINUS
SELECT player_name, team_id, position
FROM player
WHERE position = 'TC'
MINUS
SELECT player_name, team_id, posi	tion
FROM player
WHERE team_id = 'K10'
ORDER BY 1;

SET 연산자 지침

SELECT 목록에 있는 표현식의 개수 즉, 컬럼 수가 일치해야 합니다. 또한 데이터 유형 역시 일치해야 합니다.