[SQL] join

전에 공부했던 내용이라 출처가 기억이 안난다. 어쨋든 잊어먹을 것 같아 다시 정리하는 시간을 갖기위해 블로그에 남기니 조인에 종류와 각각의 특성을 다시 정리하고 이해하고자 한다면 마음에 여유를 갖고 읽어보면 좋을 것 같다. 중간중간 연습문제와 답도 들어가 있음. 

 

01 | 조인의 종류

조인의 종류를 열거해 보면 내부 조인, 외부 조인, 동등 조인, 안티 조인, 셀프 조인, 세미 조인, 카타시안 조인(CATASIAN PRODUCT), ANSI 조인이 있는데, 이 8가지 조인 방법이 상대적으로 독립적인 개념은 아니다. 

즉 내부 조인의 상대 개념이 외부 조인이며, 외부 조인을 제외한 셀프 조인, 안티 조인 등은 모두 내부 조인에 포함된다. 또한 ANSI조인(sql표준에서 제공)​은 7가지 조인을 모두 포함한 개념으로 ANSI SQL을 사용한 점만 다를 뿐이며(일반적으로 ANSI 조인이라고 굳이 구분하지는 않는다) 이 책에서는 이해를 돕기 위해 별도로 구분하였다. 이 내용을 정리하면 다음과 같다.

• 조인 연산자에 따른 구분: 동등 조인, 안티 조인

• 조인 대상에 따른 구분: 셀프 조인

• 조인 조건에 따른 구분: 내부 조인, 외부 조인, 세미 조인, 카타시안 조인

위 세가지는 학술적인 분류로 위와 아래 조인은 다른개념이다.

• 기타: ANSI 조인/oracl 조인(오라클 내부에서만 사용 가능하다/비싸다) - 그래서 호환 문제가 있을경우 ANSI조인을 사용하나 오라클조인도 어느정도 알아놓아야 한다.

동등 조인(수평적 결합)​

가장 기본이 되며 일반적인 조인 방법이 바로 동등 조인(EQUI-JOIN)이다. 동등 조인은 WHERE 절에서 등호(‘=’)연산자를 사용해 2개 이상의 테이블이나 뷰를 연결한 조인이다. 즉 등호 연산자를 사용한 WHERE절 조건에 만족하는 데이터를 추출하는 조인이다. 이때 WHERE절에 기술한 조건을 조인 조건이라고 한다.​

​

--사원테이블의 데이터를 사원번호,사원이름,사원소속부서코드,소속부서명의 모든데이터를 출력하라.

SELECT   a.employee_id, a.emp_name, a.department_id, b.department_name

FROM    employees a,

            departments b -- 별칭을 만들면 반드시 별칭을 사용해야 한다.그러나 중복되는 컬럼의 경우 반드시 어느테이

                                   블것인지 지정해 줘야 에러가 나지 않는다.

WHERE   a.department_id = b.department_id;​

​

결과

EMPLOYEE_ID          EMP_NAME             DEPARTMENT_ID           DEPARTMENT_NAME

-----------               -----------------        -------------                  -------------------------

198                       Donald OConnell        50                            배송부

199                       Douglas Grant           50                             배송부

200                       Jennifer Whalen         10                             총무기획부

201                       Michael Hartstein       20                             마케팅

...

 

106 개의 행이 선택됨​사원과 부서 테이블에 공통적으로 존재하는 부서번호(department_id)를 등호 연산자를 사용해 조회조건에 명시했다. 부서번호 컬럼은 부서 테이블에서 키(Primary Key)에 해당해 필수 값이지만 사원 테이블에서는 필수 값이 아니므로 위 쿼리 결과는 사원 테이블에서 부서번호 컬럼 값이 있는 건만 추출된다. 즉 사원 테이블의 전체 건수가 107건이지만 부서번호가 없는 사원이 한 건 존재하므로 106건이 조회된 것이다.​

--모든 사원이 담당하고 있는 아래 업무관련 정보를 출력하라.

--출력내용:<employees>사원번호,사원이름,사원소속부서코드,<departments>소속부서명,<jobs>업무이름

--각 테이블간의 컬럼끼리의 관계를 빨리 파악

--절대 루프가 되는 코드를 만들어서는 안된다.

​

SELECT   a.employee_id, a.emp_name, a.department_id, b.department_name, c.job_id

FROM    employees a,

            departments b,

            jobs c

WHERE   a.department_id = b.department_id

and       a.job_id=c.job_id;

★ 테이블이 3개면 아래 조건식은 2개만 나올 수 있는것이다. 

