DB 기본기 익히기 (3) : JOIN, GROUP BY, ORDER BY

1. JOIN - 두 개의 테이블을 하나의 테이블로

• 두 개의 테이블을 조합해서 하나의 데이터 표로 확인해야 하는 경우가 있다. 이것이 바로 JOIN
• [테이블명].[컬럼명] 으로 조건을 넣는다는 것에 유의

SQL> SELECT ENAME, EMP.DEPTNO, DEPT.DEPTNO, DNAME
  2  FROM EMP, DEPT
  3  WHERE EMP.DEPTNO = DEPT.DEPTNO;

ENAME          DEPTNO     DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH              20         20 RESEARCH
ALLEN              30         30 SALES

• 다만 위에 방법은 두 테이블을 왔다 갔다하면서 데이터를 찾고 뽑아오는 과정을 반복한다. 즉 이러한 쿼리문이 많을 수록 트래픽이 올라가고 서버에 부하를 줄 수 있다.
• 때문에 컬럼을 검색 할 때는 QUERY의 내용에 대해서 [테이블명].[컬럼명] 을 쓰는게 좋다.
• 또한 별칭을 쓰면 같은 내용이라도 아래처럼 훨씬 QUERY 문이 훨씬 줄어든다.
• JOIN QUERY 문에서 WHERE 절은 JOIN 조건절이라고 한다 ⇒ JOIN 조건절의 연산자에 따라서 EQUI JOIN 나 NONEQUI JOIN 라고도 한다.

SQL> SELECT E.ENAME, E.DEPTNO, D.DEPTNO, D.DNAME
  2  FROM EMP E, DEPT D
  3  WHERE E.DEPTNO = D.DEPTNO;

ENAME          DEPTNO     DEPTNO DNAME
---------- ---------- ---------- --------------
SMITH              20         20 RESEARCH

SQL> SELECT E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL FROM EMP E, SALGRADE S
  2  WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

ENAME             SAL      GRADE      LOSAL      HISAL
---------- ---------- ---------- ---------- ----------
SMITH             800          1        700       1200
JAMES             950          1        700       1200

• 단 아래와 같을 때 AND 절은 조건절이 아닌 일반절로 취급된다. 이는 두 테이블을 연결하기 위한 절이 아닌 데이터를 뽑아내기위한 절이기 때문이다.

SQL> SELECT E.ENAME, E.SAL, S.GRADE, S.LOSAL, S.HISAL FROM EMP E, SALGRADE S
  2  WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
  3  AND E.SAL >= 2000;

ENAME             SAL      GRADE      LOSAL      HISAL
---------- ---------- ---------- ---------- ----------
CLARK            2450          4       2001       3000
BLAKE            2850          4       2001       3000

• 아래처럼 서로 다른 별칭을 사용하여 논리적으로 복사가 가능하다.
  • 실제로 테이블 복사가 되는가? → X
  • 논리적으로 QUERY 문에 사용할 수 있는가? → O

SQL> SELECT E.EMPNO, E.ENAME, E.MGR, C.EMPNO, C.ENAME, C.MGR
  2  FROM EMP E, EMP C;

     EMPNO ENAME             MGR      EMPNO ENAME             MGR
---------- ---------- ---------- ---------- ---------- ----------
      7369 SMITH            7902       7369 SMITH            7902

• WHERE 조건절에서 = 로 찾을 때는 기본적으로 같은 행에서 의 내용을 비교하게 됨. 이렇게 같은 부분을 기준으로 비교하는 것을 보다 수비게 해결하기 위해 사용되는 것이 JOIN 명령어

- JOIN 은 크게 4가지

• = 연산자인 경우 : EQUI JOIN
• = 아닌 다른 연산자 : NONEQUI JOIN
• 자기 스스로를 논리적으로 복사하여 JOIN 하는 것 ⇒ SELF JOIN
• NULL 값을 해결하기 위해 외부에서 값을 덧붙이는 조인 : 외부조인 ⇒ 외부조인이 필요한 쪽에 (+) 붙이기

####### SELF 조인 #######

SQL> SELECT E.ENAME, E.MGR, C.EMPNO, C.ENAME
  2  FROM EMP E, EMP C
  3  WHERE E.MGR = C.EMPNO;

ENAME             MGR      EMPNO ENAME
---------- ---------- ---------- ----------
FORD             7566       7566 JONES

####### 외부 조인 #######
SQL> SELECT E.ENAME, C.ENAME
  2  FROM EMP E, EMP C
  3  WHERE E.MGR = C.EMPNO(+);

ENAME      ENAME
---------- ----------
JONES      KING
SMITH      FORD
KING

 

2. NATURAL JOIN, INNER JOIN

