개발할 때도 그렇고 업무를 하면서 자료 추출 등의 일이 점점 많아지면서 쿼리를 한번 정리해두면 좋겠다는 생각을 했다.
Inner Join
> 기본 사용법
Left Outer Join
> 기본 사용법
SELECT A.EMPNO
,A.ENAME
,A.JOB
,A.DEPTNO
,B.DNAME
FROM EMP A, DEPT B
WHERE A.DEPTNO = B.DEPT(+)
기준 테이블에 해당하는 값이 없을 경우 NULL이 표시된다.
Full Outer Join
> 기본 사용법
Decode
Decode는 If Else와 같은 동작을 수행한다.
요즘은 CASE WHEN 구문 사용을 권장하긴 하지만 Decode문을 사용하고 있는 쿼리를 해석하려면 알아두는게 좋을 것 같다.
> If Else
SELECT ENAME
,COMM
,DECODE(COMM, NULL, 'N', 'Y') COMM_YN
FROM EMP
WHERE EMPNO IN ('1234', '5678')
> 조건 여러 개
SELECT ENAME
,DEPTNO
,DECODE(DEPTNO, '10', 'NEW YORK'
, '20', 'DALLAS'
, '30', 'CHICAGO'
, '40', 'BOSTON') LOC
FROM EMP
WHERE EMPNO IN ('1234', '5678')
> 행을 열로 바꾸기
SELECT SUM(DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1)) "1월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1)) "2월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1)) "3월"
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1)) "4월"
...
,SUM(DECODE(TO_CHAR(HIREDATE, 'MM'), '01', 1)) "12월"
FROM EMP
CASE WHEN
> 일반적인 CASE WHEN
SELECT ENAME
,DEPTNO
,CASE WHEN DEPTNO = '10' THEN 'NEW YORK'
WHEN DEPTNO = '20' THEN 'DALLAS'
ELSE '기타'
END AS LOC_NAME
FROM EMP
> ELSE를 생략 후 만족하는 조건이 없으면 NULL 리턴
SELECT ENAME
,DEPTNO
,CASE WHEN DEPTNO = '10' THEN 'NEW YORK'
WHEN DEPTNO = '2' THEN 'DALLAS'
End AS LOC_NAME
FROM EMP
> 비교 연산자, 범위 연산자 등 사용 가능
SELECT DEPTNO
,SCORE
,CASE WHEN SCORE >= 95 THEN '1등급'
WHEN SCORE >= 85 THEN '2등급'
WHEN SCORE >= 75 THEN '3등급'
END AS GRADE
FROM EMP
> WHERE 절에 사용 가능
SELECT ENAME
,SCORE
FROM EMP
WHERE (
CASE WHEN SCORE >= '95' THEN '1등급'
WHEN SCORE >= '85' THEN '2등급'
WHEN SCORE >= '75' THEN '3등급'
END
) = '1등급' -- 1등급인 직원 추출
Distinct
> 여러 개 컬럼
SELECT DISTINCT (DEPT_NAME, STORE_NAME)
FROM DEPARTMENT
Group By
> 기본적인 GROUP BY
SELECT NAME, AGE
FROM CUSTOMERS
GROUP BY NAME, AGE
> 일, 시간 별로 그룹핑
SELECT
> HAVING
SELECT CLASS
,SUM(SCORE)
FROM STUDENT
GROUP BY CLASS
HAVING SUM(SCORE) > 150
Order By
> 기본적인 Order By
SELECT EMPNO
,ENMAE
,JOB
,SAL
FROM EMP
ORDER BY SAL
> 별칭으로 정렬
SELECT EMPNO
,ENMAE
,JOB
,SAL
,CASE WHEN SAL > 2000 THEN '1등급'
WHEN SAL > 1000 THEN '2등급'
ELSE '3등급'
END AS SAL_GRADE
FROM EMP
ORDER BY SAL_GRADE
> SELECT 절에 없는 컬럼으로 정렬 (테이블에는 존재)
SELECT EMPNO
,ENMAE
,JOB
,SAL
FROM EMP
ORDER BY COMM
> ORDER BY 절에 함수 사용
SELECT EMPNO
,ENMAE
,JOB
,SAL
,GRADE
FROM EMP
ORDER BY CASE WHEN GRADE = "F" THEN 1
ELSE 2
END
,SAL DESC
> GROUP BY 절을 사용할 경우 ORDER BY 절에 그룹 함수(COUNT, SUM, MAX, MIN..)를 사용 가능
SELECT JOB
FROM EMP
GROUP BY JOB
ORDER BY JOB, SUM(SAL), MAX(COMM)
> ROWNUM과 함께 사용할 땐 정렬된 결과를 서브쿼리로 조회하여 ROWNUM으로 원하는 건수만큼 추출 (ROWNUM이 먼저 실행됨)
SELECT EMPNO
,ENAME
,JOB
,SAL
FROM (
SELECT EMPNO
,ENAME
,JOB
,SAL
FROM EMP
-- WHERE ROWNUM = 1 (x)
ORDER BY SAL DESC
)
WHERE ROWNUM = 1
기타
> 날짜 비교 (String 변환)
SELECT *
FROM EMP
WHERE JOIN_DATE < TO_CHAR(SYSDATE, 'YYYYMMDD')
> 날짜 비교 (Date 변환)
SELECT *
FROM EMP
WHERE SYSDATE > TO_DATE(JOIN_DATE, 'YYYYMMDD')
--TO_DATE(JOIN_DATE, 'YYYYMMDDHH24MISS' ex) 2024/01/01 00:00:00
> NVL
-- NVL(판단 대상, '판단 대상이 NULL일 때 대체할 값')
SELECT NVL(CATEGORY, 'ETC') CATEGORY
FROM PRODUCT
> NULLIF
-- 입력으로 받은 값이 기존과 다른지 확인하는 쿼리 (11 -> 동일 / 10 -> CONTENT만 다름 / 00 -> 둘 다 다름)
-- NULLIF(인자1, 인자2) -> 인자1과 인자 2가 동일하면 NULL 반환 / 다르면 인자1 반환
SELECT (CASE WHEN NULLIF(TITLE, :title) IS NULL THEN '1' ELSE 0 END) ||
(CASE WHEN NULLIF(CONTENT, :content) IS NULL THEN '1' ELSE 0 END) ||
AS FIXED_BIT
FROM POST
쿼리 최적화 팁
1. SELECT 시에는 꼭 필요한 컬럼만 불러온다.
-- Inefficient
SELECT * FROM MOVIE;
-- IMPROVED
SELECT id FROM MOVIE;- 많은 필드 값을 불러올수록 DB는 더 많은 로드를 부담하게 되고 데이터 전송에 필요한 리소스가 많아진다.
2. 조건 부여 시, 가급적이면 기존 DB값에 별도의 연산을 걸지 않는 것이 좋다.
-- Inefficient
SELECT m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count
FROM MOVIE m, RATING r
WHERE m.id = r.movie_id
AND FLOOR(r.value/2) = 2
GROUP BY m.id;
-- IMPROVED
SELECT m.id, ANY_VALUE(m.title) title, COUNT(r.id) r_count
FROM MOVIE m, RATING r
WHERE m.id = r.movie_id
AND r.value BETWEEN 4 AND 5
GROUP BY m.id;- Inefficient 쿼리의 경우, Full Table Scan을 하면서 모든 Cell 값을 탐색하고, 수식을 건 뒤, 조건 충족 여부를 판단해야 한다. 반면, Improved 쿼리의 경우 기존에 r.value가 가지고 있는 index를 그대로 활용할 수 있기 때문에 모든 필드 값을 탐색할 필요가 없어 Inefficient 쿼리 대비 더 짧은 Running Time을 가질 수 있다.
3. LIKE사용 시 와일드카드 문자열(%)을 String 앞부분에는 배치하지 않는 것이 좋습니다.
- 2번과 같은 원리이다. Index를 활용할 수 있는 value IN (...), value = "...", value LIKE "...%"와는 다르게, value LIKE "%..."는 Full Table Scan을 활용한다. 따라서 같은 결과를 낼 수 있다면, value LIKE "%..."보다는 다른 형태의 조건을 적용하는 것이 바람직하다.
- 예를 들면, 다양한 장르 중에서 Comedy와 Romantic Comedy를 추출하고 싶은 경우, LIKE "%Comedy"보다는, 다른 형태의 조건절을 사용하는 것이 효과적일 것이다.
-- Inefficient
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value LIKE "%Comedy"
GROUP BY g.value;
-- Improved(1): value IN (...)
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value IN ("Romantic Comedy", "Comedy")
GROUP BY g.value;
-- Improved(2): value = "..."
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value = "Romantic Comedy" OR g.value = "Comedy"
GROUP BY g.value;
-- Improved(3): value LIKE "...%"
-- 모든 문자열을 탐색할 필요가 없어, 가장 좋은 성능을 내었습니다
SELECT g.value genre, COUNT(r.movie_id) r_cnt
FROM rating r
INNER JOIN genre g
ON r.movie_id = g.movie_id
WHERE g.value LIKE "Romantic%" OR g.value LIKE "Comed%"
GROUP BY g.value;
4. SELECT DISTINCT, UNION DISTINCT와 같이 중복 값을 제거하는 연산은 최대한 사용하지 않아야 한다.
- 중복 값을 제거하는 연산은 많은 시간이 걸립니다. 만약 불가피하게 사용해야 하는 상황이라면, DISTINCT 연산을 대체하거나, 연산의 대상이 되는 테이블의 크기를 최소화하는 방법을 고민할 필요가 있겠습니다. 가장 대표적인 대체 방법으로는 EXISTS를 활용하는 방법이 있다.
-- Inefficient
SELECT DISTINCT m.id, title
FROM movie m
INNER JOIN genre g
ON m.id = g.movie_id;
-- Improved
SELECT m.id, title
FROM movie m
WHERE EXISTS (SELECT 'X' FROM rating r WHERE m.id = r.movie_id);
5. 같은 내용의 조건이라면, GROUP BY 연산 시에는 가급적 HAVING보다는 WHERE 절을 사용하는 것이 좋다.
- 쿼리 실행 순서에서, WHERE 절이 HAVING 절보다 먼저 실행됩니다. 따라서 WHERE 절로 미리 데이터 크기를 작게 만들면, GROUP BY에서 다뤄야 하는 데이터 크기가 작아지기 때문에 보다 효율적인 연산이 가능하다.
-- Inefficient
SELECT m.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM movie m
INNER JOIN rating r
ON m.id = r.movie_id
GROUP BY id
HAVING m.id > 1000;
-- Improved
SELECT m.id, COUNT(r.id) AS rating_cnt, AVG(r.value) AS avg_rating
FROM movie m
INNER JOIN rating r
ON m.id = r.movie_id
WHERE m.id > 1000
GROUP BY id ;
6. 3개 이상의 테이블을 INNER JOIN 할 때는, 크기가 가장 큰 테이블을 FROM 절에 배치하고, INNER JOIN 절에는 남은 테이블을 작은 순서대로 배치하는 것이 좋다.
- INNER JOIN 과정에서 최소한의 Combination을 탐색하도록 FROM & INNER JOIN의 순서를 배열하면 좋다는 이야기인데, 항상 통용되지는 않다.
- 간단한 INNER JOIN의 경우는 대부분의 Query Planner에서 가장 효과적인 순서를 탐색해 INNER JOIN의 순서를 바꾸기 때문이다. 예를 들어서, 아래의 두 쿼리는 서로 다른 INNER JOIN 순서를 가짐에도, 결과적으로 실행 시간에는 차이가 없다. (순서대로 INNER JOIN 된다면, Query (B)가 (A)보다 훨씬 적은 시간이 걸려야 할 것이다.)
-- Query (A)
SELECT m.title, r.value rating, g.value genre
FROM rating r
INNER JOIN genre g
ON g.movie_id = r.movie_id
INNER JOIN movie m
ON m.id = r.movie_id;
-- Query (B)
SELECT m.title, r.value rating, g.value genre
FROM rating r
INNER JOIN movie m
ON r.movie_id = m.id
INNER JOIN genre g
ON r.movie_id = g.movie_id;- 그러나 테이블의 개수가 늘어난다면, 탐색해야 할 INNER JOIN 순서의 경우의 수가 늘어나고, 이는 결국 Planning 비용의 증가로 이어진다. 그러다 보면 언젠가는 비싼 Planning 비용을 들이고 가장 최적의 순서를 찾는 것보다, 차선의 INNER JOIN 순서로 쿼리를 실행하더라도 Planning 비용을 줄이는 것이 결과적으로는 더 효과적인 상황이 올 것이다.
- 그렇기 때문에 복잡한 쿼리에서는 완전하게 최적화되지 않은 INNER JOIN 연산이 실행될 때가 있다. (개인적으로는 BigQuery를 사용할 때, 임시 테이블을 많이 만들다 보면 이 같은 상황이 발생하는 것을 종종 경험하곤 합니다) 따라서 이를 사전에 방지하기 위해 최적화된 INNER JOIN 순서를 입력 단계에서 조정해 두는 것은 분명 도움이 될 것입니다. INNER JOIN의 최적화 여부가 연산량에 미치는 영향력은 상당히 크기 때문이다.