Oracle SQL(5)

YangJiWon·2021년 1월 11일
oraclesql
0

DB

목록 보기
5/12

조인

  • 여러 테이블을 연결해서 필요한 데이터를 조회하는 방법을 조인이라고 한다.

조인 종류

종류설명
오라클 조인cartesian product조인 조건을 생략하거나 조인이 잘못된 경우에 발생된다.
equi 조인기본키(Primary Key)와 참조키(Foreign Key)을 사용하여 반드시 조건이 일치하는 데이터만 조회하는 방법이다.
non-squi 조인조건이 반드시 일치하지 않더라도 범위에 포함하는 경우에 조회하는 방법이다.
outer 조인조건에 일치하지 않아도 조인 결과에 포함시키는 방법이다.
self 조인자신의 테이블과 조인하는 방법이다.
ANSI 조인cross 조인오라클 조인의 catessian product와 동일한 방법이다.
(다른 DB에서도 사용 가능)natural 조인오라클 조인의 equi 조인과 동일하며 자동으로 두 개의 테이블에서 일치하는 컬럼을 찾아서 조인된다.
using(컬럼)오라클 조인의 equi 조인과 동일하며 명시적으로 일치하는 컬럼을 작성한다.
join ~ on절오라클 조인의 non-equi 조인과 동일 조인 방법이다. on 절에 조인조건을 명시한다.
left, right, full outer 조인오라클 조인의 outer 조인과 동일한 방법이다.
self 조인오라클 조인의 self 조인과 동일한 방법이다.

Equi 조인

  • 조인 대상이 되는 두 테이블에서 공통적으로 존재하는 컬럼의 값이 반드시 일치하는 행을 연결하여 데이터를 반환하는 조인이다.
  • 일치하지 않는 데이터가 존재하는 경우는 제외되며 대부분 기본키를 가진 테이블과 참조키를 가진 테이블을 조인할 때 사용된다.
  • 단순 조인 또는 내부 조인(inner)이라고도 한다.
-- Equi 조인 문법
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼;

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

SELECT e.empno, e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno = 7900;

SELECT e.empno, e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.empno = 7369;

SELECT d.dname 부서명, COUNT(*) 인원수
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.hiredate < TO_DATE('05', 'RR')
GROUP BY d.dname;
  • 테이블에 별칭(alias)를 사용했을 때는 무조건 별칭으로 사용해야 한다. 그렇지 않으면 오류가 발생한다.
  • SELECT 할 때 두 테이블에 똑같은 컬럼명이 같지 않으면 테이블1.컬럼이 아닌 컬럼명만 써줘도 된다.


Non-Equi 조인

  • WHERE 절에 조인 조건을 지정할 때 동등 연산자(=) 이외의 비교 연산자(BETWEEN ~ AND ~, >, >=, <, <=)를 사용하는 조인을 의미한다.
  • 두 테이블의 컬럼에 동일한 값이 없을 때 사용한다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.컬럼 비교연산자(>, < 등) 테이블2.컬럼;

SELECT ename, sal, grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal
ORDER BY 3;

SELECT empno, ename, job, sal, grade, losal, hisal
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal AND deptno = 10;

SELECT e.ename, e.sal, d.dname, s.grade
FROM emp e, dept d, salgrade s
WHERE e.deptno = d.deptno
AND e.sal BETWEEN s.losal AND s.hisal;

SELECT e.empno, e.ename, e.deptno, d.dname, d.loc, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal 
AND d.deptno = 10
AND e.deptno = d.deptno;

SELECT e.empno, e.ename, e.deptno, d.dname, d.loc, e.sal, s.grade
FROM emp e, dept d, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal 
AND e.empno = 7369
AND e.deptno = d.deptno;



Cartesian Product

  • 모든 가능한 행들의 Join으로 다음과 같은 경우에 발생한다.
    1) 조인 조건이 생략된 경우
    2) 조인 조건이 잘못된 경우
    3) 첫 번째 테이블의 모든 행이 두 번째 테이블의 모든 행과 두 번째 테이블의 모든 행이 첫 번째 테이블의 모든 행과 조인되는 경우
    4) 양쪽 ROW의 개수를 곱한 결과
SELECT empno, ename, job, dept.deptno, dname, loc
FROM dept, emp
ORDER BY empno;

self 조인

  • 필요에 의해서 하나의 테이블만 사용하여 자기 자신을 조인하는 것이 self 조인이다.
  • 같은 테이블에 대해 두 개의 alias를 작성함으로써 FROM 절에 두 개의 테이블을 사용하는 것과 같이 한다.
  • Column에 대해서도 어떤 테이블에서 왔는지 반드시 Alias명을 기술하여야 한다.
