이전에 만들었던 프로젝트들도 관계형 데이터베이스를 사용했었지만 데이터베이스는 많은 부분이 독학으로 배운게 많아 전에는 데이터 정형화와 ERD 등 이해하기 어려운 부분이 많았는데 이것들을 배우면서 알게 된 서브쿼리에 대해 이해가 안가 정리하기 시작했다
📌 서브 쿼리
서브쿼리는 SQL 문장 안에 포함된 또 다른 SQL 문장이다 주로 WHERE 절, HAVING 절, FROM 절, SELECT 절의 일부로 사용된다 서브쿼리는 주 쿼리의 결과에 영향을 주거나 조건을 만족하는 데이터를 선택하기 위해 사용된다
-- WHERE 절에서 서브쿼리 사용 예제: 100보다 큰 주문금액을 갖는 고객 선택
SELECT customer_name, order_id, order_amount
FROM orders
WHERE order_amount > (SELECT AVG(order_amount) FROM orders);
-- HAVING 절에서 서브쿼리 사용 예제: 주문 수가 3개 이상인 고객 선택
SELECT customer_id, COUNT(*) as order_count
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= (SELECT AVG(order_count) FROM (SELECT COUNT(*) as order_count FROM orders GROUP BY customer_id));
-- FROM 절에서 서브쿼리 사용 예제: 주문 테이블과 상품 테이블을 결합한 후 서브쿼리로 결과 집합 생성
SELECT o.order_id, p.product_name
FROM (SELECT order_id, product_id FROM order_details WHERE quantity >= 3) AS od
JOIN orders o ON od.order_id = o.order_id
JOIN products p ON od.product_id = p.product_id;
-- SELECT 절에서 서브쿼리 사용 예제: 각 부서별 직원 수를 구한 후 그 값을 SELECT 문장에서 사용
SELECT department_id,
(SELECT COUNT(*) FROM employees WHERE department_id = d.department_id) AS employee_count
FROM departments d;
WHERE 절에서 값이 한개일 경우는 = 로 조회가 가능하지만 값이 여러개가 반환될 경우 NOT IN으로 해줘야 조회가 가능하다
메뉴를 하나도 등록하지 않은 가계들을 삭제하는 SQL 문이다 먼저 메뉴를 사용하는 가계들을 조회했다
SELECT STORE_ID FROM FOOD_MENU FM GROUP BY STORE_ID
결과
0005 번은 사용을 안하고 있다는걸 확인 이걸 서브쿼리문으로 DELETE 문을 만들었다 근데 값이 여러개다 보니깐 실행되지 않는 문제가 발생
-- 잘못된 쿼리
DELETE FROM STORE WHERE STORE_ID != (SELECT STORE_ID FROM FOOD_MENU FM GROUP BY STORE_ID);
아래 문으로 바꾸니깐 정상 실행 됐다
DELETE FROM STORE WHERE STORE_ID NOT IN (SELECT STORE_ID FROM FOOD_MENU FM GROUP BY STORE_ID);
📌 스칼라 쿼리
단일 값을 반환하는 쿼리를 말한다 다시 말해, 스칼라 쿼리는 하나의 행과 하나의 열을 반환하는 쿼리이다
아래 조인으로 만든 예시가 있는데 이걸 스칼라 쿼리로 바꿔보겠다
SELECT CM.CAR_MAKER_NAME, C.CAR_NAME, COUNT(*) ,TRUNC(AVG(CS.CAR_SELL_PRICE), 0) AS AVG
FROM CAR c INNER JOIN CAR_MAKER cm
ON C.CAR_MAKER_CODE = CM.CAR_MAKER_CODE
INNER JOIN CAR_SELL cs
ON C.CAR_ID = CS.CAR_ID
GROUP BY C.CAR_NAME, CM.CAR_MAKER_NAME
ORDER BY CM.CAR_MAKER_NAME DESC;
JOIN 방식
SELECT (SELECT CM.CAR_MAKER_NAME FROM CAR_MAKER CM
WHERE C.CAR_MAKER_CODE = CM.CAR_MAKER_CODE) AS CAR_MAKER_NAME,
CAR_COUNT, AVG_PRICE
FROM
(SELECT
C.CAR_MAKER_CODE, COUNT(*) AS CAR_COUNT,
AVG(C.CAR_PRICE) AS AVG_PRICE
FROM CAR C
GROUP BY C.CAR_MAKER_CODE) C;
서브쿼리(스칼라 쿼리)
JOIM으로 해결할 수 있는걸 스칼라로도 해결이 가능하다 프로그래밍으로는 좋지 않은 코드지만 이런식으로도 해결할 수 있다는 걸 알 수 있다
📌 인라인 뷰
FROM 절에서 사용되는 인라인 뷰는 쿼리 내에서 서브쿼리를 사용하여 새로운 가상 테이블을 만들는 것을 의미한다
이 가상 테이블은 주로 해당 쿼리에서만 사용되며, 쿼리의 다른 부분에서는 직접적으로 참조되지 않는다
인라인 뷰는 일반적으로 복잡한 쿼리를 간단하게 만들거나, 쿼리의 가독성을 향상시키기 위해 사용된다
간단한 예제를 통해 설명해보자면 EMPLOYEES 테이블에서 연봉이 평균 연봉보다 높은 직원들의 정보를 조회하는 경우를 생각해보자
SELECT employee_id, employee_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
위의 쿼리에서는 EMPLYEES 테이블의 평균 연봉보다 높은 연봉을 받는 직원들의 정보를 조회하고 있다 이를 인라인 뷰를 사용하여 다음과 같이 나타낼 수 있다
SELECT employee_id, employee_name, salary
FROM (
SELECT employee_id, employee_name, salary
FROM employees
) AS emp
WHERE salary > (SELECT AVG(salary) FROM employees);
이렇게 하면 쿼리의 가독성이 향상되고, 필요한 데이터를 보다 간결하게 선택할 수 있다 위의 예제에서는 EMPLYEES 테이블에서 직원의 정보를 선택하여 인라인 뷰를 만들고, 그 결과를 다시 직원의 평균 연봉과 필터링하고 있다
👨🏻💻 활용해보기
게시판 뷰
다양한 커뮤니티 사이트를 보면서 게시판에 필요한 데이터를 모았다
게시판 번호, 게시판 제목, 사용자 닉네임, 조회수, 작성날자, 개추, 비추, 댓글 수를 조회하도록 뷰를 만들었다
JOIN을 서브쿼리로 만들어서 BOARD_LIKE(HATE) 테이블에서 FK로 받은 값들을 기준으로 데이터를 가져왔다
마찬가지로 COMMENT도 BOARD_COMMENT를 COUNT하고 해당하는 결과를 가져왔다
ROW_NUMBER() OVER(ORDER BY B.BOARD_NO DESC) AS RW 의 경우는 윈도우 함수로 오라클에서만 쓸 수 있다
게시판을 열었을 때 한번에 보여지는 것이 아니라 20개씩 혹은 30개씩 이런식으로 일부 데이터만 가져와서 조회해주는데 마찬가지로 이걸 나눌려고 만들었다 페이징(PAGING)
CREATE OR REPLACE VIEW BOARD_VIEW
AS
SELECT
ROW_NUMBER() OVER(ORDER BY B.BOARD_NO DESC) AS RW,
B.BOARD_NO, B.BOARD_TITLE,
BM.BOARD_MEMBER_NICKNAME , B.BOARD_VIEW , B.BOARD_WRITE_DATE,
NVL(BCL.BOARD_LIKE_COUNT,0) AS BOARD_LIKE_COUNT,
NVL(BCH.BOARD_HATE_COUNT,0) AS BOARD_HATE_COUNT,
NVL(BCC.COMMENT_COUNT, 0) AS BOARD_COMMENT_COUNT
FROM BOARD B JOIN BOARD_MEMBER BM
ON B.BOARD_MEMBER_ID = BM.BOARD_MEMBER_ID
LEFT JOIN
(SELECT BCL.BOARD_NO, COUNT(*) AS BOARD_LIKE_COUNT
FROM BOARD_LIKE BCL GROUP BY BCL.BOARD_NO) BCL
ON BCL.BOARD_NO = B.BOARD_NO
JOIN
(SELECT BCH.BOARD_NO, COUNT(*) AS BOARD_HATE_COUNT
FROM BOARD_HATE BCH GROUP BY BCH.BOARD_NO) BCH
ON BCH.BOARD_NO = B.BOARD_NO
LEFT JOIN
(SELECT BC.BOARD_NO, COUNT(*) AS COMMENT_COUNT
FROM BOARD_COMMENT bc
GROUP BY BOARD_NO) BCC
ON BCC.BOARD_NO = B.BOARD_NO ;
