(DB)JOIN

오늘은 JOIN에 대해 공부하는 시간을 가져보도록 하겠다.

JOIN은 관계형 데이터 베이스에서 가장 핵심적인 기능을 가지고 있다. 아니중요한건 알겠는데 조인이 뭔데 인간아

조인은 직역하면 붙다,어울리다 라는 의미를 가지고 있다.

 

즉 테이블과테이블 간에 결합을 할때 사용한다

 

오라클 JOIN 문법 

 

원형

SELECT TABLE1.COL1 ,TABLE2.COL2

FROM TABLE1 ALIAS1 , TABLE ALIAS2

WHERE TABLE1.COL2 = TABLE.COL2

 

별칭부여

SELECT A.COL1 , B.COL1

FROM TABLE TABLE1 A, TABLE B,

WHERE A.COL = B.COL2

 

테이블에 별칭을 지정해주고 3번째줄에서 별칭을 사용

 

 

ANSI 문법

SELECT A.COL1 , B.COL1

FROM TABLE1 A , [INNER] JOIN TABLE2 B 

ON A.COL2 = A.COL2;

 

 

 

지금부터 볼 조인은 전부 INNER JOIN 이고 반대가 OUTTER JOIN 이다.

좀더 자세한것들은 예제와 설명을 통해 알아보자

 

 

카디션곱 CARTESIAN PRODUCT 

JOIN에서 가장 중요한 부분은 조인 조건을 지정해주는 부분이다.

예를 들어 EMP 테이블가 DEPT테이블에 가서 서로 관련 있는 데이터를 가져올때 이 조인 조건을 보고 가져 오는데 

만약 조건을 잘못주게되면 틀린데이터나 데이터를 가져오지 못하는 경우가 발생한다. 

그리고 특히 조인 조건(WHERE)을 적지 않게 되면 해당 테이블에 대한 모든 데이터를 가져오게 되는데 이걸 

카디션 곱이라고 한다.

 

CREATE TABLE TESTCAT1 (
NO NUMBER,
NAME VARCHAR2(2)
);

INSERT INTO TESTCAT1 VALUES(1,'A');
INSERT INTO TESTCAT1 VALUES(2,'B');

CREATE TABLE TESTCAT2 (
NO NUMBER,
NAME VARCHAR2(2)
);
INSERT INTO TESTCAT2 VALUES(1,'C');
INSERT INTO TESTCAT2 VALUES(2,'D');


CREATE TABLE TESTCAT3 (
NO NUMBER,
NAME VARCHAR2(2)
);
INSERT INTO TESTCAT3 VALUES(1,'E');
INSERT INTO TESTCAT3 VALUES(2,'F');

 

연습테이블을 만들고

SELECT a.NAME , b.NAME FROM testcat1 a , testcat2 b WHERE  a.NO = b.NO;

정상적인 조인을 수행한다

 

SELECT a.NAME , b.NAME FROM testcat1 a , testcat2 b;

이렇게 where 조건을 지정하지않으면 모든 경우의 수가 전부 나온다. 그럼 testcat1 a * testcat1 b로 계산 된 4개의

데이터가 나오게된다

 

SELECT a.NAME , b.NAME,c.NAME FROM testcat1 a , testcat2 b, testcat3 C WHERE A.NO =B.NO AND A.NO = C.NO;

 

 

SELECT a.NAME , b.NAME,c.NAME FROM testcat1 a , testcat2 b, testcat3 C WHERE A.NO =B.NO;

위부분에 WHERE절을 보면 2개의 테이블 조건만으로 카디션 곱을 생성한다

출력할것은 A (별칭) 테이블 NAME ,B (별칭) 테이블 NAME , C (별칭) 테이블 NAME이렇게3개이다

FROM 어디로 TESTCAT1,2,3 의 의름으로 부터    = TESTCAT 1 A ,TESTCAT  B ,TESTCAT 3 C 

WHERE 출력조건 A (별칭) 테이블의 넘버 = B (별칭) 테이블의넘버

을 주었기 때문에   A와 C  ,B와D는 동일한 자릿 수의 조건을 갇게되며

정렬되지 않은 C(별칭) 테이블의 넘버 는 지정이 되지않아 

A = C ,E 인것과 A = C ,F인것

B = D ,E 인것과 B= D ,F인것

