[SQL]여러 테이블의 데이터를 조인해서 출력하기(ON절, USING절, NATURAL JOIN, LEFFT/RIGHT OUTER JOIN)

join은 크게 oracle join과 ANSI/ISO SQL:1999 standard이 있다

이전에 포스팅한 EQUI JOIN, NON EQUI JOIN, OUTER JOIN, SELF JOIN과 같은 문법을 ORACLE JOIN이라 하고, 오늘 배울 ON절, USING절, NATRUAL JOIN, LEFT/RIGHT OUTER JOIN은 모두 ORALCE 9i 버전부터 지원하는 문법. 두 문법은 작성 SQL의 차이만 있을 뿐 성능의 차이는 없음

 

1. ON절

ON절은 ORACLE 작성법에서 사용했던 WHERE절을 대신해 JOIN에 대한 조건을 쓸 때 사용한다. 예를 들어 사원의 이름과 부서 위치를 출력하는 경우, 두 방법의 작성법을 비교하면 아래와 같다.

-- oracle EQUI JOIN 

SELECT e.ename, d.loc 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

-- ON절을 사용한 JOIN 

SELECT e.ename, d.loc 
FROM emp e JOIN dept d 
ON (**e.deptno = d.deptno);

ORALCE JOIN의 경우 일반적인 EQUI 조인일 때 FROM절에서 두 테이블을 콤마(.)로 연결했으며 이 JOIN에 대한 조건은 WHERE절에 입력했다. 하지만 ON절을 사용할 경우 FROM에서 두 테이블 사이에 'JOIN'을 입력해야하며, 이에 대한 JOIN조건은 ON절에서 괄호안에 나타내야 한다.

위 처럼 ON절을 사용하여 직업이 SALESMAN인 사원들의 이름과 직업, 월급, 부서 위치를 출력하면 아래와 같이 쿼리를 작성한다.

SELECT e.ename as 이름, e.job as 직업, e.sal as 월급, d.loc as 부서위치 
FROM emp e JOIN dept d 
ON (e.deptno = d.deptno)
WHERE e.job = 'SALESMAN';

직업이 'SALESMAN'인 조건은 JOIN조건이 아닌 검색 조건이기 때문에 WHERE절에 작성한다.

여러 개의 테이블을 조인할 때 조인 작성법의 차이는 다음과 같다

-- oracle EQUI JOIN 
SELECT e.ename, d.loc 
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.dpetno 
AND e.sal BETWEEN s.losal AND s.hisal;

-- ON절을 사용한 JOIN 
SELECT e.ename, d.loc, s.grade 
FROM emp e 
JOIN dept d ON (e.deptno = d.deptno) 
JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal);

위와 같이 여러 개의 테이블을 조인할 때는 연결고리가 되는 JOIN 조건을 테이블의 개수에서 하나를 차감해서 기술한다. (JOIN 조건의 개수 = 테이블 개수 - 1)

 

2. USING절

USING절은 ON절과 마찬가지로 WHERE절을 대신해 JOIN에 대한 조건을 쓸 때 사용한다. 다만 ON절과는 사용하는 방법이 다르다.

예를 들어 사원의 이름과 부서 위치를 출력하는 경우, WHERE절을 쓸 때, ON절을 쓸 때, USING절을 쓸 때의 작성법을 비교하면 아래와 같다.

-- WHERE절 
SELECT e.ename, d.loc 
FROM emp e, dept d 
WHERE e.deptno = d.deptno;

-- ON절 
SELECT e.ename, d.loc 
FROM emp e JOIN dept d 
ON (e.deptno = d.deptno);

-- USING절 
SELECT e.ename, d.loc 
FROM emp e JOIN dept d 
USING (deptno);

예시를 통해 알 수 있듯이 FROM절에서 JOIN을 쓰는 점에서 ON과 같지만 USING절에는 JOIN조건을 만족하는 컬럼명만을 괄호안에 입력한다. USING절에는 반드시 괄호를 사용해야하고, 만약 사용하지 않는 경우 아래와 같은 에러가 발생한다.

ORA-00906: missing left parenthesis