​

--ansi inner join: 안씨에서 쓰는 join 표준 코드작성법. 

select  a.employee_id, a.emp_name, a.department_id, b.department_name

from   employees a inner join departments b (오라클의 , 대신 inner join)

on      a.department_id = b.department_id; (오라클의 where 대신 on)

​

select       a.employee_id, a.emp_name, a.department_id, c.job_id

from         employees a

inner join   departments b on a.department_id = b.department_id

inner join   jobs c on a.job_id=c.job_id

order by    a.employee_id asc;​

​

① 일반 조인

 

입력

SELECT   a.department_id, a.department_name, b.job_id, b.department_id

FROM    departments a, job_history b

WHERE   a.department_id = b.department_id;

 

결과

DEPARTMENT_ID      DEPARTMENT_NAME        JOB_ID             DEPARTMENT_ID

-------------             -----------------               -----------          ------------

20                         마케팅                          MK_REP             20

50                         배송부                          ST_CLERK           50

50                         배송부                          ST_CLERK           50

60                         IT                                IT_PROG             60

80                         영업부                          SA_MAN            80

80                         영업부                          SA_REP              80

90                         기획부                          AC_ACCOUNT      90

90                         기획부                          AD_ASST             90

110                       경리부                          AC_MGR             110

110                       경리부                          AC_ACCOUNT      110

 

10개의 행이 선택됨.

이 쿼리는 부서와 job_history 테이블을 부서번호 값을 조건으로 조인한 결과다. 따라서 job_hisotry에 없는 부서는 조회되지 않았다. 그런데 job_hisotry 테이블에는 없더라도 부서 테이블에 있는 모든 부서를 같이 보고 싶다면 어떻게 해야 할까? 바로 이때 다음과 같이 외부 조인을 사용한다.​

​​

② 외부 조인

 

입력

SELECT   a.department_id, a.department_name, b.job_id, b.department_id

FROM    departments a, job_history b

WHERE   a.department_id = b.department_id (+) ;

 

결과

DEPARTMENT_ID        DEPARTMENT_NAME       JOB_ID           DEPARTMENT_ID

-------------               -----------------              ----------         -------------

10                           총무기획부                   

20                           마케팅                          MK_REP          20

30                           구매/생산부                   

40                           인사부

50                           배송부                          ST_CLERK        50

50                           배송부                          ST_CLERK        50

60                           IT                                IT_PROG          60

...

31개의 행이 선택됨.

10개가 아닌 31개의 결과가 조회되었고 job_history에 없는 부서(10, 30, 40 등)도 모두 조회되었다. 쿼리를 자세히 보면 조인 조건에 (+) 기호가 붙어 있는데, 조인 조건에서 데이터가 없는 테이블의 컬럼에 (+) 기호를 붙이는 것이 바로 외부 조인이다. 당연히 10, 30, 40번 부서는 job_history에 데이터가 없으므로 NULL로 출력되었다. 또 다른 예를 살펴 보자.

 

입력

SELECT      a.employee_id, a.emp_name, b.job_id, b.department_id

FROM       employees a, job_history b

WHERE      a.employee_id = b.employee_id(+)

AND         a.department_id = b.department_id;

 

결과

EMPLOYEE_ID         EMP_NAME                 JOB_ID             DEPARTMENT_ID

-----------              --------------------         ----------          -------------

201                      Michael Hartstein          MK_REP            20

122                      PayamKaufling              ST_CLERK          50

176                      Taylor                         SA_MAN           80

176                      Jonathon Taylor             SA_REP            80

 

4개의 행이 선택됨.

데이터는 조회되었는데 뭔가 좀 이상하다. 분명히 (+)를 붙였는데도 모두 조회되어야 하는데 4건만 조회되었다. 왜 그런 것일까? 여기서 또 알아 두어야 할 내용이 있다. 다음과 같이 외부 조인은 조건에 해당하는 조인 조건 모두에 (+)를 붙여야 한다.

입력

select     a.employee_id, a.emp_name, b.job_id, b.department_id

from      employees a, job_history b

where     a.employee_id   =   b.employee_id(+)

and        a.department_id =   b.department_id(+);

결과

EMPLOYEE_ID        EMP_NAME               JOB_ID              DEPARTMENT_ID

-----------             --------------------       ----------           -------------

201                     MichaelHartstein         MK_REP            20

122                     PayamKaufling            ST_CLERK          50

176                     Jonathon Taylor          SA_REP             80

176                     Jonathon Taylor          SA_MAN           80

170                     Tayler Fox

150                     Peter Tucker

