KangKoon's Made ʕ•͡.̮•ʔ: SQL

SQL 사용법에 대해 알아보자!

7. 조인 : 등가 조인과 비 등가 조인
- RDB(관계형 데이터베이스 : Relational Database)는 데이터가 이상 현상을 보이지 않도록 정규화를 거친 여러 개의 테이블에 데이터를 지정, 분산된 데이터들은 테이블간의 관계(Relationship)를 통해 데이터베이스(DB)의 가장 중요한 요소인 무결성을 보장 받음, RDB의 R이 바로 릴레이셔널(Relational)로 테이블간의 관계를 의미, 보통 참조, 관계 등으로 불리는 이 속성은 RDB의 가장 핵심적인 요소. 이와 같이 RDB는 여러 테이블에 정보를 저장하기 때문에 필요한 데이터를 검색하기 위해서 대부분 여러 테이블의 데이터를 동시에 검색해야 함. 여러 테이블의 데이터를 검색하는 것을 조인(JOIN)이라고 하고 조인은 테이블간의 관계(Relational)를 근간으로 수행

> 조인의 개념
: 여러 테이블에 걸쳐서 일어나는 검색은 사실 RDB를 배우지 않은 누구라도 수행 할 수 있는 어찌 보면 당연한 정보 습득과정임, RDB에서는 이런 검색을 조인이라고 함, 조인은 사실 RDB에서 새롭게 도출된 개념이 아니라 이미 일상적으로 우리가 사회생활 속에서 당연시 되던 것을 정리 한 것에 지나지 않음.
조인을 쉽게 표현하고자 한다면 반드시 테이블간의 관계를 명확히 파악해야 하며, 테이블 간의 공통 컬럼이나 동일 내용을 담고 있는 컬럼이 무엇인지 알지 못한다면 당연히 조인 문장을 구사 할 수 없음.

등가 조인과 비 등가 조인은 조인 검색의 조건에 등호와 부등호를 이용한 검색을 의미, 즉 등가 조인은 두 테이블 간에 같은 값을 찾아서 조인하는 것으로 조인에서 가장 많이 사용되는 형태이며 비 등가 조인은 서로 다른 값을 부등호를 통해서 조인하는 것

> 등가 조인과 비 등가 조인

SQL>SELECT 테이블1.컬럼, ... 테이블2.컬럼, ...

2 FROM 테이블1, 테이블2, ....

3 WHERE 조인_조건

4 AND 일반_조건

* 조인 조건은 두 테이블간의 관계를 수식으로 표현한 것

* 조인 문장에서 기술된 모든 테이블은 반드시 조인 조건으로 기술되어야 함

* 동일한 이름의 컬럼이 여러 테이블에 존재하는 경우 테이블 이름은 반드시 써줘서 어떤 테이블의 컬럼인지 알려줘야 함

* 조인 조건은 SELECT 문에 사용된 테이블의 개수에 따라 달라지지만 테이블 개수가 n이라면 항상 조인 조건의 개수는 (n-1)개 이상

* 조인 조건에 '='를 이용하는 조인을 등가 조인(Equi-join)이라고 하고 부등호가 포함된 조인 조건을 이용하는 경우 비 등가 조인(Non equi-join)이라고 함

-예제1. 각 사원의 근무 부서를 검색

SQL>SELECT eno, ename, emp.dno, dept.dno, dname

2 FROM dept, emp

3 WHERE dept.dno = emp.dno;

사원의 정보와 부서에 대한 정보는 각각 emp(사원)와 dept(부서) table에 나누어 관리되므로 만일 이들 두 테이블의 정보를 서로 연관 지어 출력하고 싶다면 조인을 이용, 그리고 위와 같이 조인 조건에 등호(=)를 이용하는 조인을 등가 조인이라고 함

등가 조인은 보통 참조 관계를 통해서 이루어지는데 위의 경우 emp 테이블의 dno 컬럼이 됨, 예를 들어 사원번호가 0001번인 안영희 사원의 경우 부서 번호는 01번이고 01번 부서 번호는 부서 테이블을 검색하면 총무부가 됨, 즉 emp 테이블의 dno 컬럼의 값과 dept 테이블의 dno 컬럼의 값이 같은 경우에만 서로 연관지어 출력했는데, 이것을 조인 조건이라고 함. 위 SELECT 문에서 검색대상인 테이블의 개수가 2개 이므로 조인 조건은 반드시 (2-1)개 이상으로 한 개의 조인 조건을 이용

▲참조 관계

