sql 조인

여러 테이블에서 흩어져 있는 정보 중에서 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들어서 결과를 보여주는 것. 즉 두 개 이상의 테이블을 합치는 기능

 

표준(ANSI) 조인의 5가지

 Cross 조인, Equi 조인, Non-Equi 조인, Self 조인, Outer 조인

 

참고> (ANSI-American National Standards Institute 미국국립표준협회)

 

cross 조인

• 다른말로 Cartesian Product(카르테시안 프로덕트) 부름
•   - 테이블 행수를 모두 곱한 결과가 만들어짐
• - 10행 테이블과 5행 테이블이 cross 조인하면 10*5=50행의 테이블이 만들어짐

ex>

select student.*, enrol.*

from student cross join enrol;

 

-특정 테스트 목적이 아니고선 사용할 일 없음

 

EQUI 조인-1

2개 이상의 테이블에 관련 있는 공통 열의 값을 이용하여 논리적으로 결합하는 연산이 수행되는 조인. Equal 연산자(=)를 사용해서 Equi join(이퀴조인)이라고 함  - 가장 많이 사용

ex> select student.stu_no, stu_name, stu_dept, enr_grade

      from student, enrol

      where student.stu_no=enrol.stu_no;        

EQUI 조인-2

다른 표현법으로 natural join 사용 (같은 열을 이용하여 조인)

ex> select stu_no, stu_name, stu_dept, enr_grade

      from student natural join enrol;

 

열의 이름이 같은 경우 2쌍 이상일경우 (join using)

ex> select stu_no, stu_name, stu_dept, enr_grade

      from student join enrol using(stu_no);

또 다른 표현법 (join on)

ex> select student.stu_no, stu_name, stu_dept, enr_grade

      from student join enrol on student.stu_no = enrol.stu_no;

  

 조인 조건 추가

ex> select student.stu_no, stu_name

      from student, enrol

      where student.stu_no=enrol.stu_no and sub_no =101;

 

 주의> 연산자 우선순위! (and 가 or 보다 우선함, 무조건 괄호를 이용)

          select student.stu_no, stu_name

          from student, enrol

          where student.stu_no = enrol.stu_no and sub_no=101 or sub_no=102; 

우선순위 규칙

• 괄호

• 숫자연산자

• 연결연산자

• 모든비교연산자

• IS [NOT] NULL, LIKE, [NOT] IN

• [NOT] BETWEEN

• NOT 논리연산자

• AND 논리연산자

• OR 논리연산자

EQUI 조인-3

수정표기 

          select student.stu_no, stu_name

          from student, enrol

          where student.stu_no = enrol.stu_no and (sub_no=101 or sub_no=102);

다른 표현법

 select stu_no, stu_name                               select stu_no, stu_name

 from student natural join enrol                      from student join enrol using(stu_no)

 where sub_no=101 or sub_no=102;                where sub_no=101 or sub_no =102;

 ex)

select student.stu_no, stu_name

from student join enrol on student.stu_no=enrol.stu_no

where sub_no =101 or sub_no=102;

 

Equi 조인에서 where 절에서 ‘=‘가 아닌 연산자(비교 연산자) 사용

 -마일리지 점수등 계산 또는 고객등급 산정을 위해 많이 사용

 

ex) select empno, ename, sal, grade

      from emp, salgrade

      where sal between losal and hisal;

 

Equi 조인에서 where 절에서 ‘=‘가 아닌 연산자(비교 연산자) 사용

 -마일리지 점수등 계산 또는 고객등급 산정을 위해 많이 사용

ex> select empno, ename, sal, grade

      from emp, salgrade

      where sal between losal and hisal;

 

자기 자신 테이블과 조인 (필요정보가 한 테이블에 다 들어있음)

 -이것도 많이 사용

Self 조인

ex> select a.empno as "사원번호", a.ename as "사원이름",

              b.empno as "상급자사원번호", b.ename as "상급자 이름"

              from emp a, emp b 

              where a.mgr = b.empno;

 

      같은 테이블에 이름이 두개 존재함으로 별칭을 사용하여 조인 구현 

      참고> 테이블 별칭은 as 와 쌍따움표 미 표기 해함.

Outer 조인 

 조인 조건을 만족하지 않는 행들도 질의 결과에 포함하기 위한 조인, 즉 2개 이상의 테이블이 조인될 때 어는 한쪽의 테이블에는 해당하는 데이터가 있으나 다음 테이블에는 없는 경우 그 데이터는 출력되지 않는 문제를 해결하기 위한 조인(Null 값을 포함한 검색)

