서브쿼리

서브쿼리

서브쿼리란 SQL문 안에 부품처럼 들어가는 SELECT문 즉, 전체 SQL문에서 일부를 이루는 또 다른 SQL문을 말한다.
서브쿼리를 사용할 때는 소괄호로 감싸줘야 에러가 아니 않는다.
전체 SQL문을 outer query, 서브쿼리를 inner query라고도 한다.

SELECT절에 서브쿼리

SELECT절에 서브쿼리를 쓰는것은 원래 테이블에는 없던 새로운 컬럼을 추가해서 본다는 뜻. 컬럼의 최댓값, 평균값처럼 특정 컬럼의 특징을 찾아주는 서브쿼리를 자주쓴다.

SELECT
	id,
    name, 
	price,
    (SELECT AVG(price) FROM item) AS avg_price
FROM copang_main.item;

AVG(price)를 바로 쓰지 않은 것은 집계함수는 일반적으로 group by를 통해 쓰게 되는데, 예외적으로 허용되는게 SELECT절에서 단독으로 쓰이는 경우다.

WHERE절에 서브쿼리

SELECT id, name, price
FROM item
WHERE price = (SELECT MAX(price) FROM item);

WHERE절에도 서브쿼리를 사용할 수 있다.

여러개의 값을 리턴하는 서브쿼리

IN

WHERE 컬럼 IN(서브쿼리)

SELECT * FROM item
WHERE id IN
(
SELECT item_id
FROM review
GROUP BY item_id HAVING COUNT(*) >= 3
);

IN은 해당하는 값만 조회할 때 사용한다.
review테이블에서 item_id컬럼의 그룹내의 값의 개수가 3개 이상인 item_id컬럼을 리턴하게 된다.

ANY(SOME)

WHERE 컬럼 조건 ANY(서브쿼리)

WHERE 컬럼 > ANY(서브쿼리)

컬럼의 값이 서브쿼리가 리턴한 값들 중 단 하나의 값보다도 크다면 TRUE를 리턴한다.
SOME도 동일한 기능을 수행한다.

ALL

WHERE 컬럼 조건 ALL(서브쿼리)

컬럼의 값이 서브쿼리가 리턴한 값들 모두에 해당 조건이 성립해야 TRUE를 리턴한다.

FROM절에 서브쿼리

FROM절에도 서브쿼리를 쓸수있다 FROM절의 서브쿼리는 테이블을 리턴한다. 서브쿼리로 나온 테이블을 derived table이라 한다. derived table에는 반드시 alias를 붙여야 한다.

서브쿼리의 종류

리턴결과를 기준

단일값을 리턴하는 서브쿼리

스칼라 서브쿼리라고도 한다. SELECT절에서 하나의 컬럼처럼, WHERE절에서 =,>등의 조건 표현식과 비교하는 값으로 쓸 수 있다.

하나의 컬럼에 여러 로우들이 있는 형태의 결과를 리턴하는 서브쿼리

IN, ANY, ALL등의 키워드와 함께 쓸 수 있다.

하나의 테이블 형태의 결과를 리턴하는 서브쿼리

서브쿼리로 일시적으로 탄생한 테이블을 derived table이라 한다. alias를 반드시 붙여야 한다.

서브쿼리를 리턴결과가 아닌 다른 측면에서 분류

비상관 서브쿼리

outer query와 별개로 독립적으로 실행가능한 서브쿼리를 비상관 서브쿼리라 한다.

상관 서브쿼리

outer query와 상관 관계가 있는 서브쿼리를 말한다.

SELECT * 
FROM item
WHERE EXISTS(
                SELECT *
                FROM review
                WHERE review.item_id = item.id
            );

이런 식으로 item테이블은 outer query에 작성되었기 때문에 서브쿼리는 단독으로 실행되지 못한다.
EXISTS는 만약에 존재하면 TRUE가 된다는 뜻이다.

서브쿼리 alias

SQL의 실행 순서상 SELECT절에서 붙인 alias를 같은 SELECT절 안에서 재사용하지 못하게 되는데 서브쿼리를 사용하면 이 문제를 해결할 수 있다.
FROM절에 서브쿼리를 사용해서 서브쿼리 안에서 alias를 사용하면 된다.

SELECT
    email, BMI
FROM
    (
        SELECT
            *,
            weight/((height/100) * (height/100)) AS BMI FROM member
    ) AS subquery_for_BMI

서브쿼리 중첩

서브쿼리는 중첩이 가능하다. 하지만 중첩을 하다보면 SQL문의 길이가 길어지고 헷갈리기 쉬워져 해석하기 힘들다는 문제가 있다. 이 문제를 해결하기 위해 뷰를 사용한다.

VIEW

뷰는 조인등의 작업을 해서 만든 결과 테이블이 가상으로 저장된 형태를 말한다.

CREATE VIEW 뷰 이름 AS SELECT문;

CREATE VIEW v_emp AS
SELECT
    id,
    name,
    age,
    department,
    phone_num,
    hire_date
FROM employee;

SELECT * FROM v_emp;

이렇게 뷰를 생성하고 그 뷰를 사용하면 된다.

뷰와 그냥 테이블의 차이는 뷰는 테이블과 달리 데이터가 물리적으로 컴퓨터에 저장되어 있지는 않다. 뷰는 뷰를 사용할 때 DBMS가 그 뷰를 생성하는 SQL문을 재실행 하는 방식으로 가상의 테이블을 만들어준다.
뷰의 장점들은 다음이 있다.

1. 뷰는 사용자에게 높은 편의성을 제공한다.
   SQL문을 필요할 때 마다 작성할 필요없이 한번 저장해둔 SQL문을 재활용할 수 있다.
2. 다양한 구조의 데이터 분석 기반을 구축해둘 수 있다.
   같은 테이블이라도 목적에 따라 필요로 하는 데이터의 종류나 그 구조가 다 다르기 때문에 뷰를 사용해 각각 적합한 구조로 데이터들을 준비해둘 수 있다.
3. 뷰는 데이터 보안을 제공한다.
   테이블에 직접적인 접근을 하지 못하도록 막고 뷰에만 접근할 수 있도록 할 수 있다.

DB의 현황 파악하기

1. 존재하는 DB들 파악

   SHOW DATABASES;
   

   일단 현재 DBMS상에 존재하는 DB를 파악한다.

2. 한 DB안의 테이블(뷰도 포함)들 파악

   SHOW FULL TABLES IN DB이름;
   

3. 한 테이블의 컬럼 구조 파악

   DESCRIBE 테이블 이름;
   

4. Foreign Key 파악

   SELECT
    i.TABLE_SCHEMA, i.TABLE_NAME, i.CONSTRAINT_TYPE, i.CONSTRAINT_NAME,
    k.REFERENCED_TABLE_NAME, k.REFERENCED_COLUMN_NAME
   FROM information_schema.TABLE_CONSTRAINTS i
   LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
   WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY';
   

   위 SQL문은 MySQL이 직접 관리하는 기본 DB에서 Foreign Key관련 정보를 꺼내오는 SQL문이다. DBMS마다 차이가 있다.
   두 테이블의 각 컬럼 간에 Foreign Key관계가 성립해도 성능을 고려해 그것을 의도적으로 Foreign Key로 설정하지 않는 경우도 있다.
   따라서 Foreign Key를 정확하게 파악하려면 데이터의 관계 및 흐름을 스스로 파악해내야 한다.