[Web_DBMS] 10

● JOIN

- 여러 테이블에 흩어져 있는 정보 중 사용자가 필요한 정보만 가져와서 가상의 테이블처럼 만들고 결과를 보여주는 것

- 정규화를 통해 조회 테이블이 너무 많이 쪼개져 있으면 작업이 불편하기 때문에

  입력, 수정, 삭제의 성능을 향상시키기 위해서 JOIN을 통해 합친 후 사용한다.

 

1. 내부 조인(INNER JOIN)

- 조건이 일치하는 값만 합쳐서 조회

- 선행 JOIN 후행(자료가 많은 쪽을 후행으로 정한다.)

테이블명A INNER JOIN 테이블명B

ON 조건식

 

※ INNER는 생략 가능하다.

테이블명A JOIN 테이블명B

ON 조건식

 

(1) 등가 조인

- ON 절에 등호가 있을 때

 

(2) 비등가 조인

- ON 절에 등호가 없을 때

 

 

 

● SQL 실행 순서

FROM > ON > JOIN > WHERE > GROUP BY > HAVING > SELECT > ORDER BY

 

- ON 절의 조건은 JOIN이 되면서 실행된다.

- WHERE 절의 조건은 JOIN이 모두 끝나고 나서 실행된다.

- ON과 WHERE를 같이 사용할 때와, ON만 사용할 때의 결과가 같다면 ON만 사용하는 것이 좋다.

 

 

 

※ 자동 형변환

- DATE 타입을 문자열과 비교할 땐 자동 형변환이 가능하다.

- 대신 문자열 형식은 'YYYYMMDD'로 작성해야 한다.

 

 

 

---

 

실습(JOIN)

1. EMP 테이블 사원번호로 DEPT 테이블의 지역 검색

SELECT EMPNO, ENAME, DNAME, LOC FROM DEPT JOIN EMP
ON EMP.DEPTNO = DEPT.DEPTNO;

 

 

 

2. PLAYER 테이블에서 송종국 선수가 속한 팀의 전화번호 검색하기

※ 선행 테이블 정하기(개수가 적은 테이블을 선행 테이블로 정한다.)

SELECT COUNT(TEAM_ID) FROM PLAYER;  /*480*/
SELECT COUNT(TEAM_ID) FROM TEAM;  /*15*/

 

 

- ON, WHERE 사용

SELECT TEL FROM TEAM T JOIN PLAYER P
ON T.TEAM_ID = P.TEAM_ID
WHERE PLAYER_NAME = '송종국';

 

 

- ON 사용

SELECT TEL FROM TEAM T JOIN PLAYER P
ON T.TEAM_ID = P.TEAM_ID AND PLAYER_NAME = '송종국';

 

 

 

3. JOBS 테이블에서 JOB_ID로 직원들의 JOB_TITLE, EMAIL, 성, 이름 검색

SELECT JOB_TITLE, EMAIL, LAST_NAME || ' ' || FIRST_NAME AS NAME
FROM JOBS J JOIN EMPLOYEES E
ON J.JOB_ID = E.JOB_ID;

 

 

 

4. EMP 테이블의 SAL를 SALGRADE 등급으로 나누기

SELECT GRADE, EMPNO, ENAME, SAL FROM SALGRADE S JOIN EMP E
ON E.SAL BETWEEN S.LOSAL AND S.HISAL
ORDER BY GRADE DESC;

 

 

5. EMPLOYEES 테이블에서 HIREDATE가 2003~2005년까지인 사원의 정보와 부서명 검색

- TO_DATE 사용

SELECT * FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND HIRE_DATE BETWEEN TO_DATE('2003-01-01', 'YYYY-MM-DD') AND TO_DATE('2005-12-31', 'YYYY-MM-DD');

 

 

- TO_DATE 미사용(해당 일자까지 포함)

SELECT * FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
AND HIRE_DATE BETWEEN '20030101' AND '20051231';

 

 

- TO_DATE 미사용(해당 월 이전까지 포함)

SELECT * FROM DEPARTMENTS D JOIN EMPLOYEES E
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID 
AND HIRE_DATE BETWEEN TO_DATE('2003-01', 'YYYY-MM') AND TO_DATE('2005-12', 'YYYY-MM');

 

 

 

6. JOB_TITLE 중 'Manager'라는 문자열이 포함된 직업들의 평균 연봉을 JOB_TITLE별로 검색

SELECT JOB_TITLE, AVG(SALARY) FROM JOBS J JOIN EMPLOYEES E
ON J.JOB_ID = E.JOB_ID AND JOB_TITLE LIKE '%Manager%'
GROUP BY JOB_TITLE;

 

 

 

7. EMP 테이블에서 ENAME에 L이 있는 사원들의 DNAME과 LOC 검색

SELECT ENAME, DNAME, LOC FROM DEPT D JOIN EMP E
ON D.DEPTNO = E.DEPTNO AND ENAME LIKE '%L%';

 

 

 

8. 축구 선수들 중에서 각 팀 별로 키가 가장 큰 선수들 검색

- JOIN 사용

SELECT P1.TEAM_ID, PLAYER_NAME, HEIGHT, WEIGHT, BIRTH_DATE
FROM
	(
		SELECT TEAM_ID, MAX(HEIGHT) M FROM PLAYER
		GROUP BY TEAM_ID  /*팀 별로 가장 큰 키가 들어있는 테이블*/
	) P1 
JOIN PLAYER P2 /*각 선수들의 전체 정보가 들어있는 테이블*/
ON P1.TEAM_ID = P2.TEAM_ID AND P1.M = P2.HEIGHT
ORDER BY 1;

 

 

- JOIN 미사용

/*FROM 절에서 2개 이상의 테이블을 작성할 때 A, B와 같이 ,로 구분할 수 있다.*/
/*(A, B) IN ((C, D)) -> A = C AND B = D*/
/*P2 IN P1*/
SELECT * FROM PLAYER
WHERE (TEAM_ID, HEIGHT) IN (SELECT TEAM_ID, MAX(HEIGHT) FROM PLAYER GROUP BY TEAM_ID)
ORDER BY 3;

 

 

 

9. EMP 테이블에서 각 사원의 매니저 이름 조회

/*E1.MGR가 E2.EMPNO와 일치하면 E1은 E2의 매니저다.*/
/*EMP 테이블에서 MGR가 NULL이 아니면 매니저다.*/
SELECT E1.ENAME 사원, E2.ENAME 매니저 FROM EMP E1 JOIN EMP E2
ON E1.MGR = E2.EMPNO;