Sub Query & 인라인 뷰

===Sub Query

메인 쿼리의 기준테이블과 참조테이블의 집합관계에 영향을 주지 않으면서, 다른 테이블을 Where 조건으로 이용하기 위하여 서브쿼리를 이용함

브쿼리는 다른 쿼리의 결과를 메인 쿼리에서 사용하고 이를 통해 데이터를 보다 정확하게 검색하거나 처리하는 데 도움이 됩니다.

 

비교 연산자와 함께 사용: 서브쿼리는 주로 SELECT, INSERT, UPDATE, DELETE 등의 SQL 문장에서 WHERE 절, FROM 절, HAVING 절 또는 다른 SQL 절과 함께 사용됩니다.

 

이를 통해 데이터베이스에서 조금 더 복잡한 연산을 수행하고 원하는 데이터를 추출하는 데 도움을 줍니다. 서브쿼리는 SQL의 강력한 도구 중 하나로, 데이터 검색 및 조작에 유용하게 활용됩니다.

 

SELECT customer_name
FROM Customers
WHERE order_amount > (SELECT AVG(product_price) FROM Orders);

 

SELECT employee_name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee WHERE department_id = 101);

 

SELECT c.customer_name, o.order_date
FROM Customers c
JOIN Orders o ON c.customer_id = o.customer_id
WHERE o.product_id = (SELECT product_id FROM Products WHERE product_name = 'Apple iPhone');

 

SELECT c.customer_name, o.order_date
FROM Customers c, Orders o
WHERE c.customer_id = o.customer_id
  AND o.product_id = (SELECT product_id FROM Products WHERE product_name = 'Apple iPhone');

 

※ Rownum

ROWNUM"은 Oracle 데이터베이스에서 사용되는 의사 컬럼(Pseudo Column) 중 하나로, 쿼리 결과 집합에서 특정 행의 순서 번호를 나타내는 숫자를 할당하는 데 사용됩니다. 이 순서 번호는 일반적으로 쿼리 결과 집합에 표시된 행의 순서를 나타내며, 특정 순서 번호를 가진 행을 선택하거나 필터링하는 데 사용됩니다.

 

"사원(Employees)" 테이블에서 ROWNUM을 사용하여 급여가 높은 순서로 상위 5명의 사원을 선택합니다.

SELECT * 
FROM (
    SELECT employee_id, first_name, last_name, salary, ROWNUM AS ranking
    FROM Employees
    ORDER BY salary DESC
)
WHERE ranking <= 5;

 

• 서브쿼리는 "Employees" 테이블에서 사원의 정보를 검색하고 급여를 기준으로 내림차순으로 정렬합니다.
• 서브쿼리 결과에 ROWNUM을 할당하여 각 행의 순서를 나타냅니다.
• 메인 쿼리에서는 서브쿼리 결과에서 ROWNUM이 5 이하인 행만 선택하여 급여가 높은 순서로 상위 5명의 사원 정보를 반환합니다.

=== 인라인 뷰

From 절에 사용되는 서브쿼리 == 가상테이블

인라인 뷰(Inline View)는 SQL 쿼리 내에서 사용되는 가상의 테이블 또는 서브쿼리의 결과 집합을 말합니다. 이러한 가상 테이블 또는 서브쿼리의 결과는 메인 쿼리에서 직접 사용되며, 메인 쿼리의 일부로 처리됩니다. 인라인 뷰는 주로 복잡한 쿼리를 보다 간결하게 작성하고 가독성을 높이는 데 도움을 줍니다.

 

1. 가상 테이블: 인라인 뷰의 결과는 물리적으로 존재하지 않는 가상 테이블처럼 작동합니다. 따라서 데이터베이스에 따로 저장되지 않으며 쿼리 실행 시에만 생성됩니다.
2. 메인 쿼리와 조합: 인라인 뷰의 결과는 메인 쿼리와 조합하여 사용됩니다. 이를 통해 메인 쿼리에서 인라인 뷰의 결과를 테이블처럼 다룰 수 있습니다.
3. 가독성과 유지 보수성: 복잡한 쿼리를 간결하게 작성하고, 필요한 데이터만을 포함하는 가상 테이블을 만들어 가독성과 유지 보수성을 높이는 데 도움이 됩니다.
4. 중첩된 서브쿼리 대체: 인라인 뷰는 중첩된 서브쿼리를 대체하는 데 사용될 수 있으며, 중첩된 서브쿼리보다 가독성이 높은 코드를 작성하는 데 도움이 됩니다.

