1. 오늘 학습한 주제
- 조인 수행 방식 —
NESTED LOOP JOIN,SORT MERGE JOIN,HASH JOIN - DRIVING TABLE과 DRIVEN TABLE
- 서브쿼리의 유형과 위치 — 스칼라 서브쿼리, 인라인 뷰,
WHERE서브쿼리 - 단일행 서브쿼리
- 집계 결과로 행을 찾는 방법
- 다중열 서브쿼리와
IN - 다중행 서브쿼리 —
ANY,ALL - TOP-N 쿼리 —
LIMIT,OFFSET - 상관관계 서브쿼리
- 워크북 실습 Q15 ~ Q19
- DDL —
CREATE, 제약조건,DEFAULT,CHECK
2. 배운 내용
조인 수행 방식
조인은 문법이 같아도 내부적으로 수행되는 방식이 나뉜다.
NESTED LOOP JOINSORT MERGE JOINHASH JOIN
여기서 먼저 읽는 쪽이 DRIVING TABLE, 그에 맞춰 탐색되는 쪽이 DRIVEN TABLE이다. 어느 테이블을 DRIVING TABLE로 선정하는지가 성능에 중요하고, 인덱스 유무에 따라 수행 속도 차이가 발생한다.
서브쿼리도 동작 방식으로 나눌 수 있다.
- NESTED 서브쿼리
- 상호연관 서브쿼리
스칼라 서브쿼리는 서브쿼리를 실행할 결과 집합이 소량이거나, 실행계획이 비효율적으로 수립될 가능성이 있는 경우에 사용한다.
서브쿼리의 유형과 위치
서브쿼리란?
- 하나의 쿼리가 다른 쿼리를 포함하는 구조
- 유형
- 단일행(단일열, 다중열) : 일반 연산자
- 다중행(단일열, 다중열) : ANY, ALL, IN
- 위치
- SELECT(SCALAR SUBQUERY), FROM(INLINE VIEW), WHERE(SUBQUERY)반환되는 행이 하나면 =, > 같은 일반 연산자를 쓰고, 여러 행이면 ANY, ALL, IN을 쓴다. 위치에 따라 부르는 이름이 달라지고, FROM에 들어간 서브쿼리는 하나의 테이블처럼 취급되므로 별칭이 필요하다.
단일행 서브쿼리
비교 대상이 되는 값을 미리 알 수 없을 때 그 값을 구하는 쿼리를 조건 자리에 넣는다.
-- Q) 나승원 사원과 같은 직급이고 나승원 사원보다 많은 급여를 받는 사원의 정보를 검색한다면?
SELECT *
FROM employee
WHERE JOB_ID = ( SELECT JOB_ID
FROM employee
WHERE EMP_NAME = '나승원')
AND SALARY > ( SELECT SALARY
FROM employee
WHERE EMP_NAME = '나승원');조건 두 개가 각각 별도의 서브쿼리다. 두 서브쿼리 모두 한 행 한 열을 반환하므로 일반 연산자를 그대로 쓴다.
집계 결과로 행을 찾기
집계 함수의 결과와 그 행의 정보를 한 번에 뽑으려고 하면 문제가 생긴다.
-- ERROR
SELECT E.*
,MIN(SALARY)
FROM employee E;MIN(SALARY)는 전체에서 한 값으로 줄어들지만 E.*는 어느 행을 가리키는지 정해지지 않는다. 수업 파일에는 -- ERROR 주석이 붙어 있는데, MariaDB의 기본 sql_mode에서는 이런 형태가 오류 없이 실행되면서 집계 대상이 아닌 컬럼에 임의의 값이 들어가는 경우도 있어 실행 환경에 따라 결과가 달라질 수 있다.
값을 먼저 구해 조건으로 넘기면 해결된다.
SELECT *
FROM employee
WHERE SALARY = ( SELECT MIN(SALARY)
FROM employee);인라인 뷰 — 부서별 급여 총합이 가장 높은 부서
같은 문제를 그룹 단위로 확장한 것이다. 먼저 부서별 급여 총합을 구하는 쿼리를 FROM에 넣었다.
-- Q) 부서별 급여총합이 가장 높은 부서의 부서명과 급여총합을 검색한다면?
SELECT v.DEPT_NAME
,MAX(v.SUM)
FROM ( SELECT d.DEPT_NAME
,SUM(e.SALARY) AS `SUM`
FROM department d
JOIN employee e ON (d.DEPT_ID = e.DEPT_ID)
GROUP BY DEPT_NAME) v;MAX(v.SUM)은 최대값 하나로 줄어드는데 v.DEPT_NAME은 그에 대응하는 값이라는 보장이 없다. 위의 -- ERROR 예제와 같은 구조다.
HAVING에 서브쿼리를 걸어보는 형태도 확인했다.
SELECT d.DEPT_NAME
,SUM(e.SALARY)
FROM department d
JOIN employee e ON (d.DEPT_ID = e.DEPT_ID)
GROUP BY DEPT_NAME
HAVING SUM(SALARY) IN ( SELECT SUM(SALARY)
FROM department d
JOIN employee e ON (d.DEPT_ID = e.DEPT_ID)
GROUP BY DEPT_NAME);서브쿼리가 모든 부서의 총합을 그대로 반환하므로 조건으로는 아무것도 걸러지지 않는다. 비교 대상을 최대값 하나로 좁혀야 한다.
정렬 후 한 건만 끊는 방법.
SELECT D.DEPT_NAME
,SUM(E.SALARY) AS S
FROM employee E
JOIN department D ON (E.DEPT_ID = D.DEPT_ID)
GROUP BY E.DEPT_ID
ORDER BY S DESC
LIMIT 1;최대값을 구한 뒤 HAVING으로 비교하는 방법.
SELECT D.DEPT_NAME, SUM(E.SALARY)
FROM department D
JOIN employee E ON (D.DEPT_ID = E.DEPT_ID)
GROUP BY DEPT_NAME
HAVING SUM(E.SALARY) = (
SELECT MAX(SUM_SALARY)
FROM (
SELECT SUM(SALARY) AS `SUM_SALARY`
FROM department D
JOIN employee E ON (D.DEPT_ID = E.DEPT_ID)
GROUP BY DEPT_NAME
) V
);HAVING은 그룹에 대한 조건이므로 SUM()을 조건에 쓸 수 있다. 안쪽 인라인 뷰가 부서별 총합 목록을 만들고, 바깥 서브쿼리가 그중 최대값 하나를 반환한다. LIMIT 1과 달리 동점인 부서가 여러 개면 모두 나온다.
다중열 서브쿼리
비교할 값이 두 개 이상이면 컬럼을 묶어서 IN으로 비교한다.
-- Q) 부서별 최소급여를 받는 사원의 이름, 부서명, 급여 검색한다면?
SELECT E.EMP_NAME
,D.DEPT_NAME
,E.SALARY
FROM employee E
JOIN department D ON (E.DEPT_ID = D.DEPT_ID)
WHERE (D.DEPT_NAME, E.SALARY) IN ( SELECT D.DEPT_NAME
,MIN(E.SALARY)
FROM employee E
JOIN department D ON (E.DEPT_ID = D.DEPT_ID)
GROUP BY D.DEPT_NAME);부서명만으로 비교하면 그 부서의 모든 사원이, 급여만으로 비교하면 다른 부서의 같은 급여가 걸린다. (부서명, 급여) 조합으로 비교해야 부서별 최소급여자가 나온다.
윈도우 함수로도 같은 결과를 만들 수 있다.
SELECT SUB.EMP_NAME
,SUB.DEPT_NAME
,SUB.SALARY
FROM (
SELECT E.EMP_NAME
,D.DEPT_NAME
,E.SALARY
,RANK() OVER(PARTITION BY E.DEPT_ID ORDER BY E.SALARY) AS `RNK`
FROM employee E
JOIN department D ON (E.DEPT_ID = D.DEPT_ID)
) SUB
WHERE SUB.RNK = 1;부서별로 급여 순위를 매겨두고 1위만 남기는 방식이다.
다중행 서브쿼리 — ANY, ALL
-- ANY, < ANY
-- ALL, < ALL
-- 그냥 영어로 해석하면 편함
-- ANY -> SMALLER THAN ANY VALUE, BIGGER THAN ANY VALUE
-- ALL -> SMALLER THAN ALL VALUE, BIGGER THAN ALL VALUEANY는 서브쿼리 결과 중 하나만 만족하면 되고, ALL은 전부를 만족해야 한다. 연산자와 조합했을 때의 경우의 수를 확인하느라 아래 쿼리에는 < ANY가 들어가 있다. < ANY는 서브쿼리 결과 중 가장 큰 값보다 작다는 뜻이 된다.
SELECT E.EMP_NAME AS `이름`
,J.JOB_TITLE AS `직급`
,E.SALARY AS `급여`
FROM employee E
JOIN job J ON (E.JOB_ID = J.JOB_ID)
WHERE J.JOB_TITLE = '대리'
AND SALARY < ANY( SELECT E.SALARY
FROM employee E
JOIN job J ON (E.JOB_ID = J.JOB_ID)
WHERE J.JOB_TITLE = '과장');비교 전에 각 직급의 급여를 따로 확인해 두었다.
-- 과장직급의 급여확인
SELECT E.EMP_NAME AS `이름`
,J.JOB_TITLE AS `직급`
,E.SALARY AS `급여`
FROM employee E
JOIN job J ON (E.JOB_ID = J.JOB_ID)
WHERE J.JOB_TITLE = '과장';TOP-N 쿼리
-- TOP - N QUERY === LIMIT
-- LIMIT ~ OFFSET
-- OFFSET : M+1 번지부터 N번지까지 범위검색SELECT E.DEPT_ID
,SUM(SALARY)
FROM employee E
GROUP BY DEPT_ID
ORDER BY 2 DESC
LIMIT 3 OFFSET 0;ORDER BY 2는 SELECT 목록의 두 번째 컬럼을 뜻한다. OFFSET은 건너뛸 행 수이므로 OFFSET 0이면 첫 행부터 3건이다.
상관관계 서브쿼리
같은 문제를 세 가지로 풀었다. 급여는 계산의 편의를 위해 TRUNCATE(값, -5)로 정수 5자리까지 절삭한다.
-- Q) 자기 직급의 평균급여를 받는 직원의 이름, 직급명, 급여를 검색한다면?
SELECT EMP_NAME
,JOB_TITLE
,SALARY
FROM job J
JOIN employee E ON (J.JOB_ID = E.JOB_ID)
WHERE (J.JOB_ID, E.SALARY) IN ( SELECT JOB_ID, TRUNCATE(AVG(SALARY), -5)
FROM employee
GROUP BY JOB_ID);-- CASE 02
SELECT E.EMP_NAME
,J.JOB_TITLE
,E.SALARY
FROM ( SELECT JOB_ID
,TRUNCATE(AVG(SALARY), -5) AS `JOB_AVG`
FROM employee
GROUP BY JOB_ID) V
JOIN employee E ON (V.JOB_ID = E.JOB_ID AND V.JOB_AVG = E.SALARY)
JOIN job J ON (E.JOB_ID = J.JOB_ID);앞의 둘은 직급별 평균 목록을 미리 만들어두고 비교한다. 세 번째는 서브쿼리가 바깥 쿼리의 E.JOB_ID를 참조한다.
-- CASE 03
-- GROUP BY 없이도 상관관계 서브쿼리로 처리
SELECT EMP_NAME
,JOB_TITLE
,SALARY
FROM job J
JOIN employee E ON (J.JOB_ID = E.JOB_ID)
WHERE E.SALARY = ( SELECT TRUNCATE(AVG(SALARY), -5)
FROM employee E2
WHERE E2.JOB_ID = E.JOB_ID);서브쿼리가 바깥 행의 값을 받아 쓰기 때문에 혼자서는 실행되지 않는다. 바깥 쿼리 쪽에 GROUP BY가 없어도 되는 이유는 그룹 계산이 서브쿼리 안에서 행마다 이루어지기 때문이다.
워크북 실습 — Q15 ~ Q19
Q15는 HAVING으로 거르는 방법과, 평점을 먼저 구한 인라인 뷰를 조인하는 방법 두 가지로 작성했다.
-- Q15)
SELECT S.STUDENT_NO AS `학번`
,S.STUDENT_NAME AS `이름`
,D.DEPARTMENT_NAME AS `학과 이름`
,AVG(G.`POINT`) AS `평점`
FROM tb_student S
JOIN tb_department D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE S.ABSENCE_YN = 'N'
GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(G.`POINT`) >= 4.0;SELECT S.STUDENT_NO AS `학번`
,S.STUDENT_NAME AS `이름`
,D.DEPARTMENT_NAME AS `학과 이름`
,V.`평점` AS `평점`
FROM tb_student S
JOIN tb_department D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN (SELECT STUDENT_NO, AVG(`POINT`) AS `평점`
FROM tb_grade
GROUP BY STUDENT_NO
HAVING AVG(`POINT`) >= 4.0) V ON (S.STUDENT_NO = V.STUDENT_NO)
WHERE S.ABSENCE_YN = 'N';Q16은 전공 과목만 걸러 과목별 평점을 낸다. 학과 이름으로 조건을 걸어야 해서 tb_department까지 조인한다.
-- Q16)
SELECT C.CLASS_NO
,C.CLASS_NAME
,AVG(G.`POINT`)
FROM tb_class C
JOIN tb_grade G ON (C.CLASS_NO = G.CLASS_NO)
JOIN tb_department D ON (C.DEPARTMENT_NO = D.DEPARTMENT_NO)
WHERE D.DEPARTMENT_NAME = '환경조경학과'
AND C.CLASS_TYPE LIKE '전공%'
GROUP BY C.CLASS_NO, C.CLASS_NAME;Q17은 같은 테이블을 서브쿼리로 다시 조회해 학과 번호를 얻는다.
-- Q17)
SELECT STUDENT_NAME, STUDENT_ADDRESS
FROM tb_student S
WHERE S.DEPARTMENT_NO = ( SELECT DEPARTMENT_NO
FROM tb_student S2
WHERE S2.STUDENT_NAME = '최경희');Q18은 인라인 뷰의 최대값과 비교하는 방법과, >= ALL로 비교하는 방법 두 가지다.
-- Q18)
SELECT S.STUDENT_NO
,S.STUDENT_NAME
FROM tb_student S
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE S.DEPARTMENT_NO = '001'
GROUP BY S.STUDENT_NO, S.STUDENT_NAME
HAVING AVG(G.`POINT`) = (SELECT MAX(V.AVG)
FROM (
SELECT AVG(G.`POINT`) AS `AVG`
FROM tb_student S
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE S.DEPARTMENT_NO = '001'
GROUP BY S.STUDENT_NO
) V);-- Q18)
SELECT S.STUDENT_NO,
S.STUDENT_NAME
FROM tb_student S
JOIN tb_department D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE D.DEPARTMENT_NAME = '국어국문학과'
GROUP BY S.STUDENT_NO, S.STUDENT_NAME
HAVING AVG(G.`POINT`) >= ALL( SELECT AVG(G2.`POINT`)
FROM tb_grade G2
JOIN tb_student S2 ON (G2.STUDENT_NO = S2.STUDENT_NO)
JOIN tb_department D2 ON (S2.DEPARTMENT_NO = D2.DEPARTMENT_NO)
WHERE D2.DEPARTMENT_NAME = '국어국문학과'
GROUP BY G2.STUDENT_NO);>= ALL은 서브쿼리가 반환한 모든 평점보다 크거나 같다는 뜻이므로 최대값과 비교하는 것과 같다.
Q19는 기준 학과의 계열을 서브쿼리로 구해 같은 계열 전체에 조건을 건다.
-- Q19)
SELECT D.DEPARTMENT_NAME AS `계열 학과명`
,ROUND(AVG(G.`POINT`), 1) AS `전공평점`
FROM tb_student S
JOIN tb_department D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
JOIN tb_class C ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO)
WHERE D.CATEGORY = ( SELECT CATEGORY
FROM tb_department
WHERE DEPARTMENT_NAME = '환경조경학과')
AND C.CLASS_TYPE LIKE '전공%'
GROUP BY D.DEPARTMENT_NAME;DDL
-- DDL : 물리적 저장공간인 테이블 생성과 논리적 저장공간인 VIEW
-- CREATE, ALTER, DROP
-- CONSTRAINT : PRIMARY KEY, REFERENCE KEY(FOERIGN KEY), NOT NULL, UNIQUE, CHECK
-- DEFAULT : DML NULL 값이 입력될 때 DEFAULT로 선언된 값이 자동으로 할당CREATE TABLE TABLE_NAME (
COLUMN_NAME DATATYPE(SIZE) [DEFAULT] [CONSTRAINT TYPE],
[CONSTRAINT TYPE]
);
OBJECT : TABLE, VIEW, INDEX, SEQUENCE, ETC, ...테이블은 물리적 저장공간이고 VIEW는 논리적 저장공간이다. DB와 테이블 모두 IF EXISTS로 지운 뒤 다시 만든다.
DROP DATABASE IF EXISTS TABLEDB;
CREATE DATABASE TABLEDB;
USE TABLEDB;
DROP TABLE IF EXISTS TB_BLOG;
CREATE TABLE TB_BLOG(
BLOG_ID VARCHAR(50),
TITLE VARCHAR(100),
CONTENT VARCHAR(100),
EMAIL VARCHAR(100),
CREATE_AT DATE,
PRIMARY KEY (BLOG_ID) -- TABLE CONSTRAINT
);제약조건은 컬럼 뒤에 붙이는 컬럼 레벨과, 컬럼 정의가 끝난 뒤 따로 적는 테이블 레벨 두 가지로 쓸 수 있다.
-- 컬럼 레벨
BLOG_ID VARCHAR(50) PRIMARY KEY,-- 테이블 레벨
PRIMARY KEY (BLOG_ID)DEFAULT
DEFAULT는 DML에서 NULL 값이 입력될 때 선언된 값을 자동으로 할당한다.
CREATE TABLE TB_BLOG(
BLOG_ID VARCHAR(50),
TITLE VARCHAR(100),
CONTENT VARCHAR(100),
EMAIL VARCHAR(100),
CREATE_AT DATE DEFAULT SYSDATE(),
PRIMARY KEY (BLOG_ID) -- TABLE CONSTRAINT
);CREATE_AT을 컬럼 목록에서 빼고 입력하면 값을 넘기지 않아도 SYSDATE()가 들어간다.
INSERT INTO tb_blog(BLOG_ID, TITLE, CONTENT, EMAIL)
VALUES('1', '휴', '하루 마무리', 'medtate@gmail.com');PRIMARY KEY 컬럼에 NULL을 넣어보는 확인도 함께 했다.
INSERT INTO tb_blog(BLOG_ID, TITLE, CONTENT, EMAIL, CREATE_AT)
VALUES(NULL, '휴', '하루 마무리', 'medtate@gmail.com', SYSDATE());CHECK
컬럼에 들어올 수 있는 값 자체를 제한한다.
CREATE TABLE TB_BLOG(
BLOG_ID VARCHAR(50),
TITLE VARCHAR(100),
CONTENT VARCHAR(100),
EMAIL VARCHAR(100),
CATEGORY VARCHAR(100) CHECK(CATEGORY IN ('전체', '취미', '일상', '코딩')), -- COLUMN LEVEL CONSTRAINT, 정해진 값만 들어올 수 있음
CREATE_AT DATE DEFAULT SYSDATE(),
PRIMARY KEY (BLOG_ID) -- TABLE CONSTRAINT
);INSERT INTO tb_blog(BLOG_ID, CATEGORY)
VALUES('1', '전체');목록에 없는 값이 들어오면 입력 단계에서 막히므로, 애플리케이션이 아니라 DB가 값의 범위를 보장한다.
3. 헷갈렸던 부분
WHERE와 GROUP BY의 순서
절을 어느 순서로 적어야 하는지 헷갈렸다. WHERE가 먼저고 GROUP BY가 뒤다.
FROM tb_student S
JOIN tb_department D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
WHERE S.ABSENCE_YN = 'N'
GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(G.`POINT`) >= 4.0;작성 순서는 곧 처리 순서다. 행을 먼저 걸러내고 남은 행으로 그룹을 묶는다. Q15에서 휴학생을 WHERE로 제외하는 것이 평점 계산보다 앞에 오는 이유다.
WHERE에는 집계 함수를 쓸 수 없다
평점 조건도 WHERE에 넣으려다 막혔다.
-- 불가
WHERE AVG(G.`POINT`) >= 4.0WHERE가 실행되는 시점에는 아직 그룹이 만들어지지 않아 AVG()를 계산할 대상이 없다. 그룹에 대한 조건은 HAVING으로 간다.
GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAME
HAVING AVG(G.`POINT`) >= 4.0;GROUP BY에는 SELECT의 컬럼을 웬만하면 다 적는다
GROUP BY에 기준 컬럼 하나만 적고 나머지는 생략해도 되는지 헷갈렸다. 집계 함수를 거치지 않는 컬럼은 웬만하면 전부 나열하는 편이 안전하다.
SELECT S.STUDENT_NO AS `학번`
,S.STUDENT_NAME AS `이름`
,D.DEPARTMENT_NAME AS `학과 이름`
,AVG(G.`POINT`) AS `평점`
...
GROUP BY S.STUDENT_NO, S.STUDENT_NAME, D.DEPARTMENT_NAMEMariaDB 기본 sql_mode에서는 빠뜨려도 실행되는 경우가 있지만, 그때 그룹 기준이 아닌 컬럼에는 임의의 값이 들어갈 수 있다. 앞의 -- ERROR 예제나 인라인 뷰에 MAX()만 걸었던 쿼리와 같은 문제다.
조인 순서가 결과를 바꾸는 줄 알았다
Q19처럼 조인이 세 개 붙으면 어느 테이블부터 적어야 하는지 고민했다.
FROM tb_student S
JOIN tb_department D ON (S.DEPARTMENT_NO = D.DEPARTMENT_NO)
JOIN tb_grade G ON (S.STUDENT_NO = G.STUDENT_NO)
JOIN tb_class C ON (D.DEPARTMENT_NO = C.DEPARTMENT_NO)조인 조건이 같다면 적는 순서를 바꿔도 결과 집합은 같다. 다만 앞에서 정리한 DRIVING TABLE 선정과 수행 방식은 달라질 수 있으므로, 결과가 아니라 성능 쪽에서 의미가 있는 문제다.
