코딩 기록들
[Database Programming] 6.조인(JOIN)에 대하여 (+INNER JOIN) 본문
조인 JOIN
집합연산자 vs 조인
- 집합연산자를 사용한 결과는 : 2개이상 SELECT문의 결과값을 '세로'로 연결한 것
- 조인을 사용한 결과 : 2개이상 테이블 데이터를 '가로'로 연결한것
테이블 별칭 설정
- FROM절에 지정한 테이블에는 SELECT절의 열에 사용한것처럼 별칭 지정할 수 있다 -> 명시한 테이블 이름에서 한 칸 띄운 후 지정함
FROM 테이블이름1 별칭1, 테이블이름2 별칭2 ..JOIN관계에 있는 데이터에 따라 여러가지 형태의 관계가 형성됨
1) 1 : 1 ( 만들어서는 안되는 관계 )
- 한 테이블의 PK가 다른 테이블의 FK에 하나만 존재하는 관계
- 단, 컬럼의 수가 40개 이상 넘어갈 경우엔 테이블을 분리하여 1 : 1 관계를 맺어야 함
2) 1 : N
- 한 테이블의 PK가 다른 테이블의 FK에 여러 개가 존재하는 관계
- 가장 흔하게 만들게 되는 관계
ex) 한 명의 회원(PK)이 여러 개의 상품을 주문(FK)한다 (=fk가 중복되는 형태)
한 명의 회원(PK)이 여러 개의 게시글을 작성 (FK) 한다. 등
3) N : N (물리적으로 존재할 수 없는 관계)
- N : N 관계는 별도의 테이블을 만들어 1 : N 으로 해소해야 함
조인의 종류
- 대상데이터를 어떻게 연결하느냐에 따라 내부조인(등가조인, 비등가조인, 자체조인 ) / 외부조인으로 구분함
(등가, 자체 조인은 조인조건에 해당하는 데이터가 존재할경우에만 출력하기때문에 내부조인이라고 부름)
1. 등가조인
=내부조인 INNER JOIN = 단순조인 SIMPLE JOIN
- 테이블을 연결한 후 출력행을 각 테이블의 특정열에 일치한 데이터를 기준으로 선정하는 방식
- 일반적으로 가장 많이 사용되는 조인방식
- 테이블간 PK – FK 로 연결해 관련정보들을 가져옴 ex) 하나의 부서(PK)에 여러 명의 사원(FK)이 근무중
- 테이블 2개를 하나로 합치는것
- INNER 조인 = 교집합 이라고 생각하기!(같은부분만 가져옴)
2. 비등가조인
- 등가조인 방식 외의 방식을 의미함
- 조인 조건이 특정 열의 일치여부를 검사하는 방식 외에 다른 방식도 사용할수있음을 기억할것
3. 자체조인
- 하나의 테이블을 여러개의 테이블처럼 활용하여 조인하는 방식 -> 물리적으로 동일한 테이블 여러개를 사용할때 발생할수있는 문제점을 해결함
- FROM절에 같은테이블을 여러번 명시하되, 테이블의 별칭만 다르게 지정하는 방식으로 사용
4. 외부조인 (OUTER JOIN)
- 두 테이블 간 조인 수행해서 조인 기준 열의 한 쪽이 NULL이어도 강제로 출력하는 방식
INNER JOIN
FROM [TABLE A] A
INNER JOIN [TABLE B] B
ON A.PK = B.FK
WHERE [CONDITIONS]
↓
FROM STUDENT A
INNER JOIN TEACHER B
ON B.TEACHER_ID = A.TEACHER_ID
sub query vs inner join?
- sub query 를 쓰는 목적 : 여러 테이블을 연결해서 한 테이블의 정보만 조회 할 때 사용
- inner join을 쓰는 목적 : 여러 테이블을 연결해서 여러 테이블의 정보를 동시에 조회할 때 사용
INNER JOIN 문법
SELECT A.*
,B.*
FROM TABLE A
INNER JOIN TABLE B
ON A.PK = B.FK
;
-- 단, PK와 FK의 순서(위치)는 바껴도 상관없음- 표준SQL인 ANSI SQL에서는 INNER JOIN이라는 표현을 INNER 생략하고 'JOIN'으로만도 표현가능
- PK와 FK의 순서(위치)는 바껴도 상관없음
INNER JOIN 예제
-- EMPLOYEES와 DEPARTMENTS의 모든 정보를 조회
-- 부서에서 근무중인 사원들의 사원정보와 부서정보를 모두조회
SELECT E.*
, D.*
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
;
-- 사원들이 근무하는 부서의 이름과 지역의 도시명, 사원들의 이름을 조회한다
SELECT D.DEPARTMENT_NAME
, E.FIRST_NAME
, L.CITY
FROM EMPLOYEES E
INNER JOIN DEPARTMENTS D
ON D.DEPARTMENT_ID = E.DEPARTMENT_ID
INNER JOIN LOCATIONS L
ON L.LOCATION_ID = D.LOCATION_ID
;
-- 사원번호가 150번 이하인 사람들의 이름과 성, 연봉, 직무아이디, 직무명을 조회
SELECT E.FIRST_NAME
, E.LAST_NAME
, E.SALARY
--E.JOB_ID -FK
, J.JOB_ID --PK 를 가져오는것이 유리함
, J.JOB_TITLE
FROM EMPLOYEES E
JOIN JOBS J
ON J.JOB_ID = E.JOB_ID
WHERE E.EMPLOYEE_ID <= 150
;
-- 부서번호, 부서명, 부서장의 사원번호, 부서장의 이름, 부서장의 성, 부서장의 직무명을 조회한다
SELECT D.DEPARTMENT_ID
, D.DEPARTMENT_NAME
, D.MANAGER_ID
, E.FIRST_NAME
, E.LAST_NAME
, J.JOB_TITLE
FROM DEPARTMENTS D
JOIN EMPLOYEES E
ON E.EMPLOYEE_ID = D.MANAGER_ID
JOIN JOBS J
ON J.JOB_ID = E.JOB_ID
;
-> 120번 부서부터는 MANAGER_ID가 NULL값인걸
확인할수있다! (JOIN은 교집합연산이기 때문)
직무가 변경된 적이 있는 사원의 과거 직무명과 현재 직무명을 조회
- 현재직무명 : EMPLOYEES의 직무아이디와 JOBS연결해서 JOBTITLE 가져오면 알수잇음
- 과거직무명 : EMPLOYEES - JOB_HISTORY 연결
SELECT PAST_J.JOB_TITLE
, JH.START_DATE
, JH.END_DATE
, NOW_J.JOB_TITLE
, E.EMPLOYEE_ID
FROM EMPLOYEES E
JOIN JOBS NOW_J --현재직무의 정보
ON NOW_J.JOB_ID = E.JOB_ID
JOIN JOB_HISTORY JH
ON E.EMPLOYEE_ID = JH.EMPLOYEE_ID
JOIN JOBS PAST_J -- 과거직무의 정보
ON PAST_J.JOB_ID = JH.JOB_ID
--EMPLOYEES에 있는것 : 현재정보
--JOB HISTORY에 있는것 : 과거정보자기가 자기를 참조하는 쿼리
- 메뉴를 출력할때, 계단식 정보를 관리할 떄 많이 사용
- 183번 사원의 이름과 상사정보를 조회한다
SELECT E.FIRST_NAME
, M.*
FROM EMPLOYEES E --사원정보
INNER JOIN EMPLOYEES M -- 상사정보
ON E.MANAGER_ID = M.EMPLOYEE_ID
WHERE E.EMPLOYEE_ID = 183
;
SELECT col -> col 대신 쓰는 쿼리 : 스칼라scala 쿼리 -> 사용x (성능저하)
FROM table -> table 대신 쓰는 쿼리 : INLINE VIEW
WHERE col 연산자 값 -> 값 대신에 쓰는 쿼리 = 서브쿼리
(성능이 떨어지기 때문에 사용지양할것)
order by,
group by
scala query