NATURAL JOIN 은 공통의 컬럼을 ‘기준’ 으로만 사용가능하다.

만약 공통된 컬럼도 출력하고 싶다면, 공통된 컬럼은 별칭을 주지 않거나, INNER JOIN 을 사용해야한다.

• NATURAL JOIN은 동일한 컬럼명을 기준으로 두 개의 테이블을 붙인다 ⇒ 보통 동일한 컬럼명이 1개 일 때만 사용!
• 만약 동일한 컬럼이 2개 이상이라면 USING(동일한 컬럼명) 을 사용한다
• INNER JOIN 을 사용하려면 다음을 사용한다 ⇒ ON ( 컬럼명1 = 컬럼명2 )
• INNER 을 통해서 NATURAL JOIN 과 같은 결과를 얻어올 수 있다 ⇒ 교집합

// 1개 일때는 그냥 사용
SQL> SELECT * FROM EMP E NATURAL JOIN DEPT D;

// 2개 이상일때는 USING
SQL> SELECT E.ENAME, D.DNAME FROM EMP E JOIN DEPT D
USING ( DEPTNO );

// 같은 컬럼명이 없을때는 ON
SQL> SELECT E.ENAME, D.DNAME
  2  FROM EMP E INNER JOIN DEPT D
  3  ON ( E.DNO = D.DEPTNO );

 

3. OUTER JOIN

• OUTER JOIN은 조인하는 여러 테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 혹은 NULL 인 경우 데이터가 있는 쪽 테이블의 내용을 모두 출력하는 것
• LEFT OUTER JOIN 이면 왼쪽기준으로 데이터 출력, RIGHT OUTER JOIN 이면 오른쪽 기준으로 데이터 출력

SQL> SELECT E.ENAME, C.ENAME
  2  FROM EMP E LEFT OUTER JOIN EMP C
  3  ON (E.MGR=C.EMPNO);

연습하기 : NATURAL JOIN + OUTER JOIN

• JOIN 후에 다시 JOIN 할 때는 ⇒ ~~~ NATURAL JOIN ~~~ + JOIN ~~~~ 하면 된다
• NATRUAL JOIN ~~~ WHERE 절
• INNER JOIN ~~~~ ON 절 ~~~ WHERE 절

SQL> SELECT E.ENAME, D.DNAME, S.GRADE
  2  FROM EMP E NATURAL JOIN DEPT D JOIN SALGRADE S
  3  ON ( E.SAL BETWEEN S.LOSAL AND S.HISAL);

 

4. ORDER BY

• 데이터를 정렬하는 명령어 ⇒ 문자의 가장 마지막에 사용
• 어떤 컬럼 기준 어떻게 정렬
  • ORDER BY [컬럼명, 별칭] [ASC : 오름차순] [DESC : 내림차순]
  • 컬럼명 대신 별칭 사용 가능, 해당 컬럼이 SELECT 몇 번째 있는지에 따라 N번째 숫자를 써도 무방
  • 오름차순 내림차순 생략 시 기본은 오름차순
  • ORDER BY 뒤에 1차 2차 정렬을 지정 할 수 있음 ⇒ AGE DESC , NAME DESC 라고 한다면 나이순으로 내림차순 후 나이가 같으면 이름순으로 다시 내림차
  • SELECT 절에 없는 컬럼명이라도 ORDER BY 정렬 기준으로 세울 수 있음

#### 컬럼명 대신 별칭도 사용가능

SQL> SELECT EMPNO, ENAME, SAL*12 ANNSAL
  2  FROM EMP
  3  ORDER BY ANNSAL DESC;

#### 2차 정렬 사용 가능 : SAL 이 동일한 애들은 이름으로 내림차순

SQL> SELECT EMPNO, ENAME, SAL
  2  FROM EMP
  3  ORDER BY SAL DESC, ENAME DESC;

     EMPNO ENAME             SAL
---------- ---------- ----------
      7788 SCOTT            3000
      7902 FORD             3000

SQL> SELECT E.EMPNO, E.ENAME, E.SAL, E.DEPTNO
  2  FROM EMP E
  3  ORDER BY SAL DESC, EMPNO ASC;

     EMPNO ENAME             SAL     DEPTNO
---------- ---------- ---------- ----------
      7839 KING             5000         10
      7788 SCOTT            3000         20
      7902 FORD             3000         20

#### 별칭 사용 + SELECT 절에 없는 컬럼명

SQL> SELECT E.EMPNO, E.ENAME, E.SAL*12+NVL(COMM, 0) ANNSAL
  2  FROM EMP E
  3  ORDER BY DEPTNO ASC;

 

4. MULTIPLE ROW FUNCTION

