오라클 SQL과 PL/SQL ( INNER JOIN - 비등가 조인 )

Non-Equi JOIN(비등가 조인)

앞에서 EQUI 조인은 조인 조건절에 조건을 지정할 때 조인 대상 테이블들에서 서로 같은 조건을 가진 데이터를 가져왔습니다.
그러나 실제 상황에서는 같은 조건이 아닌 크거나 작거나 하는 경우의 조건으로 조회를 해야할 경우도 얼마든지 있을 수 있습니다.
그럴 때 사용하는 조인 방법이 비등가 조인입니다. 자세한 예로 알아보겠습니다.

사용 예1. CUSTOMER테이블과 GIFT테이블을 조인하여 고객별로 마일리지 포인트를 조회한 후 해당 마일리지 점수로 받을 수 있는 상품을 조회하여 고객의 이름과 받을 수 있는 상품 명을 아래와 같이 출력하세요.

ORACLE
SELECT C.GNAME CUST_NAME, TO_CHAR(C.POINT, '999,999') POINT, G.GNAME GIFT_NAME
FROM CUSTOMER C, GIFT G
WHERE C.POINT BETWEEN G.G_START AND G.G_END;

OR

SELECT C.GNAME CUST_NAME, TO_CHAR(C.POINT, '999,999') POINT, G.GNAME GIFT_NAME
FROM CUSTOMER C, GIFT G
WHERE C.POINT >= G.G_START 
AND C.POINT <= G.G_END;

ANSI
SELECT C.GNAME "CUST_NAME", TO_CHAR(C.POINT, '999,999') "POINT", G.GNAME "GIFT_NAME"
FROM CUSTOMER C JOIN GIFT G
ON C.POINT BETWEEN G.G_START AND G.G_END;

 

* BETWEEN 보다 비교연산자가 성능에는 더 좋습니다.


사용 예2. 학생테이블과 점수테이블, 학점테이블을 조회하여 학생들의 이름과 점수와 학점을 출력하세요.

ORACLE

SELECT ST.NAME, SC.TOTAL, H.GRADE 
FROM STUDENT ST, SCORE SC, HAKJUM H
WHERE ST.STUDNO = SC.STUDNO 
AND SC.TOTAL >= H.MIN_POINT 
AND SC.TOTAL <= H.MAX_POINT;

ANSI

SELECT ST.NAME, SC.TOTAL, H.GRADE 
FROM STUDENT ST JOIN SCORE SC
ON ST.STUDNO = SC.STUDNO
JOIN HAKJUM H
ON SC.TOTAL >= H.MIN_POINT 
AND SC.TOTAL <= H.MAX_POINT;