[우리FISA] SQL, sub queries and so on..

CASE WHEN ELSE END

Nested Query

• CASE문은 조건에 따라 다른 결과를 반환한다.
• CASE문은 IF-THEN-ELSE문과 유사하다.
• CASE문은 단순 CASE문과 검색 CASE문으로 나뉜다.
  • 단순 CASE문: CASE column_name WHEN value1 THEN result1 WHEN value2 THEN result2 ELSE result3 END
  • 검색 CASE문: CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE result3 END
• 단순 CASE문은 특정 열의 값을 비교한다.
• 검색 CASE문은 조건을 비교한다.

# 단순 CASE문
SELECT column_name,
       CASE column_name
         WHEN value1 THEN result1
         WHEN value2 THEN result2
         ELSE result3
       END AS new_column_name
  FROM table_name;

# 검색 CASE문
SELECT column_name,
       CASE
         WHEN condition1 THEN result1
         WHEN condition2 THEN result2
         ELSE result3
       END AS new_column_name
  FROM table_name;

• CASE문은 SELECT문, WHERE문, ORDER BY문, HAVING문에서 사용할 수 있다.

Sub Query

Outer Query 에 대비되는 개념으로 Inner Query 라고도 한다

• 서브쿼리는 다른 쿼리 안에 중첩된 쿼리이다.
  • 쿼리 안쪽에 쿼리를 넣을수 있다.
• 내부쿼리는 2개 이상 여러개가 하나의 외부쿼리 안에 쓰일 수도 있다.
  • 외부쿼리 (내부쿼리) 외부쿼리 (내부쿼리2) 외부쿼리
• subquery로 할 수 있는 일은 join으로도 다 할 수 있다.
• DBMS는 종류가 여러가지인데, 문법도 여러가지인 경우가 많다.
  • 데이터가 많은 경우에는 서브쿼리가 속도 면에서 우월하다.
  • 컬럼에 있는 전체 ROW를 붙여서 그 다음에 탐색을 하는 JOIN vs SUBQUERY는 내부쿼리의 결과 → 외부쿼리에서 조회만 하면 되기 때문에 속도가 빠르다.
• 서브쿼리는 메인쿼리(외부쿼리)안에 포함된 하나의 독립적인 SELECT문이다.
• 메인쿼리의 어느 곳에 위치하느냐에 따라
  • 특정 값을 반환하기도 하고,
  • 테이블처럼 사용되기도 하며,
  • 조건을 확인하는데 사용되기도 함.

SQL 실행순서

사실 사용하는 RDBMS 에 따라서 차이가 있다

• FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT

SELECT column_name
  FROM table_name
 WHERE column_name = (SELECT column_name FROM table_name WHERE condition);

위 쿼리는 다음과 같은 순서로 실행된다.

1. 서브쿼리가 먼저 실행된다.
2. 서브쿼리의 결과를 이용해 메인 쿼리가 실행된다.
3. 메인 쿼리의 결과가 반환된다.

• 서브쿼리는 메인 쿼리의 조건절, SELECT문, FROM절에 사용할 수 있다.
• 서브쿼리는 단일 행 서브쿼리와 다중 행 서브쿼리로 나뉜다.
  • Single Column Sub Query: 서브쿼리의 결과가 1개 행
  • Multi Column Sub Query: 서브쿼리의 결과가 여러 행

Sub Query 재사용 및 활용

• 서브쿼리는 메인 쿼리에서 여러 번 사용할 수 있다.

SELECT column_name
  FROM table_name
 WHERE column_name = (SELECT column_name FROM table_name WHERE condition)
    OR column_name = (SELECT column_name FROM table_name WHERE condition);

• 서브쿼리를 JOIN으로 변경할 수 있다.

SELECT column_name
  FROM table_name
  JOIN (SELECT column_name FROM table_name WHERE condition) AS sub_table
    ON table_name.column_name = sub_table.column_name;

• 서브쿼리를 WITH로 변경할 수 있다.

WITH sub_table AS (
  SELECT column_name
    FROM table_name
   WHERE condition
)
SELECT column_name
  FROM table_name
  JOIN sub_table
    ON table_name.column_name = sub_table.column_name;

• WITH는 서브쿼리의 결과를 임시 테이블로 저장한다.

