[Ch 2/3] SQL로 데이터 처리하기(2)

쿼리 안의 쿼리(subquery)

subquery 사용하기

[문제상황] id가 14인 임직원보다 생일이 빠른 임직원의 id, 이름, 생일을 알고싶음

[첫 번째 방법: 두 개의 쿼리로 나누어 실행]

SELECT birth_date FROM employee WHERE id=14;

위 구문으로 id=14인 임직원의 생일 파악 후

SELECT id, name, birth_date FROM employee
WHERE birth_date < "1992-08-04"

직접 값을 기입하여 결과 도출

[두 번째 방법: 하나의 쿼리로 실행] 위의 “1992-08-04”자리에 첫 번째 구문을 넣음

SELECT id, name, birth_date FROM employee
WHERE birth_date < (
    SELECT birth_date FROM employee WHERE id=14
);

• subquery(nested query or inner query): SELECT, INSERT, UPDATE, DELETE에 포함된 query. ()안에 기술됨.
• outer query(main query): subquery를 포함하는 query

SELECT id, name, position FROM employee
WHERE (dept_id, sex) = (
    SELECT dept_id, sex FROM employee WHERE id = 1
);

• subquery의 결과가 하나 이상의 attributes를 return 할 수 있음

[문제상황] id가 5인 임직원과 같은 프로젝트에 참여한 임직원들의 id를 알고 싶음

[첫 번째 방법: 두 개의 쿼리로 나누어 실행]

SELECT proj_id FROM works_on WHERE empl_id = 5;

SELECT DISTINCT empl_id FROM works_on
WHERE empl_id != 5 AND (proj_id = 2001 OR proj_id = 2002);

• id=5 임직원이 두 개의 프로젝트에 참여한 상황(2001, 2002)
• 특정 임직원이 프로젝트 2001, 2002에 모두 참여한 경우 중복 방지를 위해 DISTINCT 설정
• 하단 구문을 아래와 같이 수정 가능
  • (proj_id = 2001 OR proj_id = 2002) -> proj_id IN(2001, 2002)

IN

• v IN(v1, v2, …): v가 괄호 안의 하나와 값이 같다면 true를 return
• (v1, v2, …)는 면시작인 값들의 집합일 수도 있고 subquery의 결과(set(중복X) or multiset(중복O))일 수도 있음
• v NOT IN (v1, v2, …): v가 괄호 란의 모든 값과 값이 다르다면 true를 return

[두 번째 방법: 하나의 쿼리로 실행]

SELECT DISTINCT empl_id
FROM works_on
WHERE empl_id != 5 AND proj_id IN(
    SELECT proj_id FROM works_on WHERE empl_id = 5
);

• 어느 테이블 소속인지 명시되지 않은 attributes는 해당 attribute 이름을 가지는 가장 가까이에 있는 테이블을 참조(c에서의 중복된 이름의 변수의 범위와 비슷한 개념(?))

[FROM 절에도 subquery 존재 가능]

SELECT id, name
FROM employee, (
    SELECT DISTINCT empl_id
    FROM works_on
    WHERE empl_id != 5 AND proj_id IN (
        SELECT proj_id
        FROM works_on
        WHERE empl_id = 5
      )
) AS DSTNCT_E
WHERE id = DSTNCT_E.empl_id;

• 가상의 테이블 DSTNCT_E 생성

EXISTS

• ‘무언가’ 존재하는 튜플을 탐색
• ‘무언가’는 subquery에 명시
• EXISTS: subquery의 결과가 최소 하나의 row라도 있다면 true를 반환
• NOT EXISTS: subquery의 결과가 최소 하나의 row라도 없다면 true를 반환

SELECT P.id, P.name
FROM project P
WHERE EXISTS(
    SELECT *
    FROM works_on W
    WHERE W.proj_id = P.id AND W.empl_id IN (7, 12)
);

• IN으로 대체하여 쓰일 수 있음(쿼리 변경 필요)

ANY

• v ~비교 연산자~ ANY (subquery): subquery가 반환한 결과들 중 단 하나라도 v와의 비교 연산이 true라면 true를 반환
• SOME도 ANY와 같은 역할을 함

[SELECT 절에도 subquery 존재 가능]

[문제상황] 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID, 이름, 연봉과 해당 부서 최고 연봉을 알고 싶음.

SELECT E.id, E.name, E.salary, (
    SELECT max(salary)
    FROM employee
    WHERE dept_id = E.dept_id
  ) AS dept_max_salary
FROM department D, employee E
WHERE D.leader_id = E.id AND E.salary < ANY (
    SELECT salary
    FROM employee
    WHERE id <> D.leader_id AND dept_id = E.dept_id
);

• <>와 !=는 같은 의미
• max: 최댓값을 구하는 함수

ALL

• v ~비교 연산자~ ALL (subquery): subquery가 반환한 결과들과 v와의 비교 연산이 모두 true라면 true를 반환

참고사항

