[복습] SQL | Oracle 첫 번째 마주한 큰 산, join (1)

드디어 join을 배웠다. 오늘 배우는 내내 머리에 힘 주느라 너무 지쳤지만,  join 공부는 끝내고 집에 가야겠다. 복습 후기는 끝에 가서 풀기로 하고 바로 join으로 들어가겠다.

 

join 다음에 서브쿼리도 함께 배울테지만, join과 서브쿼리는 함수가 아니라 표현식이다.

join은 분리된 두 테이블을 참조한다.

엑셀의 vlookup처럼 분리된 한 쪽 테이블 컬럼 값을 다른 테이블을 참조해야지만 가져올 수 있는 경우 같은 것이다.

정리하자면, 다른 쪽 테이블 정보를 참조하여 필터링 하거나 혹은 원하는 결과를 출력하는 등 여러 목적을 가지고 있다.

 

다음 예제에서 살펴보겠다.

emp, dept 테이블을 사용하여 사원이름, 급여, 부서명을 출력하여라.

emp에는 deptno라는 컬럼이 있고 dept테이블에도 deptno 컬럼이 있다.

 

이렇게 출력하면 emp14건, dept6건, 14 * 6 하여 총 56건이 출력되었다. 그러나 deptno라는 데이터끼리 연결조건이 명시되지 않아서 모든 데이터를 출력한 것이기 때문에 의미가 없다.

deptno가 같은 것끼리 연결이 되어 출력되어야 한다. 그리고 나머지들은 생략되어야 한다.

 

where절에 이렇게 명시하면 에러가 생긴다. 둘 다 deptno가 있기 때문에 동일 이름의 컬럼명이라 테이블 출처가 필요하다. 

 

이렇게 하면 14건 출력이 된다. 

그런데 현업에서는 테이블명이 emp, dept처럼 세 자 혹은 네 자 정도가 아니다.

이때마다 테이블 명을 다 적으면 코드가 지저분해지므로 테이블 alias를 쓸 것이다. from 절에서는 as 없이 쓴다.

 

select에 컬럼이 올 때에는 다음과 같이 쓴다.

 

이것이 join 기법이다.

 

예제를 하나 풀어보겠다.

student와 exam_01테이블을 사용하여 각 학생의 이름, 학년, 제1전공번호(deptno1), 성적을 출력하여라.

 

이렇게 해결하였다.

 

 

728x90

 

 

그런데 현업에서는 두 개의 테이블만 결합하는 것이 아니다.

 

다음 예제로 위의 문제에서 학과번호 대신에 학과명을 붙여보자.

세 개의 테이블을 연결하기 위해서는 최소 두 개의 조건이 필요한 것을 참고하면 된다.

 

student, exam_01, department 테이블을 사용하여 각 학생의 이름, 학년, 제1전공명, 성적을 출력해보자.

 

 

한 문제를 더 풀어보자.

student, exam_01, department테이블을 사용하여 각 학생의 이름, 학년, 제1전공명, 제2전공명, 성적을 출력하여라.

처음에 이 문제를 해결하지 못하였다. 

select s.name, s.grade, d.dname as 제1전공명, s.deptno2 as 제2전공명, e.total
  from student s, exam_01 e, department d
 where s.studno = e.studno
   and s.deptno1 = d.deptno
   and s.deptno2 = d.deptno;

 

이런 형식으로 작성해서 오류가 났다. 결과적으로 deptno1 = deptno2 이렇게 같은 데이터를 출력하는 쿼리로 해석이 된 것이다. 그러므로 from에 정의를 두 번 해주어야 한다.

 

이렇게 풀어주면 된다. 그런데 왜 4 건만 출력이 될까?

이 조건을 충족시키는 데이터가 네 개이기 때문이다. 제2전공값에 null값이 많다.

where은 조건에 성립하지 않는 데이터는 생략하기 때문에, 조인 조건에 성립하지 않아 데이터를 생략했다.