• 여러행을 읽어서 실행하는 함수
• MIN, MAX, COUNT, COUNT(*), SUM(SAL) ⇒ SAL 컬럼 전체를 읽어오고 이후 MIN , MAX, SUM, COUNT, AVG 함수를 실행함 ⇒ 기본적으로는 숫자만 사용가능
• MIN, MAX 는 예외적으로 문자에도 사용 가능 ⇒ 이때 MIN, MAX 는 문자의 경우 아스키 코드를 기준으로 해서 결과 출력이 가능함
• NULL 값은 제외됨

SQL> SELECT MIN(SAL), MAX(SAL), COUNT(SAL), COUNT(*), SUM(SAL) FROM EMP;

  MIN(SAL)   MAX(SAL) COUNT(SAL)   COUNT(*)   SUM(SAL)
---------- ---------- ---------- ---------- ----------
       800       5000         14         14      29025

 

5. GROUP BY

• 특정한 컬럼을 기준으로 - 특정한 컬럼이 일치하는 값끼리 모아서 - 그룹을 만들어서 결과를 내보내줌
• 월급 : SAL 이고 부서번호 : DEPTNO 일 때
• GROUP BY DEPTNO 를 통해 DEPTNO 가 똑같은 값끼리 묶어서 그룹을 만들고, 이 값을 AVG - 평균 - 을 만든다
• GROUP BY 컬럼1, 컬럼2 : 컬럼 1과 컬럼 2가 모두 일치하는 그룹
• HAVING : GROUP BY 로 그룹화 한 후 사용하는 조건절 → 즉 GROUP BY 일때는 WHERE 대신 HAVING 로 조건절을 넣음

SQL> SELECT DEPTNO, AVG(SAL)
  2  FROM EMP
  3  GROUP BY DEPTNO
  4  ORDER BY DEPTNO ASC;

    DEPTNO   AVG(SAL)
---------- ----------
        10 2916.66667
        20       2175
        30 1566.66667

#####

SQL> SELECT JOB , AVG(SAL)
  2  FROM EMP
  3  GROUP BY JOB;

JOB         AVG(SAL)
--------- ----------
CLERK         1037.5
SALESMAN        1400
PRESIDENT       5000

#####

SQL> SELECT DEPTNO, COUNT(DEPTNO)
  2  FROM EMP
  3  GROUP BY DEPTNO;

    DEPTNO COUNT(DEPTNO)
---------- -------------
        30             6
        20             5
        10             3

##### DEPTNO 와 JOB 모두 일치하는 그룹

SQL> SELECT DEPTNO, JOB, COUNT(*)
  2  FROM EMP
  3  GROUP BY DEPTNO, JOB
  4  ORDER BY DEPTNO;

    DEPTNO JOB         COUNT(*)
---------- --------- ----------
        10 CLERK              1
        20 ANALYST            2

##### 부서번호, JOB 별 - 두가지가 모두 일치하는 - 급여 합계

SQL> SELECT DEPTNO, JOB, SUM(SAL)
  2  FROM EMP
  3  GROUP BY DEPTNO , JOB
  4  ORDER BY DEPTNO;

    DEPTNO JOB         SUM(SAL)
---------- --------- ----------
        10 CLERK           1300
        10 MANAGER         2450

#####

 

번외 : 중복 제거 => SELECT DISTINCT 컬럼

SQL> SELECT DISTINCT DEPTNO
  2  FROM EMP;

 

6. SELECT 문의 6절 : 실행 순서 & 정리

1. SELECT
2. FROM
3. WHERE : (일반적인) 조건절
4. GROUP BY : 그룹으로 묶을 때 사용하는 QUERY
5. HAVING : GROUP BY 때 사용하는 조건절
6. ORDER BY : 정렬 QUERY

##### 급여 1000이상인 사원을 대상으로
부서번호별 JOB별 평균급여
이때 평균급여는 2000이상 출력은 평균급여가 많은 순

SQL> SELECT E.DEPTNO, E.JOB, AVG(SAL)
  2  FROM EMP E
  3  WHERE SAL >= 1000
  4  GROUP BY DEPTNO, JOB
  5  HAVING AVG(SAL) >= 2000
  6  ORDER BY AVG(SAL) DESC;

    DEPTNO JOB         AVG(SAL)
---------- --------- ----------
        10 PRESIDENT       5000
        20 ANALYST         3000
        20 MANAGER         2975
        30 MANAGER         2850
        10 MANAGER         2450

#####

SQL> SELECT DEPTNO, COUNT(DEPTNO)
  2  FROM EMP
  3  WHERE SAL >= 1500
  4  GROUP BY DEPTNO
  5  HAVING COUNT(DEPTNO) > 2;