192                     Sarah Bell

106                     ValliPataballa

134                     Michael Rogers

...

 

108개의 행이 선택됨

원하는 대로 결과가 나왔다. 외부 조인 시 알아야 할 내용을 정리해 보자.

❶ 조인 대상 테이블 중 데이터가 없는 테이블 조인 조건에 (+)를 붙인다

❷ 외부 조인의 조인 조건이 여러 개일 때 모든 조건에 (+)를 붙인다

❸ 한 번에 한 테이블에만 외부 조인을 할 수 있다. 예를 들어, 조인 대상 테이블이 A, B, C 3개이고, A를 기준으로 B 테이블을 외부 조인으로 연결했다면, 동시에 C를 기준으로 B 테이블에 외부 조인을 걸 수는 없다

❹ (+)연산자가 붙은 조건과 OR를 같이 사용할 수 없다

❺ (+)연산자가 붙은 조건에는 IN 연산자를 같이 사용할 수 없다(단 IN절에 포함되는 값이 1개인 때는 사용 가능)​

​

--업무기록테이블에 존재하지 않는 부서 정보를 출력하라.

SELECT     a.department_id, a.department_name, b.job_id, b.department_id

FROM      departments a,job_history b

WHERE    a.department_id = b.department_id (+)

and         b.job_id is null;

​

​결과

​​

10        총무기획부    null     null​

30        구매/생산부   null     null​

40        인사부          null     null​

70        홍보부          null     null​

100       자금부          null     null​

120       재무팀          null     null​

130       세무팀          null     null​

140       신용관리팀    null     null​

150       주식관리팀    null     null​

160       수익관리팀    null     null​

170       생산팀          null     null​

*null 이 자료가 없어서인지 join에 의해서인지 구분해야 한다.

​

--right outer join은 결과적으로 inner join과 동일하게 된다.

SELECT      a.department_id, a.department_name, b.job_id, b.department_id

FROM       departments a, job_history b

WHERE      a.department_id(+)  = b.department_id ;​

​

오라클에서는 양쪽에 (+)를 붙이지 못하기 때문에 아래 ANSI JOIN을 사용해야한다.

--ANSI JOIN

--departments 에서 HISTORY가 없는 값까지 모두 출력​

SELECT   a.employee_id, a.department_name, b.job_id, b.department_id

FROM    departments a LEFT OUTER JOIN job_history b

ON        a.employee_id  =  b.employee_id;​

​

--아래 쿼리는 결국 INNER JOIN과 결과 값이 동일해지게 된다. 

SELECT    a.employee_id, a.department_name, b.job_id, b.department_id

FROM     departments a RIGHT OUTER JOIN job_history b

ON         a.employee_id  =  b.employee_id;​​

​

--departments테이블에서 history가 존재하지 않는 부서정보만 출력​

 SELECT   a.employee_id, a.department_name, b.job_id, b.department_id

 FROM    departments a LEFT OUTER JOIN job_history b

 ON        a.employee_id  =  b.employee_id

 WHERE   b.job_id is null;​

​

--departments 와job_history 모두의 정보를 출력하지만 1:n의 관계로 history에는 어짜피 일정정보만이 들어있어 의미가없어진다.​

SELECT  a.employee_id, a.department_name, b.job_id, b.department_id

FROM   departments a FULL OUTER JOIN job_history b

ON       a.employee_id  =  b.employee_id;​

​

신입사원은 히스토리가 존재하지 않을것이다. 

그런 사원까지 포함해서 출력하고자 할때.

또한 그 사원의 소속팀번호를 출력하고자 할때. 

반드시 비교절에 모두 (+)를 붙여줘야한다. 

   ​

--ANSI JOIN 여러 컬럼 만들기

SELECT    a.employee_id, a.emp_name, b.job_id, b.department_id

FROM     employees a LEFT OUTER JOIN job_history b

ON        ( a.employee_id  = b.employee_id

AND       a.department_id = b.department_id) ;

 

결과

EMPLOYEE_ID          EMP_NAME            JOB_ID          DEPARTMENT_ID

-----------               --------------------    ----------       -------------

201                       MichaelHartstein      MK_REP          20

122                       PayamKaufling         ST_CLERK        50

176                       Jonathon Taylor       SA_REP           80

176                       Jonathon Taylor       SA_MAN         80

170                       Tayler Fox

150                       Peter Tucker

192                       Sarah Bell

106                       ValliPataballa

134                       Michael Rogers

...

 

108개의 행이 선택됨

 

카타시안 조인=크로스 조인