(TESTCAT1 A= TESTCAT2 B)  * TESTCAT3 C  가 나오게되는것이다.

 

 

카디션 곱을 사용하는 이유는 데이터복제를 해서 원본테이블을 반복해서 읽는것을 피하기 위해서와

실수로 조인 조건 컬럼 중일부를 빠뜨리는경우에 사용한다

 

 

EQUI JOIN(등가 조인)

 

이조인 방법은 가장 많이 사용되는 조인으로 선행 테이블에서 데이터를 가져온 후 조인 조건절을 검사해서 동일한 조건을 가진 데이터를 후행 데이블에서 꺼내오는 방법이다.

내부조인(INNER JOIN) 단순 조인이라고도한다.

 

조건절에서 EQUAL 연산자 = 를 사용해서 EQUAL JOIN이라고 한다.

 

SELECT E.EMPNO, E.ENAME , D.DNAME 
FROM EMP E , DEPT D 
WHERE e.deptno = d.deptno;

SELECT E.EMPNO, E.ENAME , D.DNAME 
FROM EMP E JOIN DEPT D 
ON e.deptno = d.deptno;

E는 EMP 테이블의 별칭 D는 DEPT 의 별칭

 

위에서 살펴본바 같이 SELECT 절에서 테이블 이름 .컬럼이름 같은 형태로 적어주면 되는데 이름이 하나의 테이블에만

있을 경우에는 테이블 이름을 생략해도 자동으로 테이블 이름을 찾아서 실행하기도 합니다 (이말은 별칭 E.MEPNO 이렇게 안쓰고 그냥 EMPNO를 써도 얘가 자체적으로 읽는다는것) 하지만 양쪽에 EMPNO가 있을경우에는 별칭을 주던 테이블 원본을 앞에주던 구분을 줘야 에러가 안난다.

 

JOIN SQL 은 반드시 테이블 이름.컬럼이름으로 사용하는 습관을들이자.

 

 

학생과 교수테이블을 조인하여 학생과 교수이름을출력

SELECT S.NAME , P.NAME  ,D.DNAME
FROM student S ,department D ,professor P 
WHERE S.PROFNO = P.PROFNO ;

 

학생 학과 교수 테이블을 조인하여 학생의 이름과 학생의 학과이름 학생의 지도교수이름을 출력

 

SELECT S.NAME ,D.DNAME, P.NAME  
FROM student S ,department D ,professor P 
WHERE S.DEPTNO1 = d.deptno 
AND s.profno = p.profno;

SELECT S.NAME ,D.DNAME, P.NAME  
FROM student S  JOIN department D 
ON S.DEPTNO1 = d.deptno 
JOIN professor P
ON S.PROFNO = p.profno;

조인을 굉장히 어렵다라고 생각했는데 조건의 관계에 대해 생각하면 이해하기 쉬운거같다.

 

학생의 이름은 S.NAME 으로 출력

학생의 학과 이름이므로 학생테이블과 학과테이블의 공통 부분인 S.DEPTNO1 = d.DEPTNO

학생의 담당교수는 앞서 했던 교수와 학생의 공통부분 PROFNO

 

ANSI문법

차이가 조금있다면 

첫조건에 JOIN 으로 학생과 학과의 관계를 먼저이은후

ON에 어떤 조건이 같은지 넣어준다. 그럼이 조건에 

한번더 JOIN으로 교수테이블을 이어준후 

위조건에서 학생담당 교수이므로 학생과 교수의 공통부분을 넣어준다.

 

 

 

NON-EQUI JOIN (비등가 조인)

 

JOIN 조건에 EQUAL 연산자를 = 사용하지않고 다른 연산자나 함수를 사용하여 JOIN하는 형식

 

 

CUSTOMER 테이블과 GIFT 테이블을 조인하여 고객별로 마일리지 포인트를 조회한 후 해당마일리지

점수로 받을수 있는 상품을 조회하여 고객의 이름과 받을 수 있는 상품명을 출력하라.

 

SELECT c.gname , C.POINT ,G.GNAME
FROM customer C , gift G 
WHERE C.point BETWEEN g.g_start 
AND g.g_end;

SELECT c.gname , C.POINT ,G.GNAME
FROM customer C JOIN gift G 
ON C.point BETWEEN g.g_start 
AND g.g_end;

해석 

FROM 까지는 아실거라 믿고 WHERE 절부터 설명하겠다 .