ex> 

select a.*, sub_name     from enrol a, subject b      where a.sub_no=b.sub_no

order by 1;  ->수강정보 없는 109,110,111 과목의 정보 검색은 안됨

 

select a.*, sub_name     from enrol a right outer join subject b on a.sub_no=b.sub_no

order by 1; 

 

 OUTER 조인에는 right outer join, left outer join, full outer join 3가지

 

 - 주의> DB성능에 아주 나쁜 영향을 준다. 예를 들어 Outer join 을 사용해서 A테이블 데이터 전부 검색시 인덱스가 있어도 사용하지 않고 전체검색(full scan)하기 때문에

 해결방법: 설계시 외래키(foreign key)사용 등 

부질의(SubQuery)

 select 문내에 또 다른 select 문이 포함된 문장

 

옥성우 학생의 신장 보다 큰 학생의 학번,이름,신장 검색

1. select stu_height      from student        where stu_name='옥성우'                                  -> 결과 172

2.  select stu_no, stu_name,stu_height       from student      where stu_height > 172

 

ex> select stu_no, stu_name,stu_height

      from student

      where stu_height > (select stu_height     from student        where stu_name='옥성우')

 

ex> 조인을 이용해 작성

select a.stu_no, a.stu_name, a.stu_height

from student a, student b

where (a.stu_height > b.stu_height) and (b.stu_name ='옥성우‘);

 

SubQuery의 select는 일반적으로 where절, having절에 사용하며 from 절에도 사용한다.

 

SubQuery는 1.하나의 열값을 반환하는 단일열 부질의 2.여러 개의 열의 값을 반환하는 복수열 부질의 로 구분

 

SubQuery -단일열 부질의 1 

<하나의 열 값을 반환하는 부질의 두개로 나누어 생각해 보면>

 

SubQuery가 열의 한 값을 반환하는 경우 비교 연산자로 처리된다.

SubQuery가 여러 개의 반환값을 반환하는 경우 IN, ALL(연산자 사용), ANY(연산자 사용)등을 사용한다.

ex1> 박희철 학생과 같은 체중을 가지고 있는 학생의 정보

select *

from student

where stu_weight = (select stu_weight from student where stu_name='박희철');

 

ex2> 위의 문제에서 박희철이 포함되서 나오는데 박희철이 포함되지 않게 출력할려면?

select *

from student

where stu_weight = (select stu_weight from student where stu_name='박희철') and stu_name !='박희철 ;

 

ex3> 컴퓨터정보 학과와 같은 반인 다른 학과의 학생정보를 in을 이용하여 검색

select *          from student     

where stu_class  in ( select stu_class from student where stu_dept='컴퓨터정보') and stu_dept <> '컴퓨터정보';

 

ex4> 신장이 전체 학생들의 평균 신장보다 큰 학생을 검색

 select * from student where stu_height > (select avg(stu_height) from student);

 

SubQuery -복수열 부질의  

<여러 열 값을 반환하는 부질의 2개로 나누어 생각해 보면>

 

하나의 행을 반환하는 복수열 단일행 부질의 (비교연산자 '=' 사용)

여러 개의 행들을 반환하는 복수열 복수행 부질의 (in 사용)

 

ex1> 11번 사원과 급여, 커미션이 같은 사원의 정보를 구한다. (널 값 비교를 위해 NVL 함수 사용)

 select *    from emp_test   where (sal, nvl(comm,-1)) = (select sal, nvl(comm,-1) from emp_test where empno =11);

ex2> 여러행 복수처리 방법

        select *       from emp_test      

        where (sal,nvl(comm,-1))   in   (select sal, nvl(comm,-1)      from  emp_test         where deptno =30);

 

From 절에 subQuery 사용시 In-Line 뷰라고 한다.

 

학과별로 학과별 평균신장보다 큰 학생 정보 검색

select stu_dept, round(avg(stu_height),2) as avg_height

from student

group by stu_dept;

 

->이 결과를 테이블 처럼 생각하여 from 절에 사용하려면

select stu_no, stu_name, a.stu_dept, stu_height, avg_height

from student a, (select stu_dept, round(avg(stu_height),2) as avg_height 

                      from student       group by stu_dept) b

where a.stu_dept = b.stu_dept and stu_height > avg_height

 

(인라인 뷰 사용시 테이블 이름이 없음으로 별칭을 붙여서 사용한다.)