SQL 중급 - 복습 1

SELECT ROWNUM
     , empno
     , ename
     , job
     , sal
  FROM emp
  WHERE ROWNUM <= 5

ROWNUM : 각 행의 번호를 출력. ROWNUM과 WHERE절을 사용하면 대용량 테이블의 데이터 상단 행만 살펴볼 수 있다.

SELECT empno
     , ename
     , job
     , sal
  FROM emp
  ORDER BY sal DESC FETCH FIRST 4 ROWS ONLY

TOP-N QUERY : 정렬된 결과로부터 위쪽 또는 아래쪽의 N개의 행을 반환하는 쿼리.
FETCH N ROWS ONLY : 위에서 N번째 행까지 반환.
N부분에는 20 PERCENT 와 같은 비율도 기입할 수 있다.

SELECT empno
     , ename
     , job
     , sal
  FROM emp
  ORDER BY sal DESC OFFSET 9 ROWS
                    FETCH FIRST 2 ROWS ONLY

OFFSET : 월급이 10번째(9+1)로 높은 사원을 출력!

SELECT ename
     , loc
     FROM emp, dept
     WHERE emp.deptno = dept.deptno

JOIN : 서로 다른 테이블에 있는 컬럼들을 하나의 결과로 출력하려면 필요하다.

• emp 테이블에도 deptno가 존재하고, dept 테이블에도 deptno가 중요하다
  emp 테이블의 부서번호는 dept 테이블의 부서 번호와 같다는 조건을 주는 것이다.

SELECT ename
     , loc
     , job
     FROM emp, dept
     WHERE emp.deptno = dept.deptno
           AND emp.job = 'ANALYST'

WHERE절에 AND를 이용해 조건을 추가할 수 있다.

SELECT e.ename
     , d.loc
     , e.job
     FROM emp e, dept d
     WHERE e.deptno = d.deptno
           AND e.job = 'ANALYST'

다음과 같이 테이블에 별칭을 지정할 수 있다.
테이블마다 존재하는 컬럼이 다르므로, 가져오는 컬럼을 어느 테이블에서 가져오는지 지정해주면 좋다.

SELECT e.ename
     , e.sal
     , g.grade
  FROM emp e, salgrade s
  WHERE e.sal BETWEEN s.losal AND s.hisal

non equi join : 위 예제와 같이 emp와 salgrade 사이에 동일한 컬럼이 없는 경우 사용한다.

SELECT *
  FROM salgrade

다음과 같이 salgrade 테이블은 losal과 hisal이라는 월급 구간이 있다. 따라서 BETWEEN절과 두 컬럼을 사용해서 조건을 줄 수 있다.

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

emp 테이블에는 deptno가 40인 데이터가 없고, dept 테이블에는 있다.
따라서 EQUI JOIN을 사용하면, deptno가 40인 데이터는 출력되지 않는다.
하지만 위의 예시처럼 (+)를 추가하면 emp테이블에 없는 deptno가 40인 데이터도 출력된다 (BOSTON)
이것을 OUTER JOIN 이라고 한다.

SELECT e.ename 사원
     , e.job 직업
     , m.ename 관리자
     , m.job 직업
  FROM emp e, emp m
  WHERE e.mgr = m.empno and e.job = 'SALESMAN'

SELF JOIN : 자기 자신 테이블과 조인하는 방법.

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

ON절을 사용한 JOIN : EQUI JOIN에서는 WHERE절에 작성했던 조인 조건을 ON절에 작성한다.

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

동일한 결과를 나타내지만, EQUI JOIN으로 작성했을 때의 쿼리이다.

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

USING을 이용한 JOIN : 조인 조건 대신, 두 테이블을 연결할 때 사용할 컬럼만 괄호 안에 기입하면 된다.

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

NATURAL JOIN : 조인 조건을 명시적으로 작성하지 않아도 FROM절에 emp와 dept 사이에 NATURAL JOIN 하겠다고 기술하는 것.
연결고리가 되는 컬럼인 deptno를 WHERE 절에 조건으로 추가하고 싶으면, 테이블 별칭 없이 작성해야 한다.

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

SELECT e.ename 이름
     , e.job 직업
     , e.sal 월급
     , d.loc "부서 위치"
  FROM emp e RIGHT OUTER JOIN dept d
  ON(e.deptno = d.deptno)

LEFT/RIGHT OUTER JOIN : 위의 예시에서 emp와 dept를 조인할 때, 오른쪽의 dept 테이블의 데이터는 전부 출력된다.

SELECT e.ename 이름
     , e.job 직업
     , e.sal 월급
     , d.loc "부서 위치"
  FROM emp e FULL OUTER JOIN dept d
  ON(e.deptno = d.deptno)

FULL OUTER JOIN : 서로에게 없는 데이터 모두를 출력해주는 조인. 즉 RIGHT/LEFT OUTER JOIN을 한번에 수행하는 쿼리이다.

SELECT deptno
     , SUM(sal)
  FROM emp
  GROUP BY deptno
UNION ALL
SELECT TO_NUMBER(null) as deptno
     , SUM(sal)
  FROM emp

UNION ALL : 위아래의 쿼리 결과를 하나의 결과로 출력하는 집합 연산자.

SELECT ename
     , sal
     , job
     , deptno
  FROM emp
  WHERE deptno in (10, 20)
INTERSECT
SELECT ename
     , sal
     , job
     , deptno
  FROM emp
  WHERE deptno in (20, 30)

INTERSECT : 위/아래 쿼리의 결과물 사이의 교집합을 구해준다.

SELECT ename
     , sal
     , job
     , deptno
  FROM emp
  WHERE deptno in (10, 20)
MINUS
SELECT ename
     , sal
     , job
     , deptno
  FROM emp
  WHERE deptno in (20, 30)

MINUS : 위쪽 쿼리에서 아래쪽 쿼리를 빼주는 것이다. 즉, 위쪽 쿼리에서 아래쪽 쿼리와의 교집합 부분을 제외한 부분을 출력한다.