POINT 컬럼의 gift START 보다 크거나 같고  gift END 보다 작거나 같은조건이므로

G.GNAME 상품은 당연히 POINT 기준으로 START >=   <=END 조건으로 출력되겠지요?? 

 

SELECT c.gname , C.POINT ,G.GNAME
FROM customer C , gift G 
WHERE C.point >= g.g_start 
AND C.point <= g.g_END;

이렇게도가능하다(가독성이 이게 더좋다고 한다.)

 

STUDENT 테이블과 SCORE 테이블 HAKJUM 테이블을 조회하여 학생들의 이름과 점수와 학점을 출력 하라.

SELECT S.NAME ,C.TOTAL ,H.GRADE
FROM STUDENT S , SCORE C , HAKJUM H
WHERE s.studno =c.studno
AND C.TOTAL BETWEEN H.MIN_POINT
AND H.MAX_POINT;

SELECT S.NAME ,C.TOTAL ,H.GRADE
FROM STUDENT S , SCORE C , HAKJUM H
WHERE s.studno =c.studno
AND C.TOTAL >=H.MIN_POINT
AND C.TOTAL <=H.MAX_POINT;


ACSI문법
SELECT S.NAME ,C.TOTAL ,H.GRADE
FROM STUDENT S JOIN SCORE C 
ON s.studno =c.studno JOIN HAKJUM H
ON C.TOTAL BETWEEN H.MIN_POINT
AND H.MAX_POINT;


SELECT S.NAME ,C.TOTAL ,H.GRADE
FROM STUDENT S JOIN SCORE C 
ON s.studno =c.studno JOIN HAKJUM H
ON C.TOTAL >=MIN_POINT
AND C.TOTAL <=MAX_POINT;

 

출력할것은 3개 정해놓고 

STUDENT 와 SCORE의 관계를 이어줘야한다 아니면 (카디션 곱이 일어남)

관계를 이어줬으니 STUDENT테이블의 학생점수가 생겼다 그럼이 점수를

이제 비교 연산자나 BETWEEN을 이용해서 맥스와 민의 점수를 주면  학점은 

그비교 값에 맞게 출력된다 

 

OUTER JOIN

포토샾으로 대강만들었다 이해부탁바란다.

 

앞서 살펴본 등가조인과 ,비등가조인의 공통점은 조회하려는 데이터들이 조인에 참여하는 모든 테이블에 데이터가

존재하는 경우만 결괏값을 출력했다. 이런것들을 다 INNER JOIN이라고 한다.

 

지금부터 살펴볼 아우터 조인이란 이너조인과 반대로 한쪽 테이블에는 데이터가 있고 한쪽테이블에 없는 경우에 

데이터가 있는 쪽 테이블의 내용을 전부 출력하게 하는방법이다.

또한 기본적으로  아우터 조인은 NULL값이존재하면 그행은 아예 출력에서 제외된다.

 

STUDENT 테이블과 PROFRSSOR 테이블을 조인하여 학생 이름과 지도교수이름을 출력하되 지도교수가 

결정되지않은 학생의 명단도 함께출력하라.

SELECT S.NAME , P.NAME 
FROM STUDENT S , PROFESSOR P
WHERE s.profno =p.profno(+);

SELECT S.NAME , P.NAME 
FROM STUDENT S LEFT OUTER JOIN PROFESSOR P
ON s.profno =p.profno;

3번째 줄에 (+)가 추가된것을 볼수 있다.

WHERE 조건절에서 데이터가 없는쪽에 (+)를 추가하면된다. 지금보면 교수테이블에 +를해주었는데 학생은 존재하지만

교수가 없는 내용을 출력해야하므로 교수테이블에 +가 붙은것이다

 

ANSI 문법

 

오라클 OUTTER JOIN과 문법이 조금다르다 무엇보다 중요한것은 오라클문법같은경우는 없는쪽에 (+)를하지만 

ANSI는 데이터가 있는쪽에 표시를 해준다

 

LEFT OUTTER JOIN이라는 구문은  데이터가 존재하는쪽에 표시를 하기때문에 

ON 조건에 = 기준으로 학생데이터가 왼쪽에 있으므로 데이터가 있는행을 출력하기 위해  LEFT OUTTER JOIN

구문을 사용한것이다.

 

그럼이런경우는 어떨까??

 

 