-- 사원번호, 매니저 번호 self join
SELECT a.ename 사원, b.ename 관리자, a.mgr, b.empno
FROM emp a, emp b
WHERE a.mgr = b.empno;

SELECT e.ename 사원이름, e.mgr 관리자번호, g.empno "관리자 사원번호", g.ename 관리자이름
FROM emp e, emp g
WHERE e.mgr = g.empno
AND e.empno = 7369;

SELECT e.ename, e.mgr, g.empno, g.ename, g.deptno, d.deptno, d.dname
FROM emp e, emp g, dept d
WHERE e.mgr = g.empno
AND d.deptno = g.deptno
AND e.empno = 7369;

Outer 조인

  • 조인 조건에 만족하지 않아도 결과 값에 포함시키는 조인 방법으로 (+)연산자를 사용한다.
  • (+) 연산자는 조인하고자 하는 테이블 중에서 한 번만 사용할 수 있으며 일치하는 데이터가 없는 쪽에 지정한다.
  • (+)연산자를 지정하면 내부적으로 한 개 이상의 NULL을 가진 행이 생성되고 이렇게 생성된 NULL행들과 데이터를 가진 테이블의 행들이 조인하게 되어 조건이 일치하지 않아도 결과 값에 포함이 된다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1, 테이블2
WHERE 테이블1.공통컬럼 = 테이블2.공통컬럼(+);

SELECT e.ename 사원명, m.ename 관리자명
FROM emp e, emp m
WHERE e.mgr = m.empno(+);

Natural 조인

  • Natural 조인 == Equi 조인
  • 즉, 같은 이름을 가진 컬럼에 기반하여 동작이 된다.
  • 따라서, 조인에 참여하는 테이블에는 반드시 한 개의 공통 컬럼이 있어야 되며, 만약 두 개 이상의 공통 컬럼이 있다면 오류는 아니지만 엉뚱한 실행결과가 출력된다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블 NATURAL JOIN 테이블2
[WHERE 검색조건];

SELECT empno, ename, deptno, dname, loc
FROM emp
NATURAL JOIN dept;
-- 위와 아래의 결과는 동일하다.
SELECT e.empno, e.ename, d.dname, d.loc
FROM emp e, dept d
WHERE e.deptno = d.deptno;

SELECT empno, ename, deptno, dname, loc
FROM emp e NATURAL JOIN dept d
WHERE deptno=10;

SELECT e.ename 사원명, deptno 부서번호, d.dname 부서번호
FROM emp NATURAL JOIN dept d
WHERE e.empno = 7369;
  • Natural 조인을 사용시 공통 컬럼을 사용할 때 별칭을 사용하면 안된다. 그 이유는 자동으로 공통 컬럼을 인지하기 때문에 명시적으로 테이블명이나 별칭을 사용하면 에러가 발생된다.
--                   공통컬럼
--                   별칭 (x)
SELECT empno, ename, e.deptno, dname, loc
FROM emp e NATURAL JOIN dept d;

--                   공통컬럼
SELECT empno, ename, deptno, dname, loc
FROM emp e NATURAL JOIN dept d; -- (o)

USING (컬럼)

  • 동일이름의 컬럼이 여러 개인 경우 조인 컬럼을 지정.
  • Natural Join과는 상호배타적이다.(같이 사용하지 못한다.)
  • 컬럼명은 조회할 자료가 있는 테이블에 공통된 컬럼명이다.
  • Natural Join과 Using 절에서는 공통 컬럼명에 Table Alias명을 사용하지 못한다.
  • Natural Join은 두 개 이상의 공통 컬럼이 있다면 엉뚱한 결과가 나온다. 하지만 USING을 사용하면 명시적으로 어떤 컬럼으로 조인할지 지정할 수 있어 원하는 결과를 나오게 할 수 있다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 [INNER] JOIN 테이블2 USING(공통컬럼)
[WHERE 검색조건];

SELECT empno, deptno, ename, dname, loc
FROM emp e
JOIN dept d
USING (deptno);
-- 위와 아래의 SQL문의 결과는 동일하다.
SELECT empno, deptno, ename, dname, loc
FROM emp e
INNER JOIN dept d
USING (deptno);

-- equi 조인과 동일하다.
SELECT empno, deptno, ename, dname, loc
FROM emp e
JOIN dept d
USING (deptno)
WHERE empno = 7396;

