SQL 기본 - 2(Group by, Order by, Join)

Group By, Having절

집계 함수

Aggregate function

• 여러 행들의 그룹이 모여 그룹당 하나의 결과를 돌려준다

• group by절은 행들을 소그룹화한다

• select, having, order by절에 사용 가능하다

• 집계함수명 ([DISTINCT|ALL] 칼럼/표현식)DISTINCT는 같은 값을 하나의 데이터로 간주한다

• ALL은 기본값

주로 숫자 유형에 사용되지만, MAX, MIC, COUNT는 문자/날짜 유형에도 사용 가능하다 MIN(d) => 가장 빠른 날짜

• COUNT(*) NULL을 포함한 행의 수COUNT(DISTINCT 표현식) UNIQUE한 표현식 값 행의 수
• COUNT(표현식) 표현식 값이 NULL인 것을 제외한 행의 수
• SUM(표현식) NULL을 제외한 합계
• AVG
• MAX
• MIN
• STDDEV(표현식) 표현식의 표준 편차
• VARIANCE/VAR(표현식) 표현식의 분산

GROUP BY절에 있는 칼럼이 SELECT문으로 나올 수 있다

GROUP BY절에 없는 칼럼이 SELECT문으로 나올 수 없다

GROUP BY절에 없는 칼럼이 SELECT문으로 나오려면 집계함수를 사용해야 한다

• 그룹함수가 중첩되면 최종 결과값은 1건이다
  SELECT ID, CODE, AVG(COUNT(*)) FROM TAB
  GROUP BY ID, CODE;
  오류가 난다 AVG(COUNT(*))는 1건이라서!

• SELECT POSITION, AVG(HEIGHT) AS 평균키 FROM PLAYER;
  오류가 난다 POSITION 칼럼과 AVG(HEIGHT)를 동시에 썼기 때문
  포지션 없이 AVG만 있으면 전체 평균키 출력
  (GROUP BY 없이 SELECT에서 집계함수 사용 가능)

• SELECT JOB, HIREDATE FROM EMP GROUP BY JOB;

• 오류가 난다 hiredate가 group by 절에 없으므로 select에 쓰려면 집계 함수 적용

Group By

FROM절과 WHERE절 뒤에 위치한다

데이터들을 소그룹화하여 각 그룹별 통계 정보를 얻을 때 사용한다

• 특성
  • GROUP BY절로 기준을 정하고 SELECT절에 집계함수를 사용한다
  • 집계함수의 통계 정보는 NULL값을 가진 행을 제외한다
  • ALIAS명을 사용할 수 없다
  • 집계함수는 WHERE절에 올 수 없다
  • WHER절은 데이터를 그룹으로 나누기 전에 행들을 미리 제거하고 HAVING절은 GROUP BY절의 기준 항목이나 소그룹의 집계함수를 이용한 조건을 표시할 수 있다.

Having

소그룹의 데이터 중 일부만 필요한 경우에 사용

GROUP BY절로 소그룹을 만들고 HAVING절로 제한 조건을 적용한다

HAVING절은 보통 GROUP BY절 뒤에 위치한다 (앞에 와도 상관은 없다. 단, SQL 서버는 앞에 오면 에러가 난다)

• 키가 180 이상인 사람들의 포지션별 평균키?having절을 where절로 만들어서 group by 위로 올린다면, where절에는 집계함수를 못 써서 에러가 난다

• SELECT POSITION, ROUND(AVG(HEIGHT), 2)
  FROM PLAYER
  GROUP BY POSITION
  HAVING AVG(HEIGHT) >= 180;

• 데이터 중 일부만 필요한 경우

  1. Where 조건으로 필요한 데이터를 추출한 후 Group By
  2. Group By 조건으로 소그룹을 만든 후 Having절로 필터링

  where 조건으로 필터링을 할 경우 group by의 계산 대상을 줄일 수 있다

  where절의 조건을 변경하면 대상 데이터 개수가 변경되고 결과값이 변경될 수 있다

  having절의 조건은 변경된다고 해도 결과가 변경되는 것이 아니다. 출력되는 레코드 수가 변경되는 것이다.

• GROUP BY가 없어도 HAVING을 쓸 수 있다!!

CASE 표현을 활용한 월별 데이터 집계

부서별로 월별 입사자의 평균 급여 구하기