생략된 데이터를 전달하기 위해서는 inner 조인이 아니라  outer 조인이라는 것을 해야하는데 다음에 설명하겠다.

 

반응형

 

SQL에서 조인 조건이 =(equi) 조건 뿐만 아니라 non-equi  조건도 가능하다(즉 대소 비교가 가능함).

 

non-equi를 예제로 풀어보자

gogak, gift 테이블을 이용하여 각 고객의 포인트별로 고객이 가져갈 수 있는 상품을 출력해보자. 전제로 가장 좋은 상품만을 가져갈 수 있다.

 

비슷한 문제 하나 더 풀어보자.

student, exam_01, hakjum 테이블을 사용하여 각 학생의 이름, 학년, 성적, 학점을 출력하여라.

 

join을 정리해보자.

 

join의 종류(시험문제에도 자주 언급)

join의 형태에 따른 분류

1. equi join: join 조건에 항상 = 인 경우(동등 조건인 경우)

2. non equi join: 조건이 =가 아닌 경우(동등이 아닌 경우, between, 대소 비교 등등)

이 둘은 반대 개념

 

출력 데이터 형태에 따른 분류

3. inner join: 개념 자체가 중요한 join임. ★ default join 기법으로 굳이 명시하지 않으면 기본 선택된다.

                     join 조건에 성립(만족)하는 데이터만을 출력한다.

4. outer join: join 조건에 만족하지 않는 데이터도 생략없이 추출한다. 필요시 join 조건에 만족하지 않더라도 추가적으로 출력을 원할 때 사용하면 된다.

이 둘도 반대 개념이다.

 

5. natural join: join 컬럼이 양 테이블에 같은 이름으로 있는 경우에는 join 조건 없이도 자동 join.

6. cross join: 조인 조건 생략 시 카티시안 프로덕트(곱)를 출력하는 경우에 사용한다(전체조합)

                     거의 쓸 일이 없다. 잘못된 join임을 알려주는 것이다.

7. self join: 하나의 테이블을 스스로 join(두 번 이상의 table access가 필요한 경우). 한 번 조회하면 얻을 수 없는데 한 번 더 조회하면 얻을 수 있는 정보가 있는 경우에 사용한다.

select * from emp; 를 보면 MGR이 매니저 번호인데 SMITH의 MGR은 FORD임을 찾아야 한다.

한 행만 읽었을 때에는 SMITH의 MGR 이름을 할 수가 없다(MGR 번호만 있을 뿐임).

이렇게 사용하는데 서로 다른 테이블이라고 가정하면 쉽다.

총 14건이 출력되어야 하는데 13건이 출력되었다. 왜냐하면 KING은 사수가 없기 때문이다. 이 방법이 inner join이다.

여기서 KING 생략 없이 전체 직원 정보를 출력한다면,

이렇게 e2.empno(+)를 붙여주면 된다. 이렇게 풀이하면 outer join이다.

 

outer join에 관한 문제를 하나 풀어보겠다.

student, professor 테이블을 이용하여 각 학생의 이름, 학년, 지도교수명을 출력하여라(student의 frofno가 지도교수번호).

 

두 테이블 중 어떤 테이블 기준으로 부족한 테이블을 작성하는 느낌이 드는가? student가 기준이 된다.

그러므로 where절에 p.profno뒤에 (+)를 붙여주면 된다.

즉 where절 기준테이블 조건의 반대쪽 컬럼 뒤에 (+)를 붙여서 outer join을 수행하는 것이다.

 

가끔 join 조건이 복잡해지기도 한다. to_char(hiredate, 'YYYY')에 (+)를 붙이는 경우 to_char(hiredate, 'YYYY')(+) 이게 아니다. to_char(hiredate(+), 'YYYY') 이렇게 해야한다.

컬럼이 여러 번 나열되는 경우, 예를 들어 substr(tel, 1, instr(tel, ...)) 경우 (+)를 그때그때 넣어야 한다.

