# 서브쿼리 이해해보기

서브쿼리에 대해 알아보자

 

 

우선 나의 쿼리문은 되게 간단하고 쿼리 라인수가 20자가 넘지 않았다.. ㅋㅋ 물론 길이는 중요한 건 아니지만 단순한 쿼리문이었다.

경력이 있거나 유명한 사람들의 깃허브를 100개 이상 돌며 오픈소스에 있는 DAO, DEM , DQM 등등 쿼리문이 있는 파일을 탐색하였다.

 

 

우선 내가 직접 만들어서 쓰는 쿼리와 차이점을 본다면

첫 번째. 쿼리 포맷팅이 이쁘게 정리되어 있다.

두 번째. 서브쿼리를 잘 쓴다. SELECT의 서브쿼리부터 UPDATE의 서브쿼리까지 다양한 서브쿼리가 있었다. 서브쿼리가 있다는 건 알았지만 이렇게 자세하게 쓸 줄은 몰랐다.

세 번째. 공통 코드 및 분류코드를 문자가 아닌 숫자로 저장하였다.

 

 

우선 내가 파악한 차이점은 이것이다.

 

첫 번째 차이점의 이유를 분석하고 보완방법은 쿼리는 여러 명이 사용할 수 있기에 가독성이 중요하고 유지보수도 용이해야 한다. 따라서 SQL 포맷터 도구를 따로 사용해야 할 듯싶다.

두 번째는 기업에서는 데이터의 요구 사항이 복잡하기 때문에 서브쿼리를 많이 활용하는 것 같다. 서브쿼리는 복잡한 데이터 집계, 필터링, 변환 등을 수행하는데 용이하다. 따라서 요구사항이 복잡하면 서브쿼리를 잘 사용해 보는 것이 익숙해지는데 좋을 것 같다.

세 번째는 저장공간이 절약되며 인덱싱 및 조회 시 속도가 빨라진다는 장점이 있다. 따라서 공통코드를 중앙에서 관리하고 숫자로 적용하여 문서화해 놓는 것이 좋을 것이다.

 

 

직접 쿼리를 짜보자

 

-- 직원 정보를 저장하는 테이블
CREATE TABLE employees (
                           employee_id INT PRIMARY KEY,  -- 직원 ID (기본 키)
                           employee_name VARCHAR(100),   -- 직원 이름
                           department_id INT,            -- 부서 ID (부서 테이블의 외래 키)
                           salary DECIMAL(10, 2)         -- 직원 급여 DECIMAL 은 소수점이 들어간 실수를 저장. 즉 정확한 수치를 저장.
);

-- 직원 데이터를 삽입
INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
                                                                              (1, 'John Doe', 1, 60000),     -- John Doe, Sales 부서, 급여 60000
                                                                              (2, 'Jane Smith', 2, 75000),   -- Jane Smith, Engineering 부서, 급여 75000
                                                                              (3, 'Alice Johnson', 1, 55000),-- Alice Johnson, Sales 부서, 급여 55000
                                                                              (4, 'Chris Lee', 3, 50000),    -- Chris Lee, HR 부서, 급여 50000
                                                                              (5, 'Mike Brown', 2, 72000);   -- Mike Brown, Engineering 부서, 급여 72000



-- 부서 정보를 저장하는 테이블
CREATE TABLE departments (
                             department_id INT PRIMARY KEY,    -- 부서 ID (기본 키)
                             department_name VARCHAR(100),     -- 부서 이름
                             location_id INT                   -- 위치 ID
);

-- 부서 데이터를 삽입
INSERT INTO departments (department_id, department_name, location_id) VALUES
                                                                          (1, 'Sales', 101),          -- Sales 부서, 위치 ID 101
                                                                          (2, 'Engineering', 102),    -- Engineering 부서, 위치 ID 102
                                                                          (3, 'HR', 103);             -- HR 부서, 위치 ID 103




-- 주문 정보를 저장하는 테이블
CREATE TABLE orders (
                        order_id INT PRIMARY KEY,     -- 주문 ID (기본 키)
                        employee_id INT,              -- 직원 ID (직원 테이블의 외래 키)
                        order_amount DECIMAL(10, 2)   -- 주문 금액
);

-- 주문 데이터를 삽입
INSERT INTO orders (order_id, employee_id, order_amount) VALUES
                                                             (1, 1, 1500),   -- 주문 ID 1, John Doe가 처리한 주문, 금액 1500
                                                             (2, 1, 2000),   -- 주문 ID 2, John Doe가 처리한 주문, 금액 2000
                                                             (3, 2, 3000),   -- 주문 ID 3, Jane Smith가 처리한 주문, 금액 3000
                                                             (4, 3, 2500),   -- 주문 ID 4, Alice Johnson이 처리한 주문, 금액 2500
                                                             (5, 4, 1000);   -- 주문 ID 5, Chris Lee가 처리한 주문, 금액 1000