SELECT
    DEPTNO,
    AVG(CASE MONTH WHEN 1 THEN SAL END) AS M01,
    AVG(CASE MONTH WHEN 2 THEN SAL END) AS M02,
    AVG(CASE MONTH WHEN 3 THEN SAL END) AS M03,
    AVG(CASE MONTH WHEN 4 THEN SAL END) AS M04,
    AVG(CASE MONTH WHEN 5 THEN SAL END) AS M05,
    AVG(CASE MONTH WHEN 6 THEN SAL END) AS M06,
    AVG(CASE MONTH WHEN 7 THEN SAL END) AS M07,
    AVG(CASE MONTH WHEN 8 THEN SAL END) AS M08,
    AVG(CASE MONTH WHEN 9 THEN SAL END) AS M09,
    AVG(CASE MONTH WHEN 10 THEN SAL END) AS M10,
    AVG(CASE MONTH WHEN 11 THEN SAL END) AS M011,
    AVG(CASE MONTH WHEN 12 THEN SAL END) AS M012
FROM
   (SELECT ENAME, DEPTNO, EXTRACT(MONTH FROM HIREDATE) AS MONTH, SAL
     FROM EMP)
GROUP BY DEPTNO;

ELSE절 쓰려면

AVG(CASE MONTH WHEN 1 THEN SAL ELSE 0 END) 이렇게 쓴다

집계합수와 NULL

다중행함수는 자체적으로 NULL인 행을 제외하므로 NVL 쓸 필요가 없다

• SELECT AVG(COL3) FROM TABLE;

  이 때, COL3에 20과 NULL만 존재한다면

  20/1건이다(NULL은 건수에서도 제외된다)

CASE 표현에서 ELSE절 생략하면 기본값이 NULL

• ELSE절에 0같은 상수를 지정한 CASE 표현에 집계함수를 사용하면 불필요한 연산을 하게 만든다
• DECODE 함수의(오라클) 4번째 인자를 지정하지 않으면 기본값이 NULL이 된다

