220415~18 데이터베이스 JOIN

오늘은 밀린 수업 정리하는 날로 정했다 하핫(귀, 귀찮...)

 

---

 

1. JOIN(조인)

두 개 이상의 테이블을 서로 연결하여 데이터를 검색할 때 사용하는 방법으로, 두 개의 테이블을 마치 하나의 테이블인 것처럼 보여주는 것이다.

 

1-1. 조인의 기본 사용 방법

• 두 개의 테이블에 하나라도 같은 컬럼이 있어야 한다.
• 두 컬럼의 값은 공유되어야 한다.
• 조인을 위해 보통 기본키(primary key)와 외래키(foreign key)를 활용한다.

 

1-2. Inner Join

• 각 테이블에서 조인 조건에 일치되는 데이터만 가져온다.
• Inner Join은 '교집합'이라고 말한다.
  

/* inner join 
 * locations 테이블에 city가 'Seattle'인 경우의
 * first_name, last_name, deparment_id, deparment_name, job_id, job_title, city
 */
SELECT 	e.FIRST_NAME , e.LAST_NAME , d.DEPARTMENT_ID , d.DEPARTMENT_NAME 
	, j.JOB_ID , j.JOB_TITLE , l.CITY 
FROM 	LOCATIONS l 
	INNER JOIN DEPARTMENTS d	ON l.LOCATION_ID = d.LOCATION_ID 
	INNER JOIN EMPLOYEES e 		ON d.DEPARTMENT_ID = e.DEPARTMENT_ID 
	INNER JOIN JOBS j 			ON e.JOB_ID = j.JOB_ID
WHERE 	l.CITY = 'Seattle'
;

 

1-3. Outer Join

• 조인 조건에 일치하는 데이터 및 일치하지 않는 데이터를 모두 select 한다.
• 조인 조건에 일치하는 데이터가 없다면 null로 가져온다.
• Outer Join은 Inner Join과 다르게 주(main) 테이블이 어떤 테이블인지가 중요하다. 그래서 어떤 테이블이 중심이 되느냐에 따라 다시 Left Outer Join, Right Outer Join, Full Outer Join으로 분류 할 수 있다.
• Left Outer Join은 왼쪽에 있는 테이블이, Right Outer Join은 오른쪽에 있는 테이블이, Full Outer Join은 양쪽 테이블 모두가 중심이라는 뜻이다.
• Left Outer Join을 주로 사용한다.

 

1-4. Left Outer Join

• 왼쪽 테이블이 기준이 된다.
• 조인 조건에 부합하는 데이터가 조인 당하는 테이블(오른쪽)에 있으면 해당 데이터를, 부재하면 null을 출력한다.
• SELECT * FROM SAWON a LEFT OUTER JOIN LICENSE b

-- left outer join
/*
 * employee_id, first_name, hire_date, start_date, end_date, job_id, department_id 출력하고,
 * employee_id 오름차순 출력
 */
SELECT	e.EMPLOYEE_ID , e.FIRST_NAME , e.HIRE_DATE , jh.START_DATE 
	, jh.END_DATE, jh.JOB_ID , e.DEPARTMENT_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN JOB_HISTORY jh ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID  
ORDER BY EMPLOYEE_ID 
;

 

1-5. Right Outer Join

• 오른쪽 테이블이 기준이 된다.
• 조인 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽)에 있으면 해당 데이터를, 부재하면 null을 출력한다.
• SELECT * FROM SAWON a RIGHT OUTER JOIN LICENSE b

-- right outer join
/*
 * employee_id, first_name, hire_date, start_date, end_date, job_id, department_id 출력하고,
 * employee_id 오름차순 출력
 */
SELECT	e.EMPLOYEE_ID , e.FIRST_NAME , e.HIRE_DATE , jh.START_DATE 
	, jh.END_DATE, jh.JOB_ID , e.DEPARTMENT_ID 
FROM 	EMPLOYEES e 
	RIGHT OUTER JOIN JOB_HISTORY jh ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID  
ORDER BY EMPLOYEE_ID 
;

 

1-6. Full Outer Join

• 양쪽 테이블 모두가 기준이 된다.
• 조인 조건에 부합하는 데이터가 조인 당하는 테이블(왼쪽 or 오른쪽)에 있으면 해당 데이터를, 부재하면 null을 출력한다.
• SELECT * FROM SAWON a FULL OUTER JOIN LICENSE b

-- full outer join
/*
 * employee_id, first_name, hire_date, start_date, end_date, job_id, department_id 출력하고,
 * employee_id 오름차순 출력
 */
SELECT	e.EMPLOYEE_ID , e.FIRST_NAME , e.HIRE_DATE , jh.START_DATE 
	, jh.END_DATE, jh.JOB_ID , e.DEPARTMENT_ID 
FROM 	EMPLOYEES e 
	FULL OUTER JOIN JOB_HISTORY jh ON e.EMPLOYEE_ID = jh.EMPLOYEE_ID  
ORDER BY EMPLOYEE_ID 
;

 

1-7. ANSI Join vs ORACLE Join

SQL은 데이터베이스를 관리하기 위해 만들어진 프로그래밍 언어이며, 데이터베이스를 관리해주는 대부분의 Management System(DBMS : 오라클, MySQL, Mariadb, ...)들은 SQL을 사용한다. 물론, DBMS 자체의 특수성 때문에 SQL의 사용법이 조금씩 다르기도 하지만, 큰 틀에서 보면 나름대로의 보편성을 가지고 있다.
이를 위해 미국 국립 표준 협회(American National Standards Institute, ANSI)에서도 SQL에 대한 보편적인 문법을 제시하고 있는데, 그것이 바로 ANSI Query이다. Join도 마찬가지로 ANSI문법을 사용하는 경우가 대부분이다.

 

 

 