카타시안 조인(CATASIAN PRODUCT)은 WHERE 절에 조인 조건이 없는 조인을 말한다. 즉 FROM 절에 테이블을 명시했으나, 두 테이블 간 조인 조건이 없는 조인이다. 조인 조건이 없으므로 엄밀히 말해 조인이라 말할 수 없을 수도 있지만, FROM 절에 2개 이상 테이블을 명시했으므로 일종의 조인이다. 조인 조건이 없으므로 그 결과는 두 테이블 건수의 곱이다. 즉 A 테이블 건수가 n1, B 테이블 건수가 n2라고 한다면, 결과 건수는 ‘n1 * n2’가 된다.

 

입력

SELECT  a.employee_id, a.emp_name, b.department_id, b.department_name

FROM   employees a, departments b;

 

결과

EMPLOYEE_ID          EMP_NAME             DEPARTMENT_ID         DEPARTMENT_NAME

-----------               --------------------    -------------                 --------------------------

198                       Donald OConnell      10                            총무기획부

199                       Douglas Grant          10                            총무기획부

200                       Jennifer Whalen        10                            총무기획부

201                       Michael Hartstein      10                            총무기획부

...

 

2,889개의 행이 선택됨.

사원 테이블의 총 건수는 107건이고 부서 테이블의 총 건수는 27건이므로, 107 * 27 = 2,889건이 조회되었다.​

 

SELECT     a.employee_id, a.emp_name, b.department_id, b.department_name,a.hire_date

FROM      employees a,

              departments b

WHERE     a.department_id = b.department_id

AND        a.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');

--조건식에 파라미터 값이 제공할 때 컬럼의 타입일치 2003-01-01 00:00:00와같이 시분초의의미가 있다.

​

/*날짜 비교 데이터 누락되는 경우가 있으니 출력되는 데이터 꼭 체크할 것.

<주의> a.hire_date > TO_DATE('2003-01-01','YYYY-MM-DD');

2003-01-01 09:00:00 일경우 위 조건에서 제외되게 된다. 주의!!

*/​

​

SELECT        a.employee_id, a.emp_name, b.department_id, b.department_name

FROM         employees a

INNER JOIN departments b

ON            (a.department_id = b.department_id )

WHERE a.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');

                        ▲

                      비 교

                        ▼

--조인시 using 키워드 사용: 중복되는 컬럼은 별칭을 생략해야 한다.

SELECT    a.employee_id, a.emp_name, department_id, b.department_name

FROM     employees a INNER JOIN departments b

USING     (department_id) --on 키워드 제외

WHERE    a.hire_date >= TO_DATE('2003-01-01','YYYY-MM-DD');​

--------------------------------------------------------

SELECT   a.emp_id, b.emp_id

FROM    hong_a a,

            hong_b b

WHERE   a.emp_id(+) = b.emp_id(+);-- 에러남

​

​

SELECT   a.emp_id, b.emp_id

FROM    hong_a a  FULL OUTER JOIN hong_b b

ON        ( a.emp_id = b.emp_id);-- ANSI JOIN으로 작업

--------------------------------------------------------------

​

SELECT   a.emp_id, b.emp_id

FROM    hong_a a FULL JOIN hong_b b  -- OUTER 생략 가능 그러나 생략 안하는것이 좋다.

ON        ( a.emp_id = b.emp_id);​

​

셀프 조인

셀프란 말에서 알 수 있듯이, 셀프 조인(SELF-JOIN)은 서로 다른 두 테이블이 아닌 동일한 한 테이블을 사용해 조인하는 방법을 말한다.

 

입력

SELECT    a.employee_id, a.emp_name, b.employee_id, b.emp_name, a.department_id

FROM     employees a, employees b -- 같은 테이블에 별칭을 다르게 줌.

WHERE   a.employee_id < b.employee_id......① -- 서로 다른 내용이 수평적으로 나오도록 해주기 위해 =를 쓰지 않고

                                                                 >나 <를 쓴다

AND       a.department_id = b.department_id

AND       a.department_id = 20;

 

결과

EMPLOYEE_ID        EMP_NAME            EMPLOYEE_ID          EMP_NAME         DEPARTMENT_ID

------------            --------------           ----------------         ---------------      -----------------------

201                     Michael Hartstein     202                       Pat Fay               20

 

사원 테이블을 A, B로 나누어 조인을 하는데, 같은 부서번호를 가진 사원 중 A 사원번호가 B 사원번호보다 작은 건을 조회하는 쿼리다. 사원 테이블에서 부서번호가 20인 건은 단 2건 뿐인데(201과 202), ①조건에 의해 결과는 1건만 추출된 점에 유념하자.

