DBMS 4일차

전영덕·2023년 2월 13일

DBMS

목록 보기

4/7

Join 조인

1. Join 조인이란?

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

2. Join의 기본 사용 방법

두개의 테이블에 하나라도 같은 컬럼이 있어야
한다.
두 컬럼의 값은 공유되어야 한다.
보통 종인을 위해 기본키(Primary key)와
외래키(Foreign Key)를 활용한다.
앞으로 예시로 들을 테이블

3. inner join

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

select *
from table_A t1
	inner join table_B t2
;

4. 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은 양쪽 테이블이 모두가 중심(주)
이라는 뜻이다.

5. Left Outer Join

왼쪽 테이블이 기준이 된다.
조인 조건에 부합하는 데이터가 조인 당하는
테이블(오른쪽)에 있으면 해당 데이터를, 부재하면
NULL로 select된다.

select * from table_A t1
left outer join table_B t2

6. Right Outer Join

오른쪽 테이블이 기준이 된다.
조인 조건에 부합하는 데이터가 조인 당하는
테이블(왼쪽)에 있으면 해당 데이터를, 부재하면
NULL로 select된다.

select * from table_A t1
right outer join table_B t2

7. full outer join

양쪽 테이블이 기준이 된다.
조인 조건에 부합하는 데이터가 조인 당하는
테이블(왼쪽 or 오른쪽)에 있으면 해당 데이터를,
부재하면 NULL로 select된다.

select * from table_A t1
full outer join table_B t2

8. ANSI Join vs Oracle Join

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

2. 서브쿼리(subQuery)

1. 서브쿼리란?

Main Query에 반대되는 개념으로 이름을 붙인것
메인쿼리를 구성하는 소단위 쿼리
select, insert, delete, update 절에서 모두 사용 가능
서브쿼리의 결과 집합을 메인 쿼리가 중간 결과 값으로 사용
서브쿼리 자체는 일반 쿼리와 다를 바가 없다.

3. 실전 연습

SELECT 	*
FROM 	EMPLOYEES e 
;

SELECT 	* 
FROM 	DEPARTMENTS d  
;

SELECT 	e.EMPLOYEE_ID , e.DEPARTMENT_ID , d.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e INNER JOIN DEPARTMENTS d ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE  	DEPARTMENT_ID = 178 ;
;

--left outer join
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , d.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
;

--right outer join
SELECT e.EMPLOYEE_ID , e.FIRST_NAME , d.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	right OUTER JOIN DEPARTMENTS d
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
;

SELECT 	*
FROM JOB_HISTORY jh 
ORDER BY EMPLOYEE_ID
;

--우리회사에 있는 사원이름, 사원번호
--부서이동 정보(start_data, end_data, job_id)


SELECT 	e.LAST_NAME , e.EMPLOYEE_ID ,jh.START_DATE , jh.END_DATE , jh.JOB_ID 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN JOB_HISTORY jh 
		ON jh.DEPARTMENT_ID = jh.DEPARTMENT_ID
;

Join 예제


-- 문제2) 30번 부서의 사원들의 이름,직업,부서명을 출력하라

-- 문제3) 커미션을 받는 사원의 이름, 직업, 
-- 부서번호,부서명을 출력하라

-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 
-- 직업,부서번호,부서명을 출력하라

-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라

-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라

-- 문제7) 사원이름과 부서명과 월급을 출력하는데 
-- 월급이 3000 이상인 사원을 출력하라

-- 문제8) 급여가 3000에서 5000사이인 
-- 사원의 이름과 소속부서명 출력하라

-- 문제9) 급여가 3000이하인 사원의 이름과 급여, 
-- 근무지를 출력하라

-- 문제10) Steven King의 부서명을 출력하라.

-- 문제11) IT부서에서 근무하고 있는 사람들을 출력하라.

-- 문제12) EMPLOYEES 테이블에서 사원번호,이름,업무, 
-- EMPLOYEES 테이블의 부서번호, 
-- DEPARTMENTS 테이블의 부서번호,부서명,근무지를 출력하여라

