DataBase/Oracle

oracle outer join

띵큐 2023. 10. 12. 15:11

---sample : left outer join

SELECT e.FirstName AS EmployeeFirstName,
       e.LastName AS EmployeeLastName,
       d.DepartmentName
FROM Employees e, Departments d
WHERE e.DepartmentID = d.DepartmentID (+);

 

이 쿼리는 "Employees" 테이블의 모든 직원과 그들이 속한 부서의 정보를 가져옵니다. 왼쪽 조인을 사용하므로 "Employees" 테이블의 모든 행이 결과 집합에 포함되며, "Departments" 테이블과 일치하는 부서 정보가 있는 경우 해당 정보가 표시됩니다.

 

SELECT e.FirstName AS EmployeeFirstName,
       e.LastName AS EmployeeLastName,
       d.DepartmentName
FROM Employees e, Departments d
WHERE e.DepartmentID = d.DepartmentID (+) AND d.DepartmentName(+) = 'HR';

 

이 쿼리에서는 OUTER JOIN을 사용하며, (+)를 통해 "DepartmentName"이 'HR'인 부서에 속한 직원을 가져오면서 "Departments" 테이블의 다른 부서와도 일치하지 않는 경우에도 결과에 포함됩니다. 이러한 경우에는 "DepartmentName"이 NULL로 표시됩니다.

결과적으로, (+)를 사용하는 경우에는 'HR' 부서에 속한 직원과 NULL "DepartmentName" 값을 가진 직원이 모두 표시되지만, (+)를 사용하지 않는 경우에는 'HR' 부서에 속한 직원만 표시됩니다.

 

# outer join 제약

조인 조건 외에 일반 조건에 (+) 사용해야 데이터 유실 없음

조인 조건(Where절) 하나 이사인 경우, 모든 조인조건에 (+) 사용

조인되는 두개의 테이블에 동시에 (+)는 불가하다.

업무적으로 NULL 이 필요한 경우만 사용

참조테이블의 조건에 IN/OR 연산자 사용불가(11g 이후 사용가능)

->Decode 처리, 참조테이블을 인라인 뷰로 묶어준 뒤, 인라인 뷰에서 IN/OR 조건 사용

(+) 붙은 컬럼과 서브쿼리 같이 사용 못함

 

--- 조인 조건(Where절) 하나 이사인 경우, 모든 조인조건에 (+) 사용

Where E.employee_id = J.employee_id (+)

and E.department_id = J.department_id (+)

 

--- 조인 조건 외에 일반 조건에 (+) 사용해야 데이터 유실 없음

Outer Join을 담당하는 테이블에 대한 모든 조건에(++ 붙여야 원하는 결과 얻음=>즉 상수와 비교하더라도 (+) 기호를 붙인다.

SELECT e.FirstName AS EmployeeFirstName,
       e.LastName AS EmployeeLastName,
       d.DepartmentName
FROM Employees e, Departments d
WHERE e.DepartmentID = d.DepartmentID (+) AND d.DepartmentName(+) = 'HR';

 

--- 조인되는 두개의 테이블에 동시에 (+)는 불가하다.

Where E.employee_id(+) = J.employee_id (+)

 

※ 인라인 뷰

인라인 뷰는 주로 하위 쿼리(subquery)를 사용하여 메인 쿼리의 일부로서 다른 테이블과 조인하거나 데이터를 필터링하는데 활용됩니다. 이것은 데이터를 논리적으로 구조화하고 쿼리를 더 효율적으로 관리하는 데 도움이 됩니다.

보통 From 절에서 사용하는 테이블 형태 입니다.

 

SELECT e.FirstName, e.LastName, e.Department
FROM (
    SELECT EmployeeID, FirstName, LastName, Department
    FROM Employees
    WHERE Salary > 50000
) e;

 

- 인라인 뷰의 주요 특징

  1. 인라인 위치: 인라인 뷰는 메인 쿼리 안에서 정의되며, 메인 쿼리의 일부로 간주됩니다. 이것은 인라인 뷰를 메인 쿼리 내에서 서브쿼리로 사용할 수 있음을 의미합니다.
  2. 가상 테이블: 인라인 뷰는 가상 테이블로 간주되며, 별도의 테이블로 저장되지 않습니다. 즉, 인라인 뷰는 쿼리 실행 시에 생성되며 필요에 따라 사용됩니다.
  3. 조인 및 필터링: 인라인 뷰를 사용하여 메인 쿼리와 다른 테이블을 조인하거나 데이터를 필터링할 수 있습니다.

※ Sub 쿼리

보통 조건 절(Where)등에서 사용하는 select 쿼리

SELECT FirstName, LastName
FROM Employees
WHERE DepartmentID IN (SELECT DepartmentID FROM Departments WHERE DepartmentName = 'HR');