# Employees 테이블은 직원의 기본 정보를 저장합니다. 여기에는 직원 ID, 이름, 부서 ID 및 급여 정보가 포함됩니다.
# Departments 테이블은 부서의 기본 정보를 저장합니다. 여기에는 부서 ID, 이름 및 위치 ID가 포함됩니다.
# Orders 테이블은 직원이 처리한 주문 정보를 저장합니다. 여기에는 주문 ID, 처리한 직원의 ID 및 주문 금액이 포함됩니다.

 

 

 

 

SELECT 서브쿼리

/*------------------------------------------------------------------------------------------------------------------*/
-- SELECT
/*------------------------------------------------------------------------------------------------------------------*/
# 아래 쿼리는 메인 쿼리와 SELECT의 서브쿼리가 있다.
# 메인쿼리는 employee테이블에있는 직원 정보를 조회하고
# 서브쿼리는 각직원의 id를 대조하여 처리한 주문량의 총합을 계산하는 쿼리이다.
/*------------------------------------------------------------------------------------------------------------------*/
SELECT E.employee_id, E.employee_name ,
       (SELECT SUM(O.order_amount)
        FROM orders O
        WHERE  O.employee_id = E.employee_id) AS total_sales

FROM employees E;


/*------------------------------------------------------------------------------------------------------------------*/
-- SELECT
-- 각 부서의 평균 급여 조회
 # 아래쿼리는 메인쿼리 D에서 부서 이름을 가져오고
 # 서브쿼리에서 각 부서의 평균 급여를 계산해서 가져오는 쿼리이다.

SELECT D.department_name ,
       (SELECT AVG(E.salary)
        FROM employees E
        WHERE E.department_id = D.department_id)AS avg_salary
FROM departments D;
/*------------------------------------------------------------------------------------------------------------------*/



/*------------------------------------------------------------------------------------------------------------------*/
-- SELECT
-- 각 직원의 부서 이름 조회
# 아래쿼리는 메인쿼리 E 에서 직원 이름을 가져오고
# 서브쿼리에서 각 부서를 이름ID에 맞게 가져오는 쿼리이다.
SELECT E.employee_name ,
       (SELECT D.department_name
        FROM departments D
        WHERE  D.department_id = E.department_id
            )AS department_name
FROM employees E;
/*------------------------------------------------------------------------------------------------------------------*/

 

 

FROM 서브쿼리

/*------------------------------------------------------------------------------------------------------------------*/
-- FROM
-- FROM 절의 사용하는 서브쿼리는 임시 테이블을 생성하여 메인 쿼리에서 이를 사용함. 이는 복잡한 데이터 집합을 간단하게 만들거나, 중간 결과를 재사용하는데 유용하다


-- 부서별 평균 급여 조회
#
#
# employees 테이블의 별칭 E를 사용하여 각 부서별 평균 급여(AVG(E.salary))를 계산하고, 이를 dept_avg라는 임시 테이블로 생성.
# 메인 쿼리: dept_avg 임시 테이블을 departments 테이블의 별칭 D와 조인하여 부서 이름(D.department_name)과 부서별 평균 급여(dept_avg.avg_salary)를 조회
SELECT
    D.department_name,
    dept_avg.avg_salary
FROM
    (SELECT E.department_id, AVG(E.salary) AS avg_salary
     FROM employees E
     GROUP BY E.department_id) AS dept_avg
        JOIN departments D ON dept_avg.department_id = D.department_id;
/*------------------------------------------------------------------------------------------------------------------*/




/*------------------------------------------------------------------------------------------------------------------*/
-- FROM
-- 특정 부서의 직원 수 조회
## FROM 절안에 있는 서브쿼리를 먼저 작성하여 부서번호 , 카운트 를하여 임시테이블을 만든후
## JOIN으로  이름과 부서별 직원수를 조회한다/

SELECT
    D.department_name,
    COUNT_E.EMPIOYEE_COUNT

FROM  (SELECT E.department_id, COUNT(*)AS EMPIOYEE_COUNT
       FROM employees E
       GROUP BY E.department_id
      ) AS COUNT_E
          JOIN  departments D ON COUNT_E.department_id = D.department_id
/*------------------------------------------------------------------------------------------------------------------*/

 

 

WHERE 서브쿼리

/*------------------------------------------------------------------------------------------------------------------*/
-- WHERE
-- WHERE 절의 서브쿼리는 특정 조건을 만족하는 행을 필터링하거나 값을 비교할 때 사용


-- 특정 부서에 속한 직원 찾기