- 참조 관계란 참조되는 테이블의 컬럼 값에 존재하는 값만 입력 가능한 것으로 위 예제를 비추어 보면 emp 테이블의 dno 컬럼이 dept 테이블의 dno 컬럼을 참조함으로 emp.dno 컬럼에는 반드시 dept.dno 컬럼에 존재하는 값만이 입력되어야 함. 만일 dept.dno에 존재하지 않는 값이 emp.dno 컬럼에 존재한다면 사원 중 누군가는 회사에 존재하지 않는 부서 소속이라는 의미가 됨 이 처럼 이상 현상을 방지하기 위해 RDB는 외부키라는 기능을 제공해서 이를 미연에 방지함

-예제2.광주에서 근무하는 직원의 명단을 검색(부서번호와 부서명도 검색)

SQL>SELECT loc, d.dno, dname, eno, ename FROM dept d, emp e WHERE d.dno = e.dno AND loc = '광주';

FROM 절을 기술 할 때 테이블 명 각각에 별명을 붙일 수 있고, 별명을 붙이면 SQL문 전체에서 테이블 명 대신 별명을 사용해야 함

-예제3. 각 지원의 급여를 10% 인상한 경우 급여 등급을 검색

SQL>SELECT eno, ename, sal*1.1, grade

2 FROM emp, salgrade WHERE sal*1.1 BETWEEN losal AND hisal;

-예제4.조인 조건이 없는 잘못된 조인문장의 결과를 확인

SQL>SELECT d.dno, dname, e.dno, ename FROM dept d, emp e;

위 예제와 같이 둘 이상의 테이블을 대상으로 검색을 수행할 때 조인 조건을 누락하거나 잘못 기술하면, 검색에 사용된 테이블의 모든 행이 1:1로 대응된 잘못된 결과 값(Cartesian product)을 얻게 됨, dept 테이블의 7개 행과 emp 테이블의 16개 행이 모두 1:1로 대응된 결과, 출력된 행의 개수는 7*16 즉 112개가 됨, 아주 특별한 경우 교차 조인(cross join)이라는 이름으로 이와 같은 결과를 일부러 생성하는 경우가 있으나 보통은 잘못된 결과, 조인은 반드시 (테이블갯수 - 1)개 이상의 조인 조건이 필요

▲RDB가 조인을 수행하는 방법

- RDBMS가 조인을 수행하는 방법은 Nest Loop Join, Sort Merge Join, Hash Join, Star join등이 있음. 그러나 Nested Loop Join을 제외하면 비교적 최근에 추가된 조인 방법들임, 특히 Hash Join의 경우 오라클이 가장 선호하는 조인 방법으로 우수한 수행 성능과 안정성을 자랑, 이들 조인방법은 물리적으로 조인을 수행하는 방법에 대한 정의로써 수행성능에 큰 영향을 끼침

<문제>

1. 송강 교수의 과목을 수강하는 학생의 기말고사 점수를 성적 순서로 검색

SQL>SELECT pname, cname, s.cno, result

2 FROM professor p, course c, score s

3 WHERE pname = '송강' AND p.pno = c.pno AND c.cno = s.cno

4 ORDER BY result;

2. 화학과 1학년 학생의 기말고사 성적을 학점(A,B,C,D,E,F)으로 검색

SQL>SELECT s.sname, s.major, s.syear, sc.grade
FROM student s, score ss, scgrade sc

WHERE s.sno = ss.sno AND ss.result BETWEEN sc.loscore AND sc.hiscore AND major = '화학' AND syear =1;

3. 송강 교수가 강의하는 과목에서 평가 점수가 A인 학생의 명단을 과목명과 함께 검색

SQL>select p.pname 교수명, co.cname 과목명, s.sname 성명, grade 평점

2 from student s, professor p, course co, score ss, scgrade sc

3 where p.pname IN ('송강') and

4 sc.grade IN ('A') and

5 p.pno = co.pno and

6 ss.sno = s.sno and

7 co.cno = ss.cno and

8 result between sc.loscore and sc.hiscore;

4. 화학과 1학년 학생에게 강의하는 교수의 명단을 검색
SQL>select s.major 과목, s.syear 학년, s.sname 성명, p.pname 교수명, co.cname 과목명
2 from student s, professor p, course co, score sc
3 where s.major in ('화학') and s.syear in ('1')
4 and p.pno = co.pno and sc.sno = s.sno and sc.cno = co.cno;

5. 양선호의 입사일보다 빨리 부임한 교수의 명단을 검색

SQL>select p.pname , p.hiredate
2 from professor p, emp e
3 where ename IN ('양선호') and
4 e.hdate > p.hiredate;

8. 조인 : 자기 참조 조인과 외부 조인
- 조인은 보통 서로 다른 테이블 간에 연관된 데이터를 검색하는 것이지만 특별한 경우 자기 참조에 의해서 동일 테이블을 마치 두 개인의 테이블인 것처럼 조인하는 경우도 있음. 그리고 조인문을 쓸 때 조인에 의해서 검색된 결과가 비록 틀린 결과가 아니라 할지라도 이를 보는 사람으로 하여금 오해를 할 가능성이 있음.