-- 문제13) EMPLOYEES 테이블과 DEPARTMENTS 테이블의 
-- 부서번호를 조인하고 
-- SA_MAN 사원만의 사원번호,이름,급여,부서명,근무지를 출력하라.

-- 문제14) EMPLOYEES 테이블과 DEPARTMENTS 테이블에서 
-- DEPARTMENTS 테이블 기준으로 사원번호,이름,업무, 
-- 부서번호,부서명,근무지를 
-- EMPLOYEES 테이블의 부서번호 출력하여라

join 예제 답안

SELECT 	* FROM 	DEPARTMENTS d;
-- 문제1) 사원들의 이름, 부서번호, 부서명을 출력하라
SELECT 	e.LAST_NAME , e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e. DEPARTMENT_ID  = d.DEPARTMENT_ID 
;
-- 문제2) 30번 부서의 사원들의 이름,직업,부서명을 출력하라
SELECT 	e.LAST_NAME , e.JOB_ID , d.DEPARTMENT_ID 
FROM 	EMPLOYEES e 
	INNER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
WHERE d.DEPARTMENT_ID  = 30	
;
-- 문제3) 커미션을 받는 사원의 이름, 직업, 부서번호,부서명을 출력하라
SELECT 	e.LAST_NAME , e.JOB_ID , e.DEPARTMENT_ID , d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID 
WHERE e.COMMISSION_PCT IS NOT NULL 
;
-- 문제4) 지역번호 2500 에서 근무하는 사원의 이름, 직업, 부서번호, 부서명을 출력하라
SELECT	e.LAST_NAME , e.JOB_ID , e.DEPARTMENT_ID ,d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d  
	ON e.DEPARTMENT_ID  = d. DEPARTMENT_ID 
WHERE d.LOCATION_ID = 2500
;
-- 문제5) 이름에 A가 들어가는 사원들의 이름과 부서이름을 출력하라
SELECT 	e.FIRST_NAME , d.DEPARTMENT_NAME  
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID  = d.DEPARTMENT_ID
WHERE e.FIRST_NAME  LIKE '%A%'
;
-- 문제6) 사원이름과 그 사원의 관리자 이름을 출력하라
-- 아이거 못풀었다. 이거 강사님 답안 복붙한건데 뭔가이상하다.
SELECT 	e.EMPLOYEE_ID	,e.FIRST_NAME , e2.FIRST_NAME	, e.MANAGER_ID 
FROM 	EMPLOYEES e 
	INNER JOIN EMPLOYEES e2 
	ON e.EMPLOYEE_ID = e2.MANAGER_ID 
;
-- 문제7) 사원이름과 부서명과 월급을 출력하는데, 월급이 3000 이상인 사원을 출력하라
SELECT 	e.LAST_NAME , d.DEPARTMENT_NAME , e.SALARY
FROM 	EMPLOYEES e
	LEFT OUTER JOIN DEPARTMENTS d
	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID  
WHERE 	SALARY >= 3000
;
-- 문제8) 급여가 3000에서 5000사이인 사원의 이름과 소속부서명 출력하라
SELECT 	e.LAST_NAME , d.DEPARTMENT_NAME , e.SALARY
FROM 	EMPLOYEES e
	LEFT OUTER JOIN DEPARTMENTS d
	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID  
WHERE 	e.SALARY BETWEEN 3000 AND 5000
;
-- 문제9) 급여가 3000이하인 사원의 이름과 급여,근무지를 출력하라
SELECT 	e.LAST_NAME , e.SALARY, d.LOCATION_ID , 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

-- 문제10) Steven King의 부서명을 출력하라.
SELECT 	FIRST_NAME , LAST_NAME,  d.DEPARTMENT_NAME 
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE e.LAST_NAME = 'King'
	AND e.FIRST_NAME  = 'Steven'