지금까지 설명한 조인 방법은 모두 내부 조인에 포함된다. 이제 외부 조인에 대해 알아 보자.​

​

​

---------------연습문제-------------------------------------------------

​

/*1 EMPLOYEES 테이블에서 급여가 3000~5000범위의 사원정보를 출력하라

 출력 컬럼명: 성명,담당업무,급여,부서번호

*/

SELECT    FIRST_NAME || LAST_NAME, JOB_ID,SALARY,DEPARTMENT_ID

FROM     EMPLOYEES

WHERE    SALARY >=3000 AND SALARY <=5000;

​

SELECT   FIRST_NAME || LAST_NAME, JOB_ID,SALARY,DEPARTMENT_ID

FROM    EMPLOYEES

WHERE  SALARY BETWEEN 3000 AND 5000;

​

/*2 사원번호가 145,152,203 인 사원정보를 출력하라.*/

​

SELECT *

FROM    EMPLOYEES

WHERE   EMPLOYEE_ID =145 OR EMPLOYEE_ID=152 OR EMPLOYEE_ID=203;

​

SELECT *

FROM    EMPLOYEES

WHERE   EMPLOYEE_ID IN(145,152,203);

​

/*3 급여가 2000~5000번위이면서, 부서번호가 10 또는 30번인

출력 컬럼명:사원이름,급여,부서번호*/

SELECT    FIRST_NAME||LAST_NAME,SALARY,DEPARTMENT_ID

FROM     EMPLOYEES

WHERE   SALARY >= 2000 AND SALARY <= 5000

AND       DEPARTMENT_ID=ANY(10,30);

​

SELECT    first_name, LAST_NAME, JOB_ID, SALARY, DEPARTMENT_ID  

FROM     EMPLOYEES

WHERE    SALARY >= 2000 AND SALARY <= 5000

AND       (DEPARTMENT_ID = 10 OR DEPARTMENT_ID = 30);

​

/*4 이름에 A와 E가 있는 모든 사원 이름을 출력하라*/

SELECT    FIRST_NAME||LAST_NAME

FROM     EMPLOYEES

WHERE    FIRST_NAME||LAST_NAME LIKE '%A%'

OR         FIRST_NAME||LAST_NAME LIKE '%E%';

​

/*5 부서별로 인원수,평균급여, 최저급여, 최고급여, 급여의 합을 출력하라.*/

SELECT         DEPARTMENT_ID,

                  COUNT(EMPLOYEE_ID),

                  ROUND(AVG(SALARY),2) AS AVG,

                  MIN(SALARY) AS MIN ,

                  MAX(SALARY) AS MAX,

                  SUM(SALARY) AS SUM

FROM          EMPLOYEES

GROUP BY    DEPARTMENT_ID

ORDER BY     1;

------------------------

/* 6 부서인원이 4명보다 많은 부서와 부서번호,인원수,급여의 합을 출력하라.*/

SELECT           DEPARTMENT_ID,COUNT(DEPARTMENT_ID),COUNT(EMPLOYEE_ID),SUM(SALARY)

FROM            EMPLOYEES

group by        DEPARTMENT_ID

HAVING COUNT(EMPLOYEE_ID)>4

ORDER BY       3;

​

/*7 각 업무별(JOB_ID)로 급여 합계가 10000을 초과하는 정보를 출력하라.출력은 급여를 내림차순으로*/

SELECT          JOB_ID,SUM(SALARY)

FROM           EMPLOYEES

GROUP BY     JOB_ID

HAVING SUM(SALARY)>10000

ORDER BY SUM(SALARY) DESC;

​

--조인문제

/*8 부서가 30이고, 급여가 1500이상인 사원이름,급여, 부서명,부서번호를 출력*/

SELECT         A.FIRST_NAME||LAST_NAME,A.SALARY,A.JOB_ID,B.DEPARTMENT_NAME,A.DEPARTMENT_ID

FROM          EMPLOYEES A, DEPARTMENTS B

WHERE         A.DEPARTMENT_ID = B.DEPARTMENT_ID

AND            A.DEPARTMENT_ID=30

AND            A.SALARY>=1500;

​

/*9. 직업별로 최소 급여를 받는 정보를 출력.*/

SELECT         A.JOB_ID,MIN(SALARY)

FROM           EMPLOYEES A INNER JOIN DEPARTMENTS B

ON              A.DEPARTMENT_ID = B.DEPARTMENT_ID

GROUP BY     A.JOB_ID;​