*JOIN
--[JOIN]
--; 관계형 데이터베이스 시스템
-- (Oracle, MySQL(Maria), SQL-Server(MS-SQL)...)
-- 테이블-테이블 연관 관계를 맺고 있다
-- ex) emp-dno : 사원이 어느 부서에 소속되어 있는지
-- dept-dno : 사원번호
-- 김연아 02 - 02 회계부서
--자료형이 같아야 join을 걸 수 있다
--1) 각 사원의 근무 부서를 검색한다
SELECT dno 부서번호, eno 사원번호, ename 이름
FROM emp;
--등가 조인 : 같은 값을 연결시켰다.
--; 두 테이블의 공통 컬럼을 = 연산자로 조건을 부여한 것
SELECT eno 사번, ename 이름, emp.dno 부서번호,
dept.dno 부서번호, dname 부서명
FROM emp, dept
WHERE emp.dno = dept.dno;
--2) 광주에서 근무하는 직원의 명단을 검색하세요
-- (부서번호와 부서명도 검색하세요)
SELECT loc 위치, dept.dno 부서번호, dname 부서명,
eno 사번, ename 사원명
FROM emp, dept
WHERE emp.dno=dept.dno
AND loc='광주';
--한 테이블에밖에 존재하지 않는 필드에는 굳이 emp.eno 처럼 emp를 붙여주지 않아도 된다
--다만 dno는 두군데 다 있기 때문에 어디서 가져다 쓸 것인지 밝혀줘야한다.
SELECT loc 위치, d.dno 부서번호, dname 부서명,
eno 사번, ename 사원명
FROM emp e, dept d
WHERE e.dno=d.dno
AND loc='광주';
--비등가 조인 (특수한 경우-범위를 나타낼 때-딱 매칭되는 컬럼은 없지만 어떤 컬럼이 어떤 컬럼의 범위에 속할 때)
--3) 각 직원의 급여를 10%인상한 경우 급여 등급을 검색한다
SELECT eno 사번, ename 이름,
sal*1.1 "인상된 급여", grade 등급
FROM emp, salgrade
WHERE sal*1.1 BETWEEN losal AND hisal;
--4) 잘못된 조인( CROSS JOIN)
-- 조인 조건이 없으면 Cross Join 이라고 하며
-- 테이블들의 모든 행이 1:1 교차하게 된다.
--결과에 의미가 없다. (그냥 모든 행 연결한 경우의 수임)
SELECT d.dno, dname, e.dno, ename
FROM dept d, emp e;
내용을 입력하세요.
*외부 조인
--[Self Join - 자기 참조 조인] -- 자기 테이블 안에서 정보를 찾는다
--; emp 사원을 관리하는 관리자
-- ex) 김연아 사원의 관리자(MGR 사수) - 0301 이승철
--1) 각 사원을 관리하는 관리자(사수)의 이름을 검색하세요.
--e1 = 사원테이블, e2 = 관리자 테이블로 본다.
SELECT e1.eno 사번, e1.ename 사원명, e1.mgr 관리자사번,
e2.eno "관리자 사번", e2.ename 관리자명
FROM emp e1, emp e2
WHERE e1.mgr=e2.eno; --사원의 관리자가 관리자테이블의 누구니?
--[외부조인]
--2) 각 부서별로 사원을 검색한다
-- 일반 조인
SELECT d.dno 부서번호, dname 부서명, ename 사원명
FROM dept d, emp e
WHERE d.dno=e.dno --일치하는 것만 출력한다
ORDER BY 1;
-- 외부 조인
--ex) 부서는 존재하지만 소속 부서원이 없는경우
-- 일반 조인을 사용하면 부서가 나타나지 않는다.
-- 이 때 외부조인을 사용하면 소속부서원이 없는 부서도 표시되게 된다.
--(+)는 데이터가 부족한 컬럼에 적어야 한다.
-- 의 의미는 빈 공간(null)이라도 추가해라.
-- ; 외부 조인의 가장 중요한 의미는 출력 데이터의 신뢰성 제공이다.
--양쪽의 데이터가 일치하지 않을 때 외부 조인을 통해 모두 표현하므로
--업무상 신뢰를 높일 수 있다.
SELECT d.dno 부서번호, dname 부서명, ename 사원명
FROM dept d, emp e
WHERE d.dno=e.dno(+) -- emp의 dno가 부족한 컬럼, 부족한 쪽에 null값 나와도 표시해줘!
ORDER BY 1;
내용을 입력하세요.
*NATURAL JOIN / USING
--[Join을 표현하는 여러가지 방법]
--[Natural Join - 자연 조인]
--; 컬럼 이름과 자료형이 일치하는 것을 찾아서 Join 해준다
--1) 자연 조인으로 각 사원의 근무 부서를 검색하세요
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
NATURAL JOIN dept;
--2) 광주에서 근무하는 직원의 명단을 검색하세요
-- (부서 번호와 부서명도 검색하세요)
SELECT loc 근무처, dno 부서번호, dname 부서명,
eno 사번, ename 이름
FROM dept
NATURAL JOIN emp
WHERE loc='광주';
SELECT loc 근무처, dno 부서번호, dname 부서명,
eno 사번, ename 이름
FROM emp
NATURAL JOIN dept
WHERE loc='광주';
-- 둘 다 같은 결과
--[Using 절을 이용한 Join]
--; NATURAL JOIN은 공통 컬럼이 1개만 존재할 때 사용할 수 있다.
-- 둘 이상인 경우는 사용할 수 없다.
-- 이 때 등가 조인(=)이나 Using 절을 이용한 Join을 사용해야 한다.
--3) Using절 조인으로 각 사원의 근무 부서를 검색하세요
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
JOIN dept USING (dno);
--4) 광주에서 근무하는 직원의 명단을 검색하세요
-- (부서 번호와 부서명도 검색하세요)
SELECT loc 근무처, dno 부서번호, dname 부서명,
eno 사번, ename 이름
FROM dept
JOIN emp USING (dno) --using 절을 써서 공통 컬럼 지정하여 검색할 수도 있다.
WHERE loc='광주';
--5) 화학과 1학년 학생들의 유기화학 점수를 검색한다
--화학과 1학년 학생 : student
--유기화학 : course
--점수 : score
--student.sno = score.sno 이렇게 조인 되는 것이다.
--score.cno = course.cno
SELECT sno 학번, sname 이름, cname 과목명, result 점수
FROM student
JOIN score USING (sno)
JOIN course USING (cno)
WHERE major= '화학'
AND syear=1
AND cname='유기화학';
--등가조인 이용
SELECT s.sno 학번, sname 이름, cname 과목명, result 점수
FROM student s, score r , course c
WHERE s.sno=r.sno AND r.cno=c.cno
AND major= '화학'
AND syear=1
AND cname='유기화학';
--내츄럴 조인 이용 : : 알아서 공통칼럼 찾아줌
SELECT sno 학번, sname 이름, cname 과목명, result 점수
FROM student
NATURAL JOIN score
NATURAL JOIN course
WHERE major= '화학'
AND syear=1
AND cname='유기화학';
내용을 입력하세요.
*ON절과 좌우 외부조인
--[ON]절
--가독성이 우수하다
--(조인 조건을 명시적으로 표현)
--등가 조인과 비등가 조인을 모두 표현할 수 있다.
-- 1) 각 사원의 근무부서를 검색하세요
-- 등가조인
SELECT eno 사번, ename 이름, dept.dno 부서번호, dname 부서명
FROM emp
JOIN dept ON emp.dno=dept.dno;
--2) 개발 업무를 담당하는 사원의 급여 등급을 검색하세요
-- 비등가 조인
SELECT eno 사번, ename 이름, job 업무, grade 급여등급
FROM emp
JOIN salgrade ON sal BETWEEN losal AND hisal
WHERE job='개발';
--아래처럼 조인과 관련없는 job='개발'같은 일반 조건을
--ON절에 쓰는 것은 안하는 것이 좋다.
-- why? 일반 조건과 조인 조건의 기준이 모호해지기 때문에
--그래서 위처럼 비등가조인은 ON절에,
--일반조건은 WHERE절에 주는 것이 가독성 면에서 좋다.
SELECT eno 사번, ename 이름, job 업무, grade 급여등급
FROM emp
JOIN salgrade ON sal BETWEEN losal AND hisal
AND job='개발';
--3) 직원의 부서명과 급여등급을 검색하세요
--원칙을 정하는 것이 좋다. 이런식으로 원칙을 정해놓으면 가독성이 좋아진다.
--ex) 등가 조인은 USING 절을,
--비등가 조인은 ON절을 사용한다.
SELECT eno 사번, ename 이름, dname 부서명, grade 등급
FROM emp
JOIN dept USING (dno)
JOIN salgrade ON sal BETWEEN losal AND hisal;
--4) 직원의 이름과 담당 관리자 이름을 검색하세요
-- 자기참조 조인 : ON절로 표현
SELECT e1.eno, e1.ename 사원, e2.eno, e2.ename 관리자
FROM emp e1
JOIN emp e2 ON e1.mgr=e2.eno;
-- [좌우 외부 조인(Left Right Outer Join)]
-- (+) 기호로 하는 외부조인은 둘 중에 한쪽에만 사용 가능
-- 그러나 여기서는 마치 양쪽에 (+)를 한 것 같은 표현이 가능하다.
-- a. (+) : POS 부서는 존재, POS의 부서원이 없을 때
-- dept.dno = emp.dno(+)
-- b. 부서원이 없는 POS 부서 존재
-- 홍길동 신입사원의 부서가 배정되기 전
-- 이럴 경우는 FULL JOIN을 사용해야 한다.
--5) 홍길동 사원을 추가해보자
INSERT INTO emp(eno, ename, job)
VALUES ('3006','홍길동','설계');
COMMIT; --확정
SELECT * FROM emp; --홍길동이 추가된 것 확인 가능
--6) 사원 이름과 소속 부서를 검색하세요
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
RIGHT JOIN dept USING (dno); -- right join = dept에 기준 맞춰라. 그러므로 pos 나옴
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
LEFT JOIN dept USING (dno); --왼쪽은 기준에 안맞아도 다 나와라
SELECT eno 사번, ename 이름, dno 부서번호, dname 부서명
FROM emp
FULL JOIN dept USING (dno); --둘 다 매칭 안돼도 다 나와라