SELECT salary_count
FROM (
    SELECT COUNT(*) AS salary_count
    FROM Employees
    WHERE salary >= 50000
);

 

※ 인라인뷰의 남용

원하는 데이터가 추출되지 않으면 우선 인라인뷰부터 만들고 보는 SQL 습관

SQL은 절차적인 수행을 하지 않는다. 여러형태의 데이터를 한번에 추출하는 언어이므로 집합적 사고 방식이 중요

 

※ 인라인뷰 사용지침

데이터를 감소시켜 처리범위를 최소화 할 수 있는 경우 사용

과다한 인라인 뷰의 사용은 SQL해석을 어렵게 하며, SQL이 길어지면서 성능 저하됨

인라인뷰가 Main SQL과 합쳐져도 옵티마이저가 SQLdㄹ 변경함(Query Transformation, View Merge)

반복적인 인라인 뷰의 사용은 테이블 엑세스 횟수를 증가시킴

 

※ 인라인뷰 와 WITH 절 사용

WITH 절은 공통 테이블 표현식(Common Table Expression, CTE)를 생성할 때 사용되며, CTE는 SQL 쿼리 내에서 재사용 가능한 서브쿼리나 인라인 뷰를 정의하는 데 사용됩니다. CTE를 사용하면 쿼리의 가독성을 향상시키고 복잡한 쿼리를 분해하여 관리하기 쉽게 만들 수 있습니다.

 

WITH HighSalaryEmployees AS (
    SELECT employee_name, salary
    FROM Employee
    WHERE salary >= 50000
)
SELECT * FROM HighSalaryEmployees;

 

WITH 절을 사용하여 HighSalaryEmployees CTE를 만들고, 이 CTE를 이후의 메인 쿼리에서 사용합니다. 이러한 접근 방식을 사용하면 쿼리가 가독성이 높아지고 필요한 로직을 분리하여 관리하기 쉬워집니다.

 

----With절과 2개 이상의 인라인 뷰 사용

WITH HighSalaryEmployees AS (
    SELECT employee_name, salary
    FROM Employee
    WHERE salary >= 50000
),
FemaleEmployees AS (
    SELECT employee_name, gender
    FROM Employee
    WHERE gender = 'Female'
)
SELECT H.employee_name AS high_salary_employee, F.employee_name AS female_employee
FROM HighSalaryEmployees H
JOIN FemaleEmployees F ON H.employee_name = F.employee_name;

 

===스칼라 Sub Query

한 행에서 정확시 하나의 열값 반환하는 서브 쿼리

스칼라 서브쿼리에서 결과가 없으면 NULL

 

SELECT order_date, (
    SELECT SUM(order_amount)
    FROM OrderDetails
    WHERE order_id = O.order_id
) AS total_amount
FROM Orders O
WHERE order_date = (
    SELECT MAX(order_date)
    FROM Orders
);

1. 메인 쿼리는 "Orders" 테이블에서 주문 날짜와 스칼라 서브쿼리의 결과로 받아온 총 금액(total_amount)을 선택합니다.
2. 스칼라 서브쿼리 1은 "Orders" 테이블에서 가장 최근 주문의 날짜를 선택합니다.
3. 스칼라 서브쿼리 2는 "OrderDetails" 테이블에서 각 주문의 총 금액을 계산하고, 해당 주문과 메인 쿼리의 주문을 매칭시키기 위해 order_id를 사용합니다.

===스칼라 Sub Query의 수행순서

스칼라 서브쿼리는 메인 쿼리의 각 행에 대해 평가되며, 메인 쿼리와 함께 결과 집합이 생성됩니다. 이것은 스칼라 서브쿼리의 결과가 메인 쿼리의 조건을 만족하는지 확인하거나, 메인 쿼리의 선택 목록에 스칼라 서브쿼리의 결과를 포함하는 등 다양한 용도로 사용될 수 있음을 의미합니다.

 

===스칼라 Sub Query의 NVL NULL 처리

SELECT 
    customer_name, 
    NVL(
        (SELECT MAX(order_amount) FROM Orders WHERE customer_id = Customers.customer_id), 
        'Unknown'
    ) AS max_order_amount
FROM Customers;

 

NVL 함수는 Oracle 데이터베이스에서 NULL 값 대체를 위해 사용됩니다. 아래의 예제에서는 NVL 함수를 사용하여 스칼라 서브쿼리의 NULL 값을 처리하는 방법을 보여줍니다.