2. 예제1

/*
 * 문제1.
 * 사원들의 이름, 부서번호, 부서명을 출력
 */
SELECT 	e.FIRST_NAME , e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	INNER JOIN DEPARTMENTS d	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID  
	-- LEFT OUTER JOIN DEPARTMENTS d2 	ON e.DEPARTMENT_ID = d2.department_id
;


/*
 * 문제2. 
 * 30번 부서의 사원들의 이름, 직업, 부서명 출력
 */
SELECT 	e.FIRST_NAME , j.JOB_TITLE , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e
	INNER JOIN DEPARTMENTS d 	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
	INNER JOIN JOBS j 			ON e.JOB_ID = j.JOB_ID 
WHERE 	e.DEPARTMENT_ID = 30
;


/*
 * 문제3.
 * 커미션을 받는 사원의 이름, 직업, 부서번호, 부서명
 */
SELECT 	e.FIRST_NAME , j.JOB_TITLE , d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM 	EMPLOYEES e 
	INNER JOIN DEPARTMENTS d 	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
	INNER JOIN JOBS j 			ON e.JOB_ID = j.JOB_ID 
WHERE 	e.COMMISSION_PCT IS NOT NULL 
;


/*
 * 문제4.
 * 지역번호(location_id) 2500에서 근무하는 사원의 이름, 직업, 부서번호, 부서명 출력
 */
SELECT 	e.FIRST_NAME , j.JOB_TITLE , d.DEPARTMENT_ID , d.DEPARTMENT_NAME
FROM 	LOCATIONS l 
	INNER JOIN DEPARTMENTS d 	ON l.LOCATION_ID = d.LOCATION_ID 
	INNER JOIN EMPLOYEES e 		ON d.DEPARTMENT_ID = e.DEPARTMENT_ID 
	INNER JOIN JOBS j 			ON e.JOB_ID = j.JOB_ID 
WHERE 	l.LOCATION_ID = 2500
;
-- 아 location_id라서 그냥 employee 테이블에서 하면 되었음... 수정할 것!



/*
 * 문제5.
 * 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력 
 */
SELECT 	e.FIRST_NAME , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	INNER JOIN DEPARTMENTS d 	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	e.FIRST_NAME LIKE '%A%'
;

 

 

 

3. 예제2

-- 1. 사원 이름과 그 사원의 관리자 이름을 출력하라.
SELECT 	e.FIRST_NAME 사원이름 , e2.FIRST_NAME 관리자이름 
FROM 	EMPLOYEES e , EMPLOYEES e2 
WHERE 	e.MANAGER_ID = e2.EMPLOYEE_ID 
;

-- 2. 사원이름과 부서명과 월급을 출력하는데, 월급이 3000 이상인 사원을 출력하라.
SELECT 	e.FIRST_NAME , d.DEPARTMENT_NAME , e.SALARY 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	SALARY >=3000
ORDER BY SALARY 
;

-- 3. 급여가 3000에서 5000 사이인 사원의 이름과 부서명을 출력하라.
SELECT 	e.FIRST_NAME , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e
	LEFT OUTER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	SALARY BETWEEN 3000 AND 5000
ORDER BY SALARY 
;

-- 4. 급여가 3000이하인 사원의 이름과 급여, 근무지를 출력하라.
SELECT 	e.FIRST_NAME , SALARY , l.CITY 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d	ON e.DEPARTMENT_ID	= d.DEPARTMENT_ID 
	LEFT OUTER JOIN LOCATIONS l		ON d.LOCATION_ID 	= l.LOCATION_ID 
WHERE 	SALARY <= 3000
ORDER BY SALARY 
;

-- 5. Steven King의 부서명을 출력하라.
SELECT 	e.FIRST_NAME || ' ' || e.LAST_NAME NAME, d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	FIRST_NAME	= 'Steven'
AND 	LAST_NAME	= 'King'
;

-- 6. IT부서에서 근무하고 있는 사람들의 이름, 부서명을 출력하라.
SELECT	e.FIRST_NAME , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	d.DEPARTMENT_NAME = 'IT'
;

-- 7. 업무가 SA_MAN인 사람의 사원번호, 이름, 급여, 부서명, 근무지(location_id)를 출력하라.
SELECT 	e.EMPLOYEE_ID , e.FIRST_NAME , e.SALARY , d.DEPARTMENT_ID , d.LOCATION_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	JOB_ID = 'SA_MAN'
;

-- 8. employees 테이블과 departments 테이블에서, 
--	  deparments 테이블 기준으로 사원번호, 이름, 업무(job_id), 부서번호, 부서명, 근무지를 출력하라.
SELECT 	e.EMPLOYEE_ID , e.FIRST_NAME , e.JOB_ID , d.DEPARTMENT_ID 
	, d.DEPARTMENT_NAME , l.CITY 
FROM 	EMPLOYEES e 
	RIGHT OUTER JOIN DEPARTMENTS d 	ON e.DEPARTMENT_ID 	= d.DEPARTMENT_ID 
	LEFT OUTER JOIN LOCATIONS l 	ON d.LOCATION_ID 	= l.LOCATION_ID  
;