1. IN vs EXISTS 성능 비교: RDBMS의 종류와 버전에 따라 다
2. 해당 내용은 MySQL 기준. 다른 RDBMS의 SQL 문법은 조금씩 다를 수 있음.

---

three-valued logic

NULL

• unknown, unavailable(이용 불가), not applicable(해당 사항이 아님)
• NULL 과 비교할 때에는 같다, 다르다를 단정지을 수 없음

SELECT id FROM employee WHERE birth_date IS NULL;

• birth_date = NULL 이 아닌 ‘IS’ 사용

three-valued logic

• 비교/논리 연산의 결과로 TRUE, FALSE, UNKNOWN을 가짐
• SQL에서 NULL과 비교 연산을 하면 그 결과는 UNKNOWN
  • UNKNOWN은 TRUE일 수도 있고 FALSE일 수도 있다는 의미
  • NULL이 값은 있지만 ‘알려지지 않음’의 의미일 경우도 있기 때문

WHERE절의 condition(s)

• WHERE절에 있는 condition(s)의 결과가 TRUE인 튜플들만 선택됨
• 결과가 FALSE거나 UNKNOWN이면 선택되지 않음

---

테이블 조인(join)

• 두개 이상의 테이블들에 있는 데이터를 한 번에 조회하는 것

implicit join

SELECT D.name
FROM employee AS E, department AS D
WHERE E.id = 1 and E.dept_id = D.id;

• implicit join: FROM 절에는 테이블만 나열하고 WHERE절에 join condition을 명시 -> 복잡한 쿼리 작성 시 잘못된 쿼리를 작성할 가능성이 큼

explicit join

SELECT D.name
FROM employee AS E JOIN department AS D ON E.dept_id = D.id
WHERE E.id = 1;

• explicit join: FROM절에 ‘JOIN’ 키워드롸 함께 joined table들을 명시
  • FROM절에서 ON 뒤에 joined condition이 명시
  • 가독성이 좋아짐
  • JOIN앞에 inner 이 생략된 상태(inner join)
  • euqi join임 (하단에 설명)

INNER JOIN과 OUTER JOIN

INNER JOIN
두 테이블에서 join condition을 만족하는 튜플들로 result table을 만드는 join

• FROM table1 (INNER) JOIN table2 ON join_condition
• 비교 연산자 가능
• null값을 가지는 튜플은 result table에 포함 X
  • null 과 연산을 하면 unknown이 나오는데 결과는 true인 튜플들만 포함시킴

OUTER JOIN
두 테이블에서 join condition을 만족하지 않는 튜플들도 result table에 포함시키는 join

• FROM table1 LEFT (OUTER) JOIN table2 ON join_condition
  • table1 에 있는 null 값까지 result table에 포함시킴
  • 해당 튜플의 table2값에는 전부 null로 표시
• FROM table1 RIGHT (OUTER) JOIN table2 ON join_condition
• FROM table1 FULL (OUTER) JOIN table2 ON join_condition
  • 양쪽 테이블에서 null인 값들을 모두 표시
  • FULL OUTER JOIN은 MySQL에서 지원하지 않음(pgrsql에서는 가능)
• 비교 연산자 가능

EQUI JOIN

join condition에서 ‘=’를 사용하는 join

• euqi join을 보는 두 사지 시각
  1. inner join outer join 상관없이 = 사용 시
  2. inner join 으로 한정해서 = 사용 시

테이블 칼럼 명 변경

ALTER table ~테이블 명~
RENAME COLUMN ~바꿀 column이름~ TO ~새로운 column이름~;

[USING]
두 테이블이 equi join할 때 join 하는 attribute의 이름이 같은 경우 사용

SELECT * 
FROM employee E INNER JOIN department D ON E.dept_id = D.dept_id;

• 위의 경우 result table의 column이름이 중복(dept_id)으로 나타나게 됨

SELECT *
FROM employee E INNER JOIN department D USING(dept_id);

• dept_id가 한 번만 나타남
• 테이블의 가장 첫 번째 column이 dept_id로 나타남

NATURAL JOIN

두 테이블에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행

• join condition을 따로 명시하지 않음
• FROM table1 NATURAL (INNER) JOIN table2
• FROM table1 NATURAL (LEFT/RIGHT/FULL) (OUTER) JOIN table2

  SELECT *
  FROM employee E NATURAL INNER JOIN department D;
  

• employee 테이블과 department 테이블에서 같은 column인 것으로 equi join 수행

[CROSS JOIN]
두 테이블의 튜플 쌍으로 만들 수 있는 모든 조합을 result table 로 반환

• JOIN condition이 없음
• implicit cross join: FROM table1, table2
• explicit cross join: FROM table1 CROSS JOIN table2

  MySQL에서 CROSS JOIN

  • cross join = inner join = join
  • cross join에 ON/USING을 같이 쓰면 inner join으로 동작
  • inner join/join 이 ON/USING 없이 사용되면 cross join으로 동작

