WHERE 절의 Oracle 스타일 외부 조인 - Amazon Redshift
기본 구문사용 노트예제

WHERE 절의 Oracle 스타일 외부 조인

Oracle 호환성을 위해 Amazon Redshift는 WHERE 절 조인 조건에서 Oracle 외부 조인 연산자(+)를 지원합니다. 이 연산자는 외부 조인 조건을 정의하는 데만 사용하는 연산자이므로, 다른 컨텍스트에서는 사용하지 마십시오. 이 연산자를 달리 사용하면 대부분의 경우에는 자동으로 무시됩니다.

외부 조인은 동등한 내부 조인이 반환하는 모든 행과 한 테이블 또는 두 테이블 모두에서 일치하지 않는 행을 반환합니다. FROM 절에서 왼쪽, 오른쪽 및 전체 외부 조인을 지정할 수 있습니다. WHERE 절에서 왼쪽 및 오른쪽 외부 조인만 지정할 수 있습니다.

외부 조인 테이블 TABLE1 및 TABLE2를 지정하고 TABLE1(왼쪽 외부 조인)에서 일치하지 않는 행을 반환하려면 FROM 절에 TABLE1 LEFT OUTER JOIN TABLE2를 지정하거나 WHERE 절의 TABLE2에서 모든 조인 열에 (+) 연산자를 적용합니다. TABLE2에 일치하는 행이 없는 TABLE1의 모든 행에 대해, 쿼리의 결과는 TABLE2의 열을 포함한 모든 select list 표현식을 위한 null을 포함합니다.

TABLE1에 일치하는 행이 없는 TABLE2의 모든 행에 대해 동일한 동작을 생성하려면 FROM 절에 TABLE1 RIGHT OUTER JOIN TABLE2를 지정하거나 WHERE 절의 TABLE1에서 모든 조인 열에 (+) 연산자를 적용합니다.

기본 구문

