오라클)조인(join) - 조인 범위(이너 조인, 등가 조인)

SAMPLE DATA

DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1(c1 NUMBER, c2 NUMBER, c3 NUMBER);
CREATE TABLE t2(c1 NUMBER, c2 NUMBER, c3 NUMBER);

INSERT INTO t1 VALUES(1,1,1);
INSERT INTO t1 VALUES(2,2,null);
INSERT INTO t1 VALUES(3,3,null);
INSERT INTO t1 VALUES(4,null,null);
INSERT INTO t2 VALUES(1,1,1);
INSERT INTO t2 VALUES(2,2,null);
INSERT INTO t2 VALUES(3,null,3);
INSERT INTO t2 VALUES(4,null,null);

t1.c1	t1.c2	t1.c3	t2.c1	t2.c2	t2.c3
1	1	1	1	1	1
2	2		2	2
3	3		3		3
4			4

 

---

이너 조인(inner join)

조인이 성공한 범위를 반환.

파란색으로 표시된 부분이 이너 조인.

SELECT a.c1 AS ac1, a.c2 AS ac2, a.c3 AS ac3, b.c1 AS bc1, b.c2 AS bc2, b.c3 AS bc3
FROM t1 a, t2 b
WHERE b.c1 = a.c1
  AND b.c2 = a.c2
  AND b.c3 = a.c3;

 

NUL을 이용해서 널을 포함하여 주인 수행 가능.

SELECT a.c1 AS ac1, a.c2 AS ac2, a.c3 AS ac3, b.c1 AS bc1, b.c2 AS bc2, b.c3 AS bc3
FROM t1 a, t2 b
WHERE NVL(b.c1, -1) = NVL(a.c1, -1)
  AND NVL(b.c2, -1) = NVL(a.c2, -1)
  AND NVL(b.c3, -1) = NVL(a.c3, -1);