substr(tel(+), 1, instr(tel(+), ...))  이렇게 넣으면 된다. 그 컬럼이 어디에 있든, 몇 개이든 다 붙여야 한다.

 

위의 문제에서 실수로 (+) 위치를 잘못 출력하면,

오히려 지도교수명이 다 나온다. 교수 정보를 우선적으로 보되, 교수 정보를 토대로 지도학생이 출력된다.

즉 professor 테이블이 기준이 되었다. 여기서 지도교수명이 두 건인 경우가 있다. 한 교수 당 지도 학생이 여러 명의 경우인데, 1:다 매칭으로 출력된 것이다.

다:1, 다:다 관계가 될 수도 있기 때문에 원하는 데이터를 출력하려면 이런 걸 먼저 고민한 후 join 문법을 작성하여야 한다.

 

 

 

join 문법 function은 DBMS마다 다를 수 있으나 함수를 제외한 문법은 DBSM마다 같다. 그러나 join 문법은 DBMS마다 다르다.

1) oracle이 유독 우월주의가 있어 oracle 특유의 자체 문법을 만든다. 이것이 oracle 표준이다.

 from 절에 join 하고자 하는 대상(테이블)을 콤마(,)로 나열하면 된다. 즉 join 종류를 나타낼 필요가 없다. 

일반 조건과 join 조건 모두 where 절에 조건을 전달한다.

 

2) ansi 표준(국제적 표준, non-oracle)은 oracle이 아닌 타 RDBMS 인 것들이다. 물론 oracle에서도 사용 가능하다. 

from 절에 테이블을 나열하는데 두 테이블 사이에 join 종류를 명시하여야 한다.

그리고 join 조건과 일반 조건을 분리하기 때문에 on절에 join 조건을 전달한다(일반 조건과 join 조건의 위치가 분리됨).

 

문제로 바로 적용해보겠다.

emp, dept 테이블을 사용하여 10번 부서가 아닌 직원에 대하여 사원이름, 급여, 부서명을 출력하여라.

oracle 표준과 ansi 표준 둘 다 사용할 것.

 

outer join의 경우 세 가지로 구분이 된다.

- left outer join:  join이 되는 데이터가 왼쪽에 있을 때(왼쪽 테이블 기준)

- right outer join: join이 되는 데이터가 오른쪽에 있을 때(오른쪽 테이블 기준)

- full outer join: 업무적으로 사용되는 빈도가 발견 불가능할 정도로 거의 없다. 양쪽 테이블 기준이 된다.

 

위에서 풀어 본 문제 중 

이것이 outer join의 oracle 형태이다.

이 형식은 student를 먼저 썼으니 left outer이다.

그러므로 ansi로 고쳐 작성해보면

이렇게 작성할 수 있다.

그렇다면 같은 표현으로 right outer join으로 표현해보면

 

로 작성할 수 있다.

 

그러면 테이블이 여러 개 존재하는 경우에는 어떻게 ansi 표준으로 작성할 수 있을까?

이러한 oracle 표준을 ansi 표준으로 나타내면 다음과 같다.

 

 

full outer join은 위험한 문법이다. 업무적으로도 선생님도 써보신 적이 없다고 하셨다.

악성 쿼리가 될 가능성이 있으므로  oracle은 이 문법을 아예 금지해두었다. 

그러므로 (+)를 양 옆으로 붙일 수가 없다. 오류가 발생한다.

양쪽 테이블 모두 기준으로 데이터를 출력해보자.

에러메세지를 확인해보면, outer-join된 테이블은 1개만 지정할 수 있습니다. 라고 뜬다.

 

oracle에는 이렇듯 full outer join 구문은 없지만 필요시 두 집합(left outer join, right outer join)의 합집합을 표현하는 문법으로 대체가 가능하다.

위 과정을 ansi 표준에서는 출력이 가능하다.

 

순서가 달라질 수 있겠으나 정상 출력된다.

 

마지막 두 문제를 풀어보고 마무리를 하려 했으나 시간 관계상 집에 가서 풀이해야겠다.