[ WHERE {
[ table1.column1 = table2.column1(+) ]
[ table1.column1(+) = table2.column1 ]
}

첫 번째 조건은 다음과 동등합니다.

from table1 left outer join table2
on table1.column1=table2.column1

두 번째 조건은 다음과 동등합니다.

from table1 right outer join table2
on table1.column1=table2.column1
참고

여기에 표시된 구문은 한 쌍의 조인 열에 대해 간단한 동등 조인 케이스를 포함합니다. 하지만 다른 유형의 비교 조건과 여러 쌍의 조인 열 역시 유효합니다.

예를 들어, 다음 WHERE 절은 두 쌍의 열에 대해 외부 조인을 정의합니다. 두 조건에서 모두 (+) 연산자를 같은 테이블에 연결해야 합니다.

where table1.col1 > table2.col1(+)
and table1.col2 = table2.col2(+)

사용 노트

가능하면 WHERE 절에 (+) 연산자 대신 표준 FROM 절 OUTER JOIN 구문을 사용하십시오. (+) 연산자를 포함하는 쿼리는 다음 규칙에 따릅니다.

  • WHERE 절에는 (+) 연산자만 사용할 수 있고, 테이블 또는 뷰의 열에 대한 참조에만 사용할 수 있습니다.

  • 표현식에는 (+) 연산자를 적용할 수 없습니다. 하지만 표현식은 (+) 연산자를 사용하는 열을 포함할 수 있습니다. 예를 들어 다음과 같은 조인 조건은 구문 오류를 반환합니다.

    event.eventid*10(+)=category.catid

    그러나 다음 조인 조건이 유효합니다.

    event.eventid(+)*10=category.catid

  • FROM 절 조인 구문도 포함하는 쿼리 블록에 (+) 연산자를 사용할 수 없습니다.

  • 여러 조인 조건에 걸쳐 두 테이블이 조인되는 경우 이런 조건 전부에 (+) 연산자를 사용하거나 아무런 조건에도 이 연산자를 사용하면 안 됩니다. 혼합 구문 스타일의 조인은 따로 경고 없이 내부 조인으로 실행됩니다.

  • 외부 쿼리의 테이블을 내부 쿼리에서 생성되는 테이블과 조인하는 경우 (+) 연산자는 외부 조인을 생성하지 않습니다.

  • (+) 연산자를 사용하여 테이블을 그 자체에 외부 조인하려면 FROM 절에서 테이블 별칭을 정의하고 이런 별칭을 조인 조건에서 참조해야 합니다.

    select count(*)
from event a, event b
where a.eventid(+)=b.catid;

count
-------
8798
(1 row)

  • (+) 연산자를 포함하는 조인 조건을 OR 조건 또는 IN 조건과 결합할 수 없습니다. 예: 

    select count(*) from sales, listing
where sales.listid(+)=listing.listid or sales.salesid=0;
ERROR:  Outer join operator (+) not allowed in operand of OR or IN.

  • 2개보다 많은 테이블을 외부 조인하는 WHERE 절에서는 (+) 연산자를 주어진 테이블에 한 번만 적용할 수 있습니다. 다음 예에서는 2개의 연속 조인에서 (+) 연산자로 SALES 테이블을 참조할 수 없습니다.

    select count(*) from sales, listing, event
where sales.listid(+)=listing.listid and sales.dateid(+)=date.dateid;
ERROR:  A table may be outer joined to at most one other table.

  • WHERE 절 외부 조인 조건이 TABLE2의 열을 상수와 비교하는 경우 그 열에 (+) 연산자를 적용합니다. 이 연산자를 포함하지 않으면 제한된 열을 위한 null을 포함하는 TABLE1의 외부 조인된 행이 제거됩니다. 아래 예시 섹션을 참조하세요.

예제

다음 조인 쿼리는 LISTID 열에 대한 SALES 및 LISTING 테이블의 왼쪽 외부 조인을 지정합니다.

select count(*)
from sales, listing
where sales.listid = listing.listid(+);

count
--------
172456
(1 row)

위와 동등한 다음의 쿼리는 결과는 같지만 FROM 절 조인 구문을 사용합니다.

select count(*)
from sales left outer join listing on sales.listid = listing.listid;

count
--------
172456
(1 row)

SALES 테이블은 모든 목록이 판매로 이어지는 것은 아니므로 LISTING 테이블의 모든 목록에 대한 레코드를 포함하지는 않습니다. 다음 쿼리는 SALES와 LISTING을 외부 조인하고 SALES 테이블이 주어진 목록 ID에 대해 아무런 판매도 보고하지 않을 때도 LISTING에서 행을 반환합니다. SALES 테이블에서 파생되는 PRICE 및 COMM 열은 일치하지 않는 행에 대한 결과 집합에 null이 있습니다.

select listing.listid, sum(pricepaid) as price,
sum(commission) as comm
from listing, sales
where sales.listid(+) = listing.listid and listing.listid between 1 and 5
group by 1 order by 1;

listid | price  |  comm
--------+--------+--------
1 | 728.00 | 109.20
2 |        |
3 |        |
4 |  76.00 |  11.40
5 | 525.00 |  78.75
(5 rows)

WHERE 절 조인 연산자가 사용될 때 FROM 절에서 테이블의 순서는 중요하지 않습니다.

WHERE 절에서 더 복잡한 외부 조인 조건의 예시는 조건이 두 테이블 열 사이의 비교 and 상수와의 비교로 구성되는 경우입니다.

where category.catid=event.catid(+) and eventid(+)=796;

(+) 연산자는 두 곳에 사용되는데, 첫째로는 테이블 사이의 동등성 비교에 사용되고 둘째로는 EVENTID 열에 대한 비교 조건에 사용됩니다. 이 구문의 결과는 EVENTID에 대한 제한이 평가될 때 외부 조인된 행이 보존되는 것입니다. EVENTID 제한에서 (+) 연산자를 제거하는 경우 쿼리는 이 제한을 외부 조인 조건의 일부가 아니라 필터로 취급합니다. 그러면 EVENTID에 대해 null을 포함하는 외부 조인된 행이 결과 집합에서 제거됩니다.

다음은 이 동작을 설명하는 완전한 쿼리입니다.

select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid(+)=796;

catname | catgroup | eventid
-----------+----------+---------
Classical | Concerts |
Jazz | Concerts |
MLB | Sports   |
MLS | Sports   |
Musicals | Shows    | 796
NBA | Sports   |
NFL | Sports   |
NHL | Sports   |
Opera | Shows    |
Plays | Shows    |
Pop | Concerts |
(11 rows)

FROM 절 구문을 사용하는 동등한 쿼리는 다음과 같습니다.

select catname, catgroup, eventid
from category left join event
on category.catid=event.catid and eventid=796;

이 쿼리의 WHERE 절 버전에서 두 번째 (+) 연산자를 제거하는 경우 1개의 행만 반환합니다(eventid=796인 행).

select catname, catgroup, eventid
from category, event
where category.catid=event.catid(+) and eventid=796;

catname | catgroup | eventid
-----------+----------+---------
Musicals | Shows    | 796
(1 row)