[SELF JOIN]
테이블이 자기 자신에게 join하는 경우

---

그룹 짓기(gruping), 집계 함수(aggregate function), 정렬하기(ordering)

ORDER BY

조회 결과를 특정 attribute(s)기준으로 정렬하여 가져오고 싶을 때 사용

• defalt 정렬 방식은 오름차순(ASC)
• 내림차순 정렬 원할 경우 DESC로 표

SELECT * FROM employee ORDER BY salary;

• 오름차순으로 정렬하는 경우

SELECT * FROM employee ORDER BY salary DESC;

• 내림차순으로 정렬하는 경우

SELECT * FROM employee ORDER BY dept_id ASC, salary DESC;

• 두 개 이상의 attribute에 대해서 정렬
• dept_id기준 오름차순으로 우선 정렬 뒤, salary 는 내림차순으로 정렬

aggregate function

여러 튜플들의 정보를 요약해서 하나의 값으로 추출하는 함수

• COUNT, SUM, MAX, MIN, AVG
• 관심있는 attribute에 사용
• NULL값은 제외하고 요약 값 추출

SELECT COUNT(*) FROM employee;

• employee 테이블의 모든 튜플들을 검사
• SELECT COUNT(position) FROM employee; -> 도 동일

SELECT COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE W.proj_id = 2003;

• 각 프로젝트에 참여한 임직원 수, 최대 연봉, 최소 연봉, 평균 연봉을 구하는 방법

GROUP BY

관심있는 attribute 기준으로 그룹을 나눠서 그룹별로 aggregate function을 적용하고 싶을 때 사용

• grouping attribute(s): 그룹을 나누는 기준이 되는 attribute(s)
• grouping attribute(s)에 NULL 값이 있는 경우 NULL 값을 가지는 튜플끼리 묶임

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
GROUP BY W.proj_id;

• SELECT 문에 grouping attribute(s)(W.proj_id)를 적어주어야 어떤 그룹으로 나누었는지 파악 가능

HAVING

aggregate function의 결과값을 바탕으로 그룹을 필터링 하고 싶을 때 사용

• GROUP BY 와 함께 사용
• HAVING 절에 명시된 조건을 만족하는 그룹만 결과에 포함

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
GROUP BY W.proj_id
HAVING COUNT(*) >= 3;

• 그룹화 된 테이블에서 COUNT(*)의 수가 3이상인 튜플만 선택

[예제1: 회사 전체 평균 연봉보다 평균 연봉이 적은 부서들의 평균 연봉을 알고 싶음]

1. 회사 전체 평균 연봉을 구함(subquery)
2. 각 부서의 평균 연봉을 구함(GROUPING)
3. HAVING으로 GROUPING의 결과에 조건을 추가
4. SELECT 절에 출력할 값을 추가

SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) < (SELECT AVG(salary) FROM employee);

[예제2: 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 아고 싶음]

1. 데이터와 관련된 테이블(employee, works_on)을 조인
2. 위의 테이블에서 직원의 생일 한정
3. 위의 테이블에서 proj_id로 GROUPING
4. 위의 결과로 원하는 결과 추출(SELECT)
5. 추가적으로 proj_id로 정렬 원하는 경우 ORDER BY W.proj_id

SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31'
GROUP BY W.proj_id
ORDER BY W.proj_id;

[예제2 추가 변형: 프로젝트 참여 인원이 7명 이상인 프로젝트에 한정할 것]

SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE E.birth_date BETWEEN '1990-01-01' AND '1999-12-31' AND W.proj_id IN (
    SELECT proj_id
    FROM works_on
    GROUP BY proj_id
    HAVING COUNT(*) >= 7)
GROUP BY W.proj_id
ORDER BY W.proj_id;

• WHERE 절에서 7명 조건으로 필터링 해주어야 함
• WHERE 절에서 조건을 추가할 지, HAVING 절에서 조건을 추가할 지 주의

---

SELECT 최종 정리

SELECT attribute(s) or aggregate function(s)
FROM table(s)
WHERE condition(s)
GROUP BY group attribute(s)
HAVING group condition(s)
ORDER BY attribute(s)

• 개념적인 동작 순서
  1. FROM: 데이터를 추출할 테이블 선택
  2. WHERE: 테이블에 대한 조건 추가
  3. GROUP BY: 테이블의 특정 attribute(s)에 대해서 정렬
  4. HAVING: GROUP BY 과정에서 조건이 있다면 추가
  5. ORDER BY: 완성된 테이블의 특정 attribute(s)에 대해서 정렬
  6. SELECT: 최종 완성된 테이블에 대해 화면에 출력할 값 선택

---

강의 영상

시니어 백엔드 개발자가 알려주는 데이터베이스 개론 & SQL
인프런: 쿼리 안의 쿼리 (subquery)
인프런: three-valued logic
인프런: 테이블 조인 (join)
인프런: 그룹 짓기(grouping), 집계 함수(aggregate function), 정렬하기(ordering)