> 조인 문을 만든 과정
- 조인 문을 위한 사전 준비
: 조인 문을 만들기 위해서는 반드시 테이블간의 관계가 명확히 그려져 있어야 함 어떤 테이블이 어떤 테이블과 공통 컬럼이 있는 가 등의 내용이 명확하지 않다면 조인 문을 만드는 것은 절대 불가능

* 조인 문을 생성하는 과정
1. 지문에서 검색 대상과 조건을 구분
- 이를 통해 일단 SELECT 절, WHERE 절, ORDER BY 절을 구성
- SELECT 절은 6번에서 작성자의 의도에 따라 추가 될 수 있음
2. SELECT 절과 WHERE 절의 내용에 따라 정보를 검색할 테이블을 찾음
3. 테이블간의 관계를 확인
- 관계를 확인하는 단계에서 테이블이 추가 될 수 있음
4. 조인 조건을 기술

5. 전체 문장을 다듬음

> 자기 참조 조인(Self Join)
SQL>SELECT 별명1.컬럼1, .... 별명2.컬럼2,...
2 FROM 테이블 별명1, 테이블 별명2, ...
3 WHERE 조인_조건
4 AND 일반_조건
* 동일 테이블을 자기 참조에 의해 조인
* 별명1과 별명2는 동일 테이블에 대한 별명이지만 각각 별도의 테이블처럼 사용
* 하나의 테이블에 별명을 다르게 해서 마치 여러 개의 테이블 간 조인하는 것처럼 문장을 구현
* 서로 다른 별명을 별개의 테이블로 가정하면 보통의 등가조인이나 비 등가 조인과 동일함
* 테이블에 반드시 별명을 붙여야 됨으로 별명을 이용한 조인이라 부르기도 함

-예제2.각 사원을 관리하는 사수의 이름을 검색
SQL>SELECT e1.eno, e1.ename, e1.mgr, e2.eno, e2.ename
2 FROM emp e1, emp e2
3 WHERE e1.mgr = e2.eno;

Emp 테이블의 mgr 컬럼은 자신을 관리하는 사원의 사번이므로 같은 테이블의 eno 컬럼을 참조 테이블에는 dno와 같이 외부의 컬럼을 참조하는 컬럼 뿐 아니라 내부의 컬럼을 참조하는 컬럼도 존재할 수 있음. 동일 테이블의 다른 컬럼을 참조하는 컬럼을 이용해서 동일 테이블 간 조인하는 것을 자기 참조 조인(Self join)이라 함
위에 보는 바와 같이 자기 참조 조인은 하나의 테이블에 여러 별명을 붙여서 마치 서로 다른 테이블인 것처럼 조인하는 방법임. 그러므로 이를 작성하는 사람도 이를 두 개의 테이블로 인식하면 일반적인 조인과 다를 바가 없음.

> 외부 조인
SQL>SELECT 테이블1.컬럼, ... 테이블2.컬럼, ...
2 FROM 테이블1, 테이블2, ..
3 WHERE 조인_조건(+)
4 AND 일반_조건

* 조인 조건에 일치하는 않는 데이터를 출력해 줌
* 조인 결과물을 해석할 때 오해를 방지하기 위한 방법
* '+' 기호는 데이터가 부족한 쪽에 기술

-예제3. 각 부서별로 사원을 검색함 (외부조인과 일반조인 결과물의 비교)
SQL>SELECT d.dno, dname, ename
2 FROM dept d, emp e
3 WHERE d.dno = e.dno
4 ORDER BY 1;

SQL>SELECT d.dno, dname, ename
2 FROM dept d, emp e
3 WHERE d.dno = e.dno(+)
4 ORDER BY 1;

외부 조인(Outer join)의 가장 중요한 의미는 출력된 데이터의 신뢰성을 제공해 주는 것
SQL문의 결과는 논리적으로 틀리지 않았지만, 해석상의 오류로 인해 이를 읽는 사람이 오해 할 가능성이 충분히 존재하며, 이러한 오해는 결론적으로 부정확한 트랜잭션의 시작이 될 수 있음.
해석상의 오해를 방지하기 위해 비록 조인 조건에 부합하지 않더라도 이를 출력함으로써 검색 결과의 신뢰성을 높이는 방법이 외부조임임. 단 외부조인에 (+) 기호는 조인 조건 양쪽에 모두 기술 할 수 없음.

<문제>
1. 직원 중에 자신의 관리자 보다 급여가 높은 사람의 급여 정보를 관리자 급여 정보와 같이 검색
SQL>select e1.eno, e1.ename, e1.mgr, e1.sal, e2.eno, e2.ename, e2.sal
2 from emp e1, emp e2
3 where e1.mgr = e2.eno and e1.sal > e2.sal ;