;
-- 문제11) IT부서에서 근무하고 있는 사람들을 출력하라.
SELECT 	*
FROM 	EMPLOYEES e 
	LEFT OUTER JOIN DEPARTMENTS d 
	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID 
WHERE 	e.DEPARTMENT_ID = 60
;
-- 문제12) EMPLOYEES 테이블에서 사원번호,이름,업무,EMPLOYEES 테이블의 부서번호,DEPARTMENTS 테이블의 부서번호,부서명,근무지를 출력하여라
SELECT 	e.EMPLOYEE_ID , e.LAST_NAME , e.JOB_ID , e.DEPARTMENT_ID , d.DEPARTMENT_ID, d.DEPARTMENT_NAME , l.STREET_ADDRESS 
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 
;
-- 문제13) EMPLOYEES 테이블과 DEPARTMENTS 테이블의 부서번호를 조인하고 
-- SA_MAN 사원만의 사원번호,이름,급여,부서명,근무지를 출력하라.
SELECT 	e.EMPLOYEE_ID ,e.FIRST_NAME , e.LAST_NAME , e.SALARY , d.DEPARTMENT_NAME ,  l.STREET_ADDRESS 
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 e.JOB_ID  = 'SA_MAN'	
;
-- 문제14) EMPLOYEES 테이블과 DEPARTMENTS 테이블에서 
-- DEPARTMENTS 테이블 기준으로 사원번호,이름,업무, 
-- 부서번호,부서명,근무지를 
-- EMPLOYEES 테이블의 부서번호 출력하여라

SELECT 	e.EMPLOYEE_ID , e.LAST_NAME , e.JOB_ID , d.DEPARTMENT_ID , d.DEPARTMENT_NAME , d.LOCATION_ID
FROM 	DEPARTMENTS d
	LEFT OUTER JOIN EMPLOYEES e 
	ON e.DEPARTMENT_ID = d.DEPARTMENT_ID
ORDER BY e.DEPARTMENT_ID 	
;

SubQuery

SELECT 	ROUND( AVG(SALARY) )
FROM 	EMPLOYEES e 
;
SELECT  *
FROM 	EMPLOYEES e 
WHERE SALARY < 6462
;
	-----------------------------------------
SELECT  *
FROM 	EMPLOYEES e 
WHERE SALARY < (SELECT 	ROUND( AVG(SALARY) ) 
				FROM 	EMPLOYEES e)
;
	-----------------------------------------
SELECT 	LOCATION_ID 
FROM 	LOCATIONS l 
WHERE 	COUNTRY_ID = 'US'
;
--위에 값이 1400, 1500, 1600, 1700 이 나옴
SELECT 	*
FROM 	DEPARTMENTS d 
WHERE 	LOCATION_ID in(1400,1500,1600,1700)
;
	-----------------------------------
SELECT 	*
FROM 	DEPARTMENTS d 
WHERE 	LOCATION_ID in(	SELECT 	LOCATION_ID 
						FROM 	LOCATIONS l 
						WHERE 	COUNTRY_ID = 'US')
;
	---------------------------------------
SELECT 	*
FROM 	EMPLOYEES e 
WHERE SALARY = (SELECT 	MIN(SALARY)
				FROM 	EMPLOYEES e2)
;
	----------------------------------------
SELECT 	*
FROM 	EMPLOYEES e 
WHERE SALARY = (SELECT 	MAX(SALARY)
				FROM 	EMPLOYEES e2)
;
	--------------------------------------

SubQuery Quiz 답안