# 메인 쿼리: employees 테이블에서 직원 정보를 조회
# 서브쿼리: departments 테이블에서 부서 이름이 'Sales'인 department_id를 조회
SELECT
    E.employee_id,
    E.employee_name,
    E.salary
FROM
    employees E

WHERE
    E.department_id = (SELECT D.department_id
                       FROM departments D
                       WHERE D.department_name = 'Sales');
/*------------------------------------------------------------------------------------------------------------------*/


/*------------------------------------------------------------------------------------------------------------------*/
-- WHERE
-- 평균 급여보다 높은 급여를 받는 직원 찾기

## 메인쿼리 E에서 직원정보
## where 서브쿼리 E2에 salary 평균 구하고 > 큰것 필터링

SELECT E.employee_id , E.employee_name ,E.salary
FROM employees E
WHERE
    E.salary > (SELECT E2.salary
                FROM employees E2
                HAVING AVG(E2.salary));

/*------------------------------------------------------------------------------------------------------------------*/

/*------------------------------------------------------------------------------------------------------------------*/
-- WHERE
-- 특정 부서에 속한 직원 중 최고 급여를 받는 직원 찾기


SELECT E.employee_id , E.employee_name ,E.salary
FROM employees E
WHERE E.salary = (SELECT MAX(E2.salary)
                  FROM employees E2
                  WHERE E2.department_id= E.department_id)

/*------------------------------------------------------------------------------------------------------------------*/





/*------------------------------------------------------------------------------------------------------------------*/
-- WHERE
-- 주문 금액의 평균보다 큰 주문을 처리한 직원 찾기

# 메인 쿼리: employees 테이블에서 직원 정보를 조회
# 서브쿼리: 첫 번째 서브쿼리는 orders 테이블에서 전체 주문의 평균 금액(AVG(O2.order_amount))을 계산
# 두 번째 서브쿼리: orders 테이블에서 각 주문의 금액(O.order_amount)이 평균 금액보다 큰 주문을 처리한 직원(O.employee_id)을 조회
# 조건: 메인 쿼리는 첫 번째 서브쿼리에서 조회된 직원 ID를 가지고 있는 직원을 필터링

SELECT

    E.employee_id,
    E.employee_name
FROM
    employees E
WHERE
    E.employee_id IN (SELECT O.employee_id
                      FROM orders O
                      WHERE O.order_amount > (SELECT AVG(O2.order_amount)
                                              FROM orders O2));

/*------------------------------------------------------------------------------------------------------------------*/

 

 

HAVING 서브쿼리

/*------------------------------------------------------------------------------------------------------------------*/
-- HAVING
-- HAVING 절의 서브쿼리는 그룹화된 결과에 대해 조건을 적용할 때 사용


-- 부서별 평균 급여가 전체 평균 급여보다 높은 부서 찾기

# 메인 쿼리:employees 테이블과 departments 테이블을 조인하여 부서별로 평균 급여를 계산
# 서브쿼리:  employees 테이블에서 전체 직원의 평균 급여(AVG(E2.salary))를 계산합니다
#  조건: 메인 쿼리에서 계산된 부서별 평균 급여(AVG(E.salary))가 서브쿼리에서 계산된 전체 평균 급여보다 높은 부서들을 필터링
SELECT
    D.department_name,
    AVG(E.salary) AS avg_salary
FROM
    employees E
        JOIN
    departments D ON E.department_id = D.department_id
GROUP BY
    D.department_name
HAVING
    AVG(E.salary) > (SELECT AVG(E2.salary)
                     FROM employees E2);

/*------------------------------------------------------------------------------------------------------------------*/

/*------------------------------------------------------------------------------------------------------------------*/
-- HAVING
-- 부서별 직원 수가 특정 부서의 직원 수보다 많은 부서 찾기
# 메인 쿼리: employees 테이블과 departments 테이블을 조인하여 부서별로 직원 수를 계산
# 서브쿼리: employees 테이블에서 department_id가 1인 부서의 직원 수(COUNT(E2.employee_id))를 계산
# 조건: 메인 쿼리에서 계산된 부서별 직원 수(COUNT(E.employee_id))가 서브쿼리에서 계산된 특정 부서(부서 ID 1)의 직원 수보다 많은 부서들을 필터링
SELECT
    D.department_name,
    COUNT(E.employee_id) AS employee_count
FROM
    employees E
        JOIN
    departments D ON E.department_id = D.department_id
GROUP BY
    D.department_name
HAVING
    COUNT(E.employee_id) > (SELECT COUNT(E2.employee_id)
                            FROM employees E2
                            WHERE E2.department_id = 1);

/*------------------------------------------------------------------------------------------------------------------*/