SUM(NVL(SAL, 0)은 불필요한 연산을 하게 만든다

• NULL일 때 0을 출력하고 싶을 때는 전체 연산 결과에 쓰면 된다
• NVL(SUM(SAL), 0)

Order by절

정렬

특정 칼럼을 기준으로 조회한 데이터를 정렬한다

칼럼을 나타낼 때는 칼럼명, SELECT절의 ALIAS명, SELECT절의 칼럼 순서를 나타낸 정수 세 개를 혼용할 수 있다

• SELECT C3, C1, C2 FROM TABLE
  ORDER BY 1 DESC, 2;

  테이블에는 C1, C2, C3순으로 들어가 있어도

  select절의 순서가 적용된다

  그래서 첫 번째 칼럼를 기준으로 내림차순 정렬한 뒤, 두 번째 칼럼을 기준으로 오름차순 정렬한다

  SELECT절의 첫 번째 칼럼인 C3 DESC => 두 번째 칼럼인 C1 ASC

기본값은 오름차순이다

• ASC 기본값. 오름차순
  DESC 내림차순

• 숫자형의 오름차순은 가장 작은 값부터

  날짜형의 오름차순은 가장 빠른 날짜부터(01-JAN-2012가 01-SEP-2012보다 앞에 오게 됨)

• 오라클은 NULL을 가장 큰 값으로 간주해서 오름차순시 가장 마지막에 온다

  SQL서버는 이와 반대로 간주한다

ORDER BY 칼럼명 [ASC/DESC];

SELECT절에 쓸 수 있는 항목이 올 수 있다

• 집계함수를 사용할 수 있다

• SELECT절에 없는 칼럼이어도 사용할 수 있다(대상 테이블 안에는 있어야 한다)

  • SELECT DISTINCT
  • GROUP BY
  • SELECT UNION

  위 세 가지 경우에는 SELECT절에 있는 칼럼만 ORDER BY에 올 수 있다

• group by절을 수행한 뒤 DB는 그룹핑 관련 칼럼들로 집합을 새로 만든다

  개별 데이터는 버리기 때문에,

  group by절을 수행한 이후 수행되는 select절이나 order by 절에서 개별 데이터를 사용하면 에러가 난다

  집계 칼럼을 사용하자(order by sum(sal))

SELECT문 실행 순서

SELECT 칼럼명 [[AS] ALIAS명]
        FROM 테이블명
    WHERE 조건식
    GROUP BY 칼럼/표현식
        HAVING 그룹조건식
    ORDER BY 칼럼/표현식;

1. FROM 발췌 대상 테이블을 참조하고
2. WHERE 발췌 대상이 아닌 데이터를 제거한 뒤
3. GROUP BY 데이터를 소그룹화한다
4. HAVING 그룹핑된 값 중 조건 맞는 것을 추출해서
5. SELECT 데이터를 출력한다
6. ORDER BY 출력된 데이터를 정렬한다.

물리적 순서가 아니라 논리적 순서!

옵티마이저가 SQL 문장 에러 점검하는 순서와 동일한다

• SELECT 지역, SUM(매출) AS 매출합계
  FROM TABLE
  GROUP BY 지역
  ORDER BY 년 DESC;

  오류가 난다

  지역으로 그룹화했는데 년으로 정렬을 할 수 없기 때문

조인

개요

두 개 이상의 테이블을 연결해 데이터를 출력한다

정규화를 위해 필요

관계형 데이터베이스의 큰 장점

보통 PK, FK를 통해 조인을 하는데 아니어도 가능하다

FROM절에 여러 테이블이 나열되더라도 SQL은 두 개의 집합씩 조인을 처리한다

• A, B, C가 나열되면 특정 두 개 테이블을 먼저 조인하고
• 중간 데이터 집합과 남은 테이블을 조인한다
• 테이블 조인 순서는 DBMS가 결정한다

조인 조건을 쓸 때, 꼭 테이블명.칼럼명 이렇게 써야 되는 건 아닌데 쓰는 게 좋다

• SELECT절에서 칼럼명을 테이블명과 함께 기재하지 않았는데
  조인하는 테이블에 동일한 칼럼명이 있으면 에러가 난다

만약 테이블명에 ALIAS 적용했으면
WHERE절과 SELECT절에는 ALIAS를 써야만 한다(안 쓰면 에러)

등가 조인

Equi Join

두 테이블 간의 칼럼 값이 정확히 일치할 때!

대개 PK-FK 관계를 기반으로 한다(아니어도 가능)

JOIN의 조건은 WHERE절에 기술한다(= 이용)

• FROM PLAYER, TEAM
  WHERE TEAM.TEAM_ID = PLAYER.TEAM_ID;
• ON절에 기술하기도 한다(= 이용. ANSI/ISO SQL 표준)
• FROM PLAYER INNER JOIN TEAM
  ON TEAM.TEAM_ID = PLAYER.TEAM_ID;

INNER JOIN 대상 테이블이 N개일 때,
필요한 조인 조건은 N-1개 이상

• SELECT COUNT(*) FROM Ta, Tb

  WHERE Tb.C1 = Ta.C1;

  Ta	C1	C2	Tb	C1	C2
  	1	a		1	1
  	2	b		1	2
  	3	c		3	1
  				3	2
  				4	1

  4

  (1-a-1-1),(1-a-1-2),(3-c-3-1),(3-c-3-2)

비등가 조인

Non-Equi Join

두 테이블 간에 논리적인 연관 관계는 있으나 칼럼 값들이 일치하지 않을 때!

= 연산자가 아니라 Between, >, >=, <, <= 등을 사용한다

데이터 모델에 따라 비등가 조인이 불가능한 경우도 있다

사원의 급여가 어떤 급여등급에 속하는지 출력하시오

SELECT A.NAME 이름, A.SAL 급여, B.GRADE 급여등급
        FROM EMP A, SALGRADE B
    WHERE A.SAL BETWEEN B.LOSAL AND B.HISAL;

급여등급 테이블은 GRADE, LOSAL, HISAL로 구성되어

급여가 LOSAL에서 HISAL 사이에 있다면 해당 등급을 가져온다

3개 이상의 테이블 조인하기

선수별 홈그라운드 경기장을 출력하시오

FROM PLAYER A, TEAM B, STADIUM C
WHERE B.TEAM_ID = A.TEAM_ID
    AND C.STADIUM_ID = B.STADIUM_ID;

선수, 팀, 운동장 테이블을 조인해야 하는데,

선수와 운동장 사이에 팀이 있어야만 연관 관계를 가진다

외부 조인

Outer Join

Inner join은(등가 조인, 비등가 조인) 조인 결과가 참인 행만 반환하는데

outer join은 조인 조건을 만족하지 않는 행들도 반환한다

• 테이블1의 모든 값에 대해 테이블2의 데이터가 존재한다는 보장이 없을 때,

  테이블1의 데이터는 전부 출력하고 테이블2는 조인 조건을 만족하는 데이터만 출력한다

  조인에 실패한(짝이 안 맞는) 칼럼 값은 NULL

  아래는 오라클의 outer join 예시이다

  WHERE 테이블2.조인칼럼명(+) = 테이블1.조인칼럼명;

  (+)를 테이블1.칼럼명 뒤에 붙이면 테이블2가 기준이 된다

• 오라클의 (+)를 ANSI문으로

  FROM A, B
  WHERE A.ID = B.ID(+)
      AND B.DELETE(+) = 'N'
      AND A.USAGE = 'Y';
  
  /* ANSI */
  FROM A
      LEFT OUTER JOIN B
      ON (A.ID = B.ID
       AND B.DELETE = 'N')
  WHERE A.USAGE = 'Y';

표준 조인

FROM절 조인 형태(ANSI/ISO)

1. inner join
2. natural join
3. using 조건걸
4. on 조건절
5. cross join
6. outer join

전통적인 방법은 where절을 사용하는 것이고

상기 6가지 방법은 ANSI/ISO SQL에서 규정한 것으로

둘 중 어느 것을 해도 무관하다

단, SQL서버는 ON 조건절만 지원하고 NATURAL JOIN, USING 조건절은 지원하지 않는다

INNER JOIN

내부 조인

조건을 만족하는 행들만 반환한다

전통적으로 where절에 기술하던 조인을 from절에서 정의하는 것으로
using절이나 on절을 필수로 사용해야 한다

• SELECT *

  FROM EMP A, DEPT B

  WHERE B.DEPTNO = A.DEPTNO;

• SELECT *

  FROM EMP A [INNER] JOIN DEPT B

  ON B.DEPTNO = A.DEPTNO;

위의 두 sql 문장은 같은 결과를 만든다

내부 조인이 조인의 기본값이라서 INNER 키워드를 생략할 수 있다

NATURAL JOIN

두 테이블 간에 동일한 이름을 갖는 모든 칼럼들에 대해 등가 조인한다

내츄럴 조인은 USING, ON, WHERE로 조건을 정의할 수 없다

SQL서버는 지원하지 않는다

• FROM EMP A NATURAL [INNER] JOIN DEPT B;

  별도의 조인 조건을 지정하지 않는다

• SELECT A.EMPNO, B.DEPTNO, B.DNAME
  FROM EMP A NATURAL JOIN DEPT B;

  내츄럴 조인에 사용된 열은(두 테이블간 동일한 칼럼, 기준 칼럼) 식별자를 가질 수 없다

  select절에서 기준 칼럼을 출력할 때,

  테이블.기준칼럼 이렇게 기재하면 에러가 난다

  DEPTNO가 A, B 사이에 동일한 칼럼이라서 내츄럴 조인에 사용된 열인데

  해당 칼럼을 ALIAS나 테이블명으로 제한하면 에러가 난다

• 조인이 되는 테이블의 데이터 성격과(도메인) 칼럼명 등이 동일해야 한다

• SELECT *로 하면 기준 칼럼이 먼저 출력되고 기준 칼럼은 한 번만 출력된다

  이너 조인은 기준 칼럼 두 개 다 출력

• 동일한 두 테이블이 있다

  각 테이블에는 ColA, ColB, ColC가 있는데

  ColA의 값을 몇 행 바꾼 뒤 내츄럴 조인을 하면

  ColB, ColC 값은 여전히 두 테이블이 동일하더라도 ColA가 다르므로

  값이 바뀐 행들은 제외된다

  내츄럴 조인 결과는 ColA, ColB, ColC 세 개가 출력된다

  이는 세 칼럼 모두 기준 칼럼이 되었으므로

  중복 없이 하나씩만 출력된 것이다

  만약 이너 조인을 했다면(이너 조인을 하면 세 칼럼에 대한 조건절 명시 필요)

  ColA, ColB, ColC, ColA, ColB, ColC 이렇게 여섯 개가 출력된다

• SELECT SUM(C1) FROM Ta NATURAL JOIN Tb;

  Ta	C1	C2	Tb	C1	C3
  	1	a		1	a
  	2	b		1	b
  	3	c		2	b
  				3	c
  				3	a
  				4	b

  10(1 + 1 + 2 + 3 + 3)

USING

같은 이름을 가진 칼럼들 중에서 원하는 칼럼에 대해서만 등가 조인하고 싶을 때

SQL서버는 지원하지 않는다

SELECT * FROM DEPT A JOIN A_TEMP B
    USING (DEPTNO);

기준칼럼은 한 번만 출력되고

select절에서 명시하지 않았다면 기준칼럼을 맨 앞에 둔다

USING(ColA, ColB)처럼 여러 개를 쓸 수 있다

내츄럴 조인처럼 기준 칼럼을 ALIAS 등으로 한정지어서는 안 된다

ON

칼럼명이 달라도 조인을 할 수 있다

SELECT * FROM EMP A JOIN DEPT B
    ON (B.DEPTNO = A.DEPARTMENT);

on절의 ()는 생략 가능

using절은 조인 칼럼에 alias나 테이블명을 쓰면 에러지만

on절은 alias, 테이블명을 써 주는 게 좋다(안 쓴다고 에러가 나는 건 아니지만)

on, where 혼용 가능

on절에 join 조건 외에도 데이터 검색 조건을 추가할 수 있다

• 검색은 where절에 쓰는 것이 권장된다

• 아우터 조인에서 조인 대상 제한 모적일 때는 on에 추가 조건 기술 가능

  FROM EMP A JOIN DEPT B
      ON B.DEPTNO = A.DEPTNO
          AND A.MGR = 7698;

  AND 뒤의 데이터 검색 조건은 where로 만드는 것이 좋다

다중 테이블 조인

SELECT * 
    FROM EMP A 
        JOIN DEPT B
            ON B.DEPTNO = A.DEPTNO
        JOIN A_TEMP C
            ON C.DEPTNO = B.DEPTNO;

CROSS JOIN

일반 집합 연산자의 PRODUCT 개념으로
테이블간 조인 조건이 없을 때 생길 수 있는 모든 데이터의 조합

두 개의 테이블에 대한 카티시안 곱(CARTESIAN PRODUCT / CROSS PRODUCT)

결과는 양쪽 집합의 M * N

• FROM EMP A CROSS JOIN DEPT B;

  EMP의 행 14개 DEPT의 행 4개

  => 크로스 조인 결과 56건(14 * 4)

• SELECT COUNT(*) FROM Ta, Tb
  WHERE Ta.C1 >= 2
  AND Tb.C2 IN('b', 'c');

  Ta	C1	C2	Tb	C1	C2
  	1	a		1	a
  	2	b		1	b
  	3	c		2	a
  	4	d		3	b
  				3	c
  				4	d

  9(Ta의 조건 만족 3행 * Tb의 조건 만족 3행)

where절에 조건을 추가할 수 있다. 이러면 이너 조인이랑 같아짐

OUTER JOIN

조인 조건을 FROM에서 쓴다

outer join의 기준 테이블(아우터 집합)은 조인에 실패해도 최종 결과에 포함된다

USING/ON절 필수

LEFT [OUTER] JOIN

먼저 표기된 좌측 테이블 데이터를 읽은 후,
우측 테이블에서 조인 대상 데이터를 읽는다

조인 조건을 만족하는 값이 없을 경우 우측 테이블의 칼럼 값은 NULL이 된다

• SELECT SUM(B.PRICE) / COUNG(DISTINCT A.NUM)

  FROM 고객 A, 주문 B

  WHERE B.CODE(+) = A.NUM

  AND B.PRICE(+) > 10000;

  고객	NUM	NAME	주문	NUM	CODE	PRICE
  	1	K		2001	1	40000
  	2	N		2002	2	15000
  	3	D		2003	2	7000
  	4	C		2004	2	8000
  				2005	2	20000
  				2006	3	5000
  				2007	3	9000

  18750

  • 결과

    NUM	NAME	NUM	CODE	PRICE
    1				40000
    2				15000
    2				20000
    4		NULL	NULL	NULL
    5		NULL	NULL	NULL

    (40000 + 15000 + 20000) / 4

RIGHT [OUTER] JOIN

FULL [OUTER] JOIN

좌측, 우측 테이블의 모든 데이터를 조인한다

조인에 성공한 행은 한 번만 표시한다

• OUTER JOIN의 예시(EMP-DEPT순서)

  EMP	A(PK)	B	C(FK)	DEPT	C(PK)	D	E
  	1	b	w		w	1	10
  	3	d	w		z	4	11
  	5	y	y		v	2	12