SELECT * FROM EMPLOYEES e1;
-- 문제1) EMPLOYEES 테이블에서 Kochhar의 급여보다 많은 
-- 사원의 정보를 사원번호,이름,담당업무,급여를 출력하라.
SELECT 	EMPLOYEE_ID , LAST_NAME , JOB_ID , SALARY 
FROM 	EMPLOYEES e 
WHERE 	SALARY > (	SELECT 	SALARY 
					FROM 	EMPLOYEES e2 
					WHERE 	LAST_NAME  = 'Kochhar'
)
;
--------
SELECT 	SALARY 
FROM 	EMPLOYEES e 
WHERE 	LAST_NAME  = 'Kochhar'
;
--------
-- 문제2) EMPLOYEES 테이블에서 급여의 평균보다 적은 사원의 
-- 사원번호,이름,담당업무,급여,부서번호를 출력하여라.  
SELECT 	EMPLOYEE_ID , LAST_NAME , JOB_ID , SALARY , DEPARTMENT_ID 
FROM 	EMPLOYEES e 
WHERE 	SALARY < (	SELECT 	AVG( SALARY) 
					FROM 	EMPLOYEES e2 
)
;
-- 문제3) EMPLOYEES 테이블에서 100번 부서의 
-- 최소 급여보다 최소 급여가 많은 다른 모든 부서를 출력하라
SELECT 	DEPARTMENT_ID, MIN(SALARY) 
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
HAVING min(SALARY)> ((	SELECT 	MIN(SALARY) 
						FROM	EMPLOYEES e 
						WHERE 	DEPARTMENT_ID = 100))
;
-- 문제4) 업무별로 최소 급여를 받는 사원의 정보를 
-- 사원번호,이름,업무,부서번호를 출력하여라. 
-- 단 업무별로 정렬하여라.
-- 이것 못품...
SELECT 	JOB_ID , min(SALARY)
FROM 	EMPLOYEES e
GROUP BY JOB_ID 
;
SELECT 	e.EMPLOYEE_ID 	,	e.FIRST_NAME 
	,	e.JOB_ID 		,	e.SALARY 
FROM 	EMPLOYEES e
WHERE 	(JOB_ID, SALARY) IN (SELECT JOB_ID , min(SALARY)
							FROM 	EMPLOYEES e
							GROUP BY JOB_ID )
ORDER BY JOB_ID 
;

-- 문제5) EMPLOYEES 테이블에서 (사원번호가 123인 사원의 직업)과 같고 
-- (사원번호가 192인 사원의 급여(SAL))보다 많은 사원의 
-- 사원번호,이름,직업,급여를 출력하라.
SELECT 	EMPLOYEE_ID , LAST_NAME , JOB_ID , SALARY 
FROM 	EMPLOYEES e 
WHERE 	JOB_ID =(SELECT 	JOB_ID FROM 	EMPLOYEES e WHERE 	EMPLOYEE_ID = 123)
	AND salary > (SELECT 	SALARY FROM 	EMPLOYEES e WHERE 	EMPLOYEE_ID = 192)
;
SELECT 	SALARY 
FROM 	EMPLOYEES e 
WHERE 	EMPLOYEE_ID = 192
;
SELECT 	JOB_ID 
FROM 	EMPLOYEES e 
WHERE 	EMPLOYEE_ID = 123
;

-- 문제6) EMPLOYEES 테이블에서 (50번 부서의 최소 급여)를 받는 사원보다 많은 급여를 받는 
-- 사원의 사원번호,이름,업무,입사일자,급여,부서번호를 출력하라. 
-- 단 50번은 제외

SELECT 	MIN(SALARY) 
FROM 	EMPLOYEES e 
WHERE 	DEPARTMENT_ID = 50
;
SELECT 	*ㅁ
FROM 	EMPLOYEES e 
WHERE 	SALARY >(	SELECT 	MIN(SALARY) 
					FROM 	EMPLOYEES e 
					WHERE 	DEPARTMENT_ID = 50)
AND DEPARTMENT_ID != 50
;

전영덕

이전 포스트

DBMS 3일차

다음 포스트

DBMS 4일차

DBMS

Join 조인

1. Join 조인이란?

2. Join의 기본 사용 방법

3. inner join

4. outer join

5. Left Outer Join

6. Right Outer Join

7. full outer join

8. ANSI Join vs Oracle Join

2. 서브쿼리(subQuery)

1. 서브쿼리란?

3. 실전 연습

DBMS 3일차

DBMS_5일차

0개의 댓글