๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ
ORACLE/SQL

[Oracle] NON-EQUI JOIN ๋น„๋“ฑ๊ฐ€ ์กฐ์ธ ์ •๋ฆฌ :: ๋งˆ์ด์ž๋ชฝ

by ๐ŸŒปโ™š 2020. 3. 19.

NON-EQUI JOIN

๋‘๊ฐœ์˜ ํ…Œ์ด๋ธ”์„ ํŠน์ •ํ•œ ํ‚ค๋ฅผ ๊ธฐ์ค€์œผ๋กœ ํ•ฉ์ณ์„œ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œํ˜„ํ•˜๋Š” ๊ฒƒ์„ JOIN์ด๋ผ๊ณ  ํ•ฉ๋‹ˆ๋‹ค. ์‚ฌ์› ํ…Œ์ด๋ธ”์— ๋ถ€์„œ ๋ฒˆํ˜ธ๊ฐ€ ์žˆ๊ณ  ๋ถ€์„œ ํ…Œ์ด๋ธ”์— ๋ถ€์„œ๋ช…์ด ์žˆ์–ด์„œ ์‚ฌ์›์˜ ์ด๋ฆ„๊ณผ ๋ถ€์„œ๋ช…์„ ํ™•์ธํ•˜๊ธฐ ์œ„ํ•ด์„œ ์‚ฌ์›ํ…Œ์ด๋ธ”์˜ ๋ถ€์„œ๋ฒˆํ˜ธ์™€ ๋ถ€์„œ ํ…Œ์ด๋ธ”์˜ ๋ถ€์„œ๋ฒˆํ˜ธ๋กœ JOIN ์‹œ์ผœ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œํ˜„ํ•  ์ˆ˜ ์žˆ์Šต๋‹ˆ๋‹ค. ์ด๋ ‡๊ฒŒ ํŠน์ • ์นผ๋Ÿผ์€ ๊ฐ™๋‹ค๋Š” ํ˜•์‹์œผ๋กœ ์กฐ์ธ์„ ์‹œํ‚ค๋Š” ๊ฒƒ์€ EQUI JOIN์ด๋ผ๊ณ  ํ•ฉ๋‹ˆ๋‹ค. ๊ทธ๋Ÿผ NON-EQUI JOIN์€ ๋ญ˜๊นŒ์š”?

 

์ƒํ˜ธํ‰๊ฐ€ ๋“ฑ๊ธ‰, ์„ฑ์  ๋“ฑ๊ธ‰๊ณผ ๊ฐ™์ด ํŠน์ • ๋ฒ”์œ„์™€ ๋“ฑ๊ธ‰์„ ์ง€์ •ํ•˜๊ณ  ํ•ด๋‹น ๋ฒ”์œ„์— ๋“ค์—ˆ์„ ๋•Œ ๋“ฑ๊ธ‰์œผ๋กœ ๋ฐ์ดํ„ฐ๋ฅผ ํ‘œํ˜„ํ•˜๊ธฐ ์œ„ํ•ด ์‚ฌ์šฉ๋˜๋Š” ๊ฒƒ์ด NON-EQUI JOIN์ž…๋‹ˆ๋‹ค. NON-EQUI JOIN์€ EQUI JOIN๊ณผ ๋‹ค๋ฅด๊ฒŒ ํŠน์ • ์นผ๋Ÿผ์„ ๋ฒ”์œ„๋กœ JOIN ์‹œํ‚ต๋‹ˆ๋‹ค.

 

Oracle Scott ๊ณ„์ •์œผ๋กœ NON-EQUI JOIN์„ ์•Œ์•„๋ณด๊ฒ ์Šต๋‹ˆ๋‹ค.

 

EMP TABLE

1
2
3
4
5
SELECT
    ENAME
    ,JOB
    ,SAL
FROM EMP;

EMP ํ…Œ์ด๋ธ”์˜ SAL ์นผ๋Ÿผ์˜ ๊ฐ’์„ ๊ธฐ์ค€์œผ๋กœ ๋“ฑ๊ธ‰์„ ๋งค๊ฒจ์ค„๊ฒ๋‹ˆ๋‹ค.

 

SALGRADE TABLE

1
SELECT * FROM SALGRADE;

SALGRADE ํ…Œ์ด๋ธ”์˜ LOSAL๊ณผ HISAL์„ ๊ธฐ์ค€์œผ๋กœ GRADE ์นผ๋Ÿผ์„ ์ด์šฉํ•ด์„œ ๋“ฑ๊ธ‰์„ ๋งค๊ธฐ๊ฒ ์Šต๋‹ˆ๋‹ค.

 

 

์‚ฌ์› ์›”๊ธ‰๋ณ„ ๋“ฑ๊ธ‰

Oracle Syntax

1
2
3
4
5
6
7
8
SELECT
    ENAME
    ,JOB
    ,SAL
    ,GRADE
FROM EMP, SALGRADE
WHERE SAL >= LOSAL
AND SAL <= HISAL;

 

ANSI Syntax

1
2
3
4
5
6
7
SELECT
    ENAME
    ,JOB
    ,SAL
    ,GRADE
FROM EMP JOIN SALGRADE
ON SAL BETWEEN  LOSAL AND HISAL;

 

๋Œ“๊ธ€