Cross 조인

  • Catesian Product와 동일하다.
SELECT empno, ename, dname
FROM dept
CROSS JOIN emp;
-- 위와 아래의 SQL문은 동일한 결과를 도출한다.
SELECT empno, ename, dname
FROM dept, emp;

JOIN ~ ON

  • Non-Equi 조인 또는 임의의 조건으로 조인 시 사용한다.
  • 복잡한 조건의 조인이 가능하다.(서브쿼리, AND/OR, EXIST, IN 연산자)
SELECT 테이블1.컬럼, 테이블2
FROM 테이블1 [INNER] JOIN 테이블2 ON 조인조건
[WHERE 검색조건];

-- equi 조인과 동일
SELECT empno, ename, dname, loc
FROM emp e
JOIN dept d
ON e.deptno = d.deptno;

--            공통컬럼
--            명시해줘야함 
SELECT empno, deptno, ename, dname, loc
FROM emp e
JOIN dept d
ON e.deptno = d.deptno; -- (X)

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

-- Inner 생략 가능하다.
SELECT e.ename, d.dname, d.loc
FROM emp e INNER JOIN dept d ON e.deptno = d.deptno
WHERE e.deptno = 10;

-- JOIN ~ ON으로 Non equi 사용 가능하다.
SELECT e.ename, e.sal, s.grade
FROM emp e JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.empno = 7369;

-- JOIN ~ ON으로 equi, Non equi 동시에 가능하다.
SELECT ename, sal, grade
FROM emp e JOIN dept d ON e.deptno = d.deptno
JOIN salgrade s ON e.sal BETWEEN s.losal AND s.hisal
WHERE e.empno = 7369;

-- JOIN ~ ON으로 self join 가능하다.
SELECT e.ename, e.mgr, m.empno, m.ename, m.mgr, mm.empno, mm.ename
FROM emp e JOIN emp m ON e.mgr = m.empno
JOIN emp mm ON m.mgr = mm.empno;

LEFT, RIGHT, FULL OUTER

  • 오라클 조인에서는 Outer 조인을 사용할 때 (+)연산자를 이용하고 반드시 한 쪽 테이블에서만 사용할 수 있었다.
  • ANSI 조인에서의 Outer 조인은 (+)연산자 대신에 LEFT, RIGHT, FULL 키워드를 이용하며 한 쪽 테이블 또는 양쪽 테이블 지정이 모두 가능하다.
SELECT 테이블1.컬럼, 테이블2.컬럼
FROM 테이블1 LEFT|RIGHT|FULL OUTER JOIN 테이블2
ON 조인조건 | USING(컬럼)
[WHERE 검색조건];
  • LEFT OUTER : LEFT로 지정된 테이블 1의 데이터를 테이블2의 조인조건 일치 여부와 상관없이 모두 출력한다는 의미이다.
  • RIGHT OUTER : RIGHT로 지정된 테이블2의 데이터를 테이블1의 조인조건 일치 여부와 상관없이 모두 출력한다는 의미이다.
  • FULL OUTER : LEFT, RIGHT OUTER 조인 결과를 합친 결과로서, 양쪽 테이블1과 테이블2의 데이터를 조인 조건 일치여부와 상관없이 모두 출력한다는 의미이다.
-- 오른쪽 값이 일치여부와 관계없는 값인 NULL 값도 출력
SELECT e.ename 사원명, m.ename 관리자명
FROM emp e LEFT OUTER JOIN emp m
ON e.mgr = m.empno;

-- 왼쪽 값이 일치여부와 관계없는 값인 NULL 값도 출력
SELECT e.ename 사원명, m.ename 관리자명
FROM emp e RIGHT OUTER JOIN emp m
ON e.mgr = m.empno;

-- 왼쪽, 오른쪽 값이 일치여부와 관계없는 값인 NULL 값도 출력
SELECT e.ename 사원명, m.ename 관리자명
FROM emp e FULL OUTER JOIN emp m
ON e.mgr = m.empno;

SELECT e.empno 사원번호, e.ename 사원명, d.dname 부서명
FROM dept d LEFT OUTER JOIN emp e
ON d.deptno = e.deptno;
-- 위와 아래는 동일한 결과이다.
SELECT e.empno 사원번호, e.ename 사원명, d.dname 부서명
FROM dept d LEFT OUTER JOIN emp e
USING(deptno);

profile
YangJiWon
데이터데이터데이터!!
이전 포스트

Oracle SQL 워크북

다음 포스트

Oracle SQL(6)

0개의 댓글