USING절을 사용하여 직업이 SALESMAN인 사원들의 이름과 직업, 월급, 부서 위치를 출력하면 아래와 같이 쿼리를 작성한다.

SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e JOIN dept d 
USING (deptno) 
WHERE e.job = 'SALESMAN';

USING절을 사용하여 여러 개의 테이블을 조인하려면 다음과 같이 emp와 조인하는 테이블명 다음에 USING절을 사용한다.

SELECT e.ename, d.loc, s.grade 
FROM emp e 
JOIN dept d USING (deptno) 
JOIN salgrade s ON (e.sal BETWEEN s.losal AND s.hisal);

여기서 between문의 경우 등호관계가 아니기 때문에 USING절 대신 ON절을 사용해 나타낸다.

 

3. NATURAL JOIN

조인 조건을 주지 않고 EQUI조인을 하는 경우 NATURAL JOIN을 사용한다.

NATURAL JOIN의 경우 두 테이블에 둘 다 존재하는 동일한 컬럼을 기반으로 암시적인 JOIN을 수행한다.예를 들어 직업이 SALESMAN인 사원들의 이름과 직업, 월급, 부서 위치를 출력하면 아래와 같이 쿼리를 작성한다.

SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e NATURAL JOIN dept d 
WHERE e.job = 'SALESMAN';

위의 예시에서 두 테이블에 모두 존재하는 DEPTNO를 오라클이 알아서 찾아 이를 이용하여 JOIN을 수행한다. 이때 WHERE절에 조건을 기술할 때 JOIN의 연결고리가 되는 컬럼은테이블명을 테이블 별칭 없이 기술해야한다.

아래의 예시를 통해 더 자세히 알 수 있다.

SELECT e.ename as 이름, e.job as 직업, e.sal as 월급, d.loc as 부서위치 
FROM emp e NATURAL JOIN dept d 
WHERE e.job = 'SALESMAN' AND e.deptno = 30;

ORA-25155: column used in NATURAL join cannot have qualifier

위의 쿼리에서 JOIN의 연결고리가 되는 컬럼인 deptno가 dept테이블의 별칭인 'd'와 함께 기술된 것을 볼 수 있다. 하지만 위와 같이 실행할 경우 에러가 발생한다. 이 에러는 NATURAL JOIN에 사용된 열은 식별자를 가질 수 없다는 에러로, 이를 해결하기 위해서는 deptno의 테이블인 dept에 대한 별칭 없이 사용해야한다.

-- 올바른 사용법 

SELECT e.ename as 이름, e.job as 직업, e.sal as 월급, d.loc as 부서위치 
FROM emp e NATURAL JOIN dept d 
WHERE e.job = 'SALESMAN' AND deptno = 30;

 

반응형

728x90

 

4. LEFT / RIGHT OUTER JOIN

EUQI JOIN으로 JOIN이 안 되는 결과를 출력하기 위해서는 LEFT / RIGHT / FULL OUTER JOIN을 사용한다. 참고로 이전 포스팅에서 OUTER JOIN을 다루면서 LEFT/RIGHT OUTER JOIN에서도 다룬 적이 있다.

[SQL] JOIN - EQUI JOIN, NON EQUI JOIN, OUTER JOIN, SELF JOIN

1) RIGHT OUTER JOIN

먼저 ORACLE OUTER JOIN과 1999ANSI/ISO JOIN SQL 작성법의 차이는 다음과 같다

-- oracle RIGHT OUTER JOIN 

SELECT e.ename, d.loc 
FROM emp e, dept d 
WHERE e.deptno (+) = d.deptno;

-- ANSI/ISO RIGHT OUTER JOIN 

SELECT e.ename, d.loc 
FROM emp e RIGHT OUTER JOIN dept d 
ON (e.deptno = d.deptno);

ORACLE JOIN의 경우 FROM에는 콤마(.)로 테이블을 나열하고, WHERE절에서 RIGHT OUTER JOIN임을 나타내지만, ANSI/ISO 에서는 FROM에서 RIGHT OUTER JOIN임을 나타내고, ON절에 JOIN조건을 입력한다는 차이를 알 수 있다.

예를 들어 사원의 이름, 직업, 월급, 부서 위치를 출력한다면 아래와 같이 쿼리를 작성할 수 있다.

SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e RIGHT OUTER JOIN dept d 
ON (e.deptno = d.deptno);

위의 결과에서 마지막 행에서 ENAME과 JOB이 NULL인 이유는 EMP테이블에서 BOSTON에서 근무하는 사원의 정보가 없기 때문이다. 이 처럼 RIGHT OUTER JOIN은 오른쪽에 위치한 테이블의 모든 정보를 가져오되, 왼쪽 테이블에서 JOIN조건을 만족하는 정보가 없는 경우에는 NULL을 출력한다.

 

2) LEFT OUTER JOIN

먼저 ORACLE OUTER JOIN과 1999 ANSI/ISO JOIN SQL 작성법의 차이는 위에서 봤던 RIGHT OUTER JOIN과 마찬가지로 FROM, WHERE절, ON절에서 확인할 수 있다.

-- ORACLE LEFT OUTER JOIN 

SELECT e.ename, d.loc 
FROM emp e, dept d 
WHERE e.deptno = d.deptno(+);

-- ANSI/ISO LEFT OUTER JOIN 

SELECT e.ename, d.loc 
FROM emp e LEFT OUTER JOIN dept d 
ON (e.deptno = d.deptno);

예를 들어 사원의 이름, 직업, 월급, 부서 위치를 출력한다면 아래와 같이 쿼리를 작성할 수 있다. 여기서 LEFT OUTER JOIN이 제대로 이루어지는지 확인해보기 위해 DEPT 테이블에 없는 부서 번호 50번을 다음과 같이 사원 테이블에 입력한다.

INSERT INTO emp(empno, ename, sal, job, deptno) VALUES(8282, 'JACK', 3000, 'ANALYST', 50);

이를 바탕으로 LEFT OUTER JOIN을 하면 아래와 같은 결과를 확인할 수 있다.

SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e LEFT OUTER JOIN dept d 
ON (e.deptno = d.deptno);

위의 결과에서 사원 JACK의 부서위치(LOC)이 NULL인 이유는 JACK의 부서번호가 50번이기 때문이다. JACK의 부서 번호는 50번이지만 DEPT테이블에서 부서번호가 50번인 부서 위치에 대한 정보가 없기 때문에 LEFT OUTER JOIN의 결과에서 NULL이 출력되는 것을 알 수 있다.

 

5. FULL OUTER JOIN

FULL OUTER JOIN은 RIGHT OUTER JOIN과 LEFT OUTER JOIN을 한번에 수행할 때 사용한다.

예를 들어 FULL OUTER JOIN 방법으로 사원의 이름, 직업, 월급, 부서 위치를 출력하면 아래와 같다.

SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e FULL OUTER JOIN dept d 
ON (e.deptno = d.deptno);

위의 결과를 통해 EMP 테이블에만 있는 부서 번호가 50번인 JACK의 데이터와 DEPT 테이블에만 있는 부서 번호가 40인 BOSTON을 출력하며 조인하고 있다는 것을 알 수 있다.

FULL OUTER JOIN의 경우 ORACLE JOIN 방법이 따로 없기 때문에 FULL OUTER JOIN을 사용하여 나타낸다. FULL OUTER JOIN을 사용하지 않고 동일한 결과를 출력하기 위해서는 다음에 살펴볼 UNION을 사용해 나타낼 수 있다.

-- FULL OUTER JOIN과 동일한 결과를 나타내는 UNION 

SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e RIGHT OUTER JOIN dept d 
ON (e.deptno = d.deptno) 
UNION 
SELECT e.ename, e.job, e.sal, d.loc 
FROM emp e LEFT OUTER JOIN dept d 
ON (e.deptno = d.deptno);

이전에 포스팅한 EQUI JOIN, NON EQUI JOIN, OUTER JOIN, SELF JOIN과 같은 문법을 ORACLE JOIN이라 하고, 오늘 배울 ON절, USING절, NATRUAL JOIN, LEFT/RIGHT OUTER JOIN은 모두 ORALCE 9i 버전부터 지원하는 문법. 두 문법은 작성 SQL의 차이만 있을 뿐 성능의 차이는 없음