Sub Query 재귀적 활용

wow..

• 서브쿼리를 재귀적으로 사용할 수 있다.

WITH RECURSIVE sub_table AS (
  SELECT column_name
    FROM table_name
   WHERE condition
  UNION ALL
  SELECT column_name
    FROM table_name
   WHERE condition
)
SELECT column_name
  FROM sub_table;

• RECURSIVE는 서브쿼리를 반복적으로 실행한다.
  • UNION ALL은 중복을 포함한 모든 결과를 반환한다.
  • UNION은 중복을 제외한 결과를 반환한다.
  • INTERSECT는 교집합 결과를 반환한다.
  • EXCEPT는 차집합 결과를 반환한다.
    • MINUS는 차집합 결과를 반환한다.
• RECURSIVE는 WITH문 뒤에 사용한다.

위 쿼리는 다음과 같은 내용을 수행

1. 서브쿼리가 먼저 실행된다.
2. 서브쿼리의 결과를 이용해 메인 쿼리가 실행된다.
3. 메인 쿼리의 결과가 반환된다.
4. 서브쿼리의 결과를 이용해 서브쿼리가 실행된다.
5. 서브쿼리의 결과를 이용해 메인 쿼리가 실행된다.
6. 메인 쿼리의 결과가 반환된다.
7. 4~6을 반복한다. (재귀적)

Sub Query 종류

스칼라(scalar) 서브쿼리

• SELECT** 절에 있는 서브쿼리 → 단일값을 반환
• 쿼리를 한 번 작성하면 재활용하는 경우가 많다 → 고치기가 좀 복잡하다
• 유지보수하기가 좀 까다로운 단점이 있다.

SELECT 칼럼1, 칼럼2, ...  
        (SELECT ...
          FROM ...
          WHERE ...
        ) [AS] 별칭1
FROM ...
WHERE ... ;

SELECT e.ename, (SELECT d.dname='ACCOUNTING'
        FROM dept d
        WHERE e.deptno = d.deptno
        ) AS ACCOUNTING 부서확인
FROM emp e;

• 스칼라 서브쿼리의 SELECT 절에는 단일 칼럼(표현식)이 와야 하며 여러 개의 칼럼(표현식)을 기술할 수 없다.
  • 또한 스칼라 서브쿼리는 단일 건(로우)를 반환해야 하고, 여러 로우를 반환하면 오류가 발생하다.

파생(derived) 테이블

• FROM** 절에 있는 서브쿼리 → 조건에 맞는 파생 테이블을 반환, 별명을 붙여서 외부쿼리에서 사용한다

SELECT 칼럼1, 칼럼2, ...
  FROM 테이블1 [AS] 별칭1,
        (SELECT ...
          FROM ...
          WHERE ...
        ) [AS] 별칭2,
        ...
  WHERE ... ;
-- 닫는 괄호 다음에 반드시! 파생 테이블의 별칭을 명시해야 하다.

SELECT a.deptno, a.dname,
        mgr.empno
  FROM dept a,
        (SELECT b.deptno, b.empno, c.ename
            FROM emp b,
                  emp c
            WHERE b.empno = c.empno
        ) mgr
  WHERE a.deptno = mgr.deptno
  ORDER BY 1;

-- 서브쿼리가 반환하는 결과 집합을 하나의 테이블처럼 사용할 수 있다. 
SELECT b.deptno, b.empno, c.ename
  FROM emp b,
        emp c
  WHERE b.empno = c.empno;

SELECT YEAR(a.release_date), a.ranks, a.movie_name,
        ROUND(a.sale_amt / b. total_amt * 100, 2) percentage
  FROM box_office a
  INNER JOIN (SELECT YEAR(release_date) years, SUM(sale_amt) total_amt
                FROM box_office
              WHERE YEAR(release_date) >= 2015
              GROUP BY 1
              ) b
    ON YEAR(a.release_date) = b.years
  WHERE a.ranks <= 3
  ORDER BY 1, 2;

• 파생 테이블은 별칭을 반드시 명시해야 한다.
• 파생 테이블을 구성하는 서브쿼리의 SELECT 절에 명시한 칼럼만 메인쿼리에서 참조할 수 있으며, 칼럼에 별칭을 사용하면 별칭으로 참조할 수 있다.
• 메인쿼리의 FROM 절에는 1개 이상의 파생 테이블을 사용할 수 있다.

SELECT a.dept_no, a.dept_name,
        mng.emp_no, mng.first_name, mng.last_name
  FROM departments a,
        LATERAL
        (SELECT b.dept_no, b.emp_no, c.first_name, c.last_name
          FROM dept_manager b, employees c
          WHERE b.emp_no = c.emp_no
            AND SYSDATE() BETWEEN b.from_date AND b.to_date
            AND a.dept_no = b.dept_no
        ) mng
  ORDER BY 1;

WHERE 절의 서브쿼리

• 특정 데이터를 걸러내기 위한 일반 조건이나 조회 조건을 기술
  • 비교 연산자 또는 ANY(~ 중 하나), SOME(하나라도 있으면), ALL(모두) 연산자를 사용
• 조인보다는 필터 조건에 주로 사용

SELECT ranks, movie_name, sale_amt
  FROM box_office
  WHERE YEAR(release_date) = 2019
    AND sale_amt >= (SELECT MAX(sale_amt)
                      FROM box_office
                      WHERE YEAR(release_date) = 2018);

SELECT ranks, movie_name, sale_amt
  FROM box_office
  WHERE YEAR(release_date) = 2019
    AND sale_amt >= ALL (SELECT sale_amt
                          FROM box_office
                          WHERE YEAR(release_date) = 2018
                            AND ranks BETWEEN 1 AND 3);

• IN, NOT IN과 EXISTS 연산자를 사용한 조건 서브쿼리

-- 다른 연산자 없이 단독으로 사용.
SELECT ranks, movie_name, director
  FROM box_office
  WHERE YEAR(release_date) = 2019
    AND movie_name IN (SELECT movie_name
                        FROM box_office
                        WHERE YEAR(release_date) = 2018);

SELECT ranks, movie_name, release_date, sale_amt, rep_country
  FROM box_office
  WHERE YEAR(release_date) = 2019
    AND ranks BETWEEN 1 AND 100
    AND rep_country NOT IN (SELECT rep_country
                              FROM box_office
                            WHERE YEAR(release_date) = 2018
                              AND ranks BETWEEN 1 AND 100);

-- EXISTS 연산자는 메인쿼리 테이블의 값 중에서 서브쿼리의 결과 
-- 집합에 존재하는 건이 있는지를 확인하는 역할
SELECT ranks, movie_name, director
  FROM box_office a
  WHERE YEAR(release_date) = 2019
    AND EXISTS (SELECT 1
                  FROM box_office b
                WHERE YEAR(release_date) = 2018
                  AND a.movie_name = b.movie_name);

• 조건 서브쿼리와 EXISTS 연산자를 사용한 조인을 세미조인,
• NOT EXISTS 연산자를 사용한 조인을 안티조인이라고 하다.

UNION / UNION ALL / INTERSECT / EXCEPT / MINUS

• UNION은 중복을 제외한 결과를 반환한다.
• UNION ALL은 중복을 포함한 모든 결과를 반환한다.
• INTERSECT는 교집합 결과를 반환한다.
• EXCEPT는 차집합 결과를 반환한다.
  • MINUS는 차집합 결과를 반환한다.

SELECT column_name
  FROM table_name1
UNION
SELECT column_name
  FROM table_name2;

SELECT column_name
  FROM table_name1
UNION ALL
SELECT column_name
  FROM table_name2;

• UNION과 UNION ALL은 결과 집합의 칼럼 수와 데이터 타입이 일치해야 한다.

ALTER TABLE

• ALTER TABLE은 테이블의 구조를 변경한다.
• ALTER TABLE은 ADD, MODIFY, DROP으로 구분된다.
  • ADD: 열을 추가한다.
  • MODIFY: 열의 속성을 변경한다.
  • DROP: 열을 삭제한다.

ALTER TABLE table_name
  ADD column_name data_type;

ALTER TABLE table_name
  MODIFY column_name data_type;

ALTER TABLE table_name
  DROP column_name;

• ALTER TABLE은 열을 추가할 때 NOT NULL, DEFAULT, CHECK 제약 조건을 추가할 수 있다.