STUDENT 테이블과 PROFRSSOR 테이블을 조인하여 학생 이름과 지도교수이름을 출력하되 지도교수가

결정되지않은 학생의 명단과 학생이 정해지지않은 지도교수의 명단도 출력하라 

 

SELECT S.NAME , P.NAME 
FROM STUDENT S , PROFESSOR P
WHERE  s.profno(+) =p.profno
UNION
SELECT S.NAME , P.NAME 
FROM STUDENT S , PROFESSOR P
WHERE  s.profno =p.profno(+)
;

이런식으로 두가지 출력문을 UNION 혹은 UNION ALL로 붙여주어야 하는데

 

ANSL는 훨씬간단한 기능을 제공해준다

 

SELECT S.NAME , P.NAME 
FROM STUDENT S FULL OUTER JOIN PROFESSOR P
ON s.profno =p.profno;

 

 

그리고 오라클 아우터 조인은 WHERE 절에 아우터조인이 되는컬럼 전체에 (+)을붙여야한다.

이게 무슨말인지는 다음예제를 보며 설명하겠다.

 

SELECT D.DEPTNO, D.DNAME , D.LOC ,E.EMPNO ,ENAME ,E.SAL 
FROM dept D , EMP E 
WHERE d.deptno = e.deptno(+)
 AND e.deptno(+) =20;

부서정보가 전부 출력되지않았다 

왜??

마지막 조건절에 EMP.DEPTNO =20; 주었기때문에 마지막 뽑아올데이터는 저조건의 데이터를 가져오기때문이다 

저렇게 모든 조건 뒤에 (+) 을 추가 해주어야한다.

 

다음은 ANSI문법사용시 주의점에 대해알아보자

 

직업이 CLERK인 사원정보 를 출력 하고 그중에 시카고에 소속된 사원의 부서정보를 출력하라

SELECT E.EMPNO,E.ENAME, E.JOB , D.DEPTNO ,D.DNAME , D.LOC 
FROM EMP E LEFT OUTER JOIN DEPT D
ON  E.DEPTNO = d.deptno
AND LOC='CHICAGO'
WHERE E.JOB ='CLERK'
;

만약 WHERE 조건을 아우터 조건인 ON 절에 넣으면어떻게 될까?? 

이렇게 나온다 이유는???

 

ON절은 아우터 조인이 되는 조건절을 기술한다고 했다.

기준점이 되는 EMP테이블에 조건(WHERE)에 맞는 집합을출력후 아우터 조인 조건(ON)을 만족하는 집합만 아우터 조인하는것이다.

그럼 일단 WHERE 조건이 없으므로 전체가 출력되고 CHELCK 부서이면서 CHICAGO인사원의 부서정보를 보여주는것이다.

전체 타이틀에 출력을 조건으로 주고 싶으면 WHERE에 조인조건에 만족하는 결과는 ON

 

 

SELF JOIN 

 

위의 조인들은 모두 원하는 데이터들이 흩어져있었다.

그래서 여러테이블들을 조인하여 사용했는데 만약 원하는 데이터가 하나의 테이블에 다들어있다면어떡할까??

 

 

EMPNO는 사원번호이며 MGR은 상사의 사원번호다

"EMP 테이블의 SMITH의 상사는 FORD다" 일경우 출력에 필요한데이터가 EMP 테이블내에 전부 존재한다

그럴때 사용하는 조인이 SELF JOIN이다.

원리는 데이터를 가지고 있는테이블을 메모리에서 별명을 두개로 사용해서 호출하는 방법으로 2개의 테이블로 만든후

일반적인 JOIN 작업을 하는것이다.

SELECT E1.ENAME ,E2.ENAME
FROM EMP E1 ,EMP E2 
WHERE E1.MGR =E2.EMPNO;

SELECT E1.ENAME ,E2.ENAME
FROM EMP E1 JOIN EMP E2 
ON E1.MGR =E2.EMPNO;

 

정말쉽다 그냥 테이블내에 가져올 컬럼의 별명을 하나 더지어주고 JOIN을 하면되는것이다

 

 

JOIN이 어렵다고 생각했는데 솔직히 지금공부한 SQL 문중에 가장재밌었다. 

다들 너무 어렵다고만 생각하지말고 하나하나씩 이해하면서 공부한다면 시간은오래 걸리겠지만 그만큼 자신에게 도움이 될것이다.