2023.07.13 ~07.17 수업 (script 22)
◎ JOIN이란
- 한 데이터베이스 내의 여러 테이블의 레코드를 조합하여 하나의 열로 표현한 것
- 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것
- 중복된 데이터가 많이 들어 있는 테이블은 관리가 어려움
- 불필요한 중복된 데이터에 의해 하드디스크(저장공간)의 낭비가 심하고 불필요한 정보로 인한 데이터의 오류가 발생할 수 있다(무결관, 일관성 등)
- 관계형데이터베이스에서 중복된 테이블의 데이터를 별도의 테이블로 분리하고
각각의 테이블을 연결하는 유일키를 사용하여 필요할때 테이블끼리 연결하여 확장하며
중복데이터 문제를 해결하는 것을 조인이라고 한다.
- 더 많은 테이블도 각각 두 테이블간의 조인들의 합이기도 함
(참고) 조인은 수평방향, 유니온은 수직방향으로 확장됨
[사용조건]
- 테이블 두개 이상
- 테이블을 조인하려면 각 테이블을 연결하기(관계 설정)위해 사용할 컬럼이 필요
- 이 컬럼들이 바로 두 테이블의 연결고리 역할을 하는 컬럼이다
- 두 개의 연결고리 컬럼은 각 테이블에 하나씩 존재
- 연결고리컬럼을 이용하여 SELECT문의 WHERE 조건절에서 연결을 위한 각종 조건연산을 사용해서 조인을 수행
- 연결고리컬럼은 보통 같은 이름과 같은 데이터형을 사용하고 한쪽에서는 중요한 키(기본키, primary key)로 다른쪽에서는 참조키(외래키, forgine)로 사용한다
INNER JOIN : 내부 조인은 둘 이상의 테이블에 존재하는 공통 속성의 값이 같은 것을 결과로 추출
-> 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
1) 동등 조인(EQUAL JOIN) : 둘 이상의 테이블에 존재하는 공통 속성의 동등 비교만을 사용. 부등호 조인은 동등조인에 포함하지 않는다.
2) 자연 조인(NATURAL JOIN) : 조인 대상 테이블의 모든 컬럼들을 비교하여, 같은 컬럼명을 가진 대상으로 조인을 수행. 그리고, 중복된 컬럼은 한번만 출력
OUTER JOIN : 조인하는 여러테이블에서 한 쪽에는 데이터가 있고, 한 쪽에는 데이터가 없는 경우, 데이터가 있는 쪽 테이블의 내용을 모두 출력하는 것
-> 건에 맞지 않아도 해당하는 행을 출력하고 싶을 때 사용
1) LEFT OUTER JOIN: 무조건 왼쪽 테이블의 값이 모두 출력
- 왼쪽에 조인 컬럼 값이 없는 경우(NULL)를 포함
- 오른쪽 테이블(두 번째 테이블)의 조인 컬럼에 데이터가 없어서 왼쪽 테이블의 조인 컬럼에 NULL값이 추가되는 레코드를 포함하여 가져와라
2) RIGHT OUTER JOIN : 무조건 오른쪽 테이블의 값이 모두 출력
- 오른쪽에 조인 컬럼 값이 없는 경우(NULL)를 포함
3) (UNION)FULL OUTER JOIN : 두 가지 경우를 합함
* SQL편집기 이름 변경하는 법
Ctrl + f2
▶ 내부조인(INNER JOIN)
※ JOIN으로 관리해야하는 이유
- 데이터가 너무 많다면 테이블을 분리해주어야함
- 중복데이터인 부서테이블, 주소테이블 분리하여 만들기
- 부서번호 또는 주소번호만 작성하면 나머지 생략 가능
▷ 실습용 테이블 만들기
- 사원, 부서, 주소 테이블 각각 만들기
-- 1. 사원 테이블
CREATE TABLE tbl_employee (
emp_id NUMBER NOT NULL,-- 사원번호
emp_name varchar2(100), -- 사원명
gender varchar2(10), -- 성별
age NUMBER, -- 나이
hire_date DATE, -- 입사일
dept_id NUMBER, -- 외부테이블 부서테이블의 기본키(외래키로 사용)
address_id NUMBER, -- 외부테이블 주소테이블의 기본키(외래키로 사용)
PRIMARY key(emp_id)
-- 기본키의 인덱스 이름을 직접 정하는 방법 :
-- CONSTRAINT 기본키파일이름 PRIMARY KEY(기본키 컬럼이름) 사용
-- 예)CONSTRAINT tbl_employee_pk
);-- 2. 부서 테이블
CREATE TABLE tbl_dept (
dept_id NUMBER NOT NULL, -- 부서id
dept_name varchar2(50), -- 부서명
use_yn varchar2(2) DEFAULT 'Y', -- 사용여부
dept_desc varchar2(200), -- 부서설명
CONSTRAINT tbl_dept_pk PRIMARY key(dept_id)
);-- 3. 주소 테이블
CREATE TABLE tbl_address (
address_id NUMBER NOT NULL, -- 주소일련번호
city varchar2(100), -- 도시명
gu varchar2(50), -- 구 이름
address_name varchar2(100), -- 나머지 주소
CONSTRAINT tbl_address_pk PRIMARY key(address_id)
);
※ 오라클에서는 테이블과 comment를 같이 사용할 수 없어 별도로 분리하여 작성해야함
1. 오라클에서 객체 주석 사용하기
- 객체주석을 사용하면 자기설명기능이 추가되어 나중에 컬럼의 용도나 중요한 정보를 추가하여 개발시 참조할 수 있다
예) COMMENT ON COLUMN tbl_dept.use_yn IS '부서가 현재 사용중이면 Y 아니면 N';
-> 주석 설명을 통해 어떤 값이 들어가는지 쉽게 알 수 있다
2) [구문]
COMMENT ON [TABLE or COLUMN] 객체이름 IS '객체설명';COMMENT ON TABLE 테이블명 IS '주석문';
COMMENT ON COLUMN 테이블명.컬럼객체명 IS '주석문';오라클은 COMMENT 작성시 별도의 COMMENT ON 객체타입 객체명 IS '설명내용' 형식 사용
예)COMMENT ON TABLE tbl_employee IS '사원테이블';
3) 추가된 코멘트 확인하기
[구문]
SELECT * FROM ALL_COL_COMMENTS
WHERE TABLE_NAME = '테이블명' -- 테이블이름을 문자열로 주기▷ 테이블 및 객체 주석(COMMENT) 처리
COMMENT ON TABLE tbl_address IS '주소테이블';
COMMENT ON COLUMN tbl_address.address_id IS '주소일련번호';
COMMENT ON COLUMN tbl_address.city IS '도시명';
COMMENT ON COLUMN tbl_address.gu IS '구 이름';
COMMENT ON COLUMN tbl_address.address_name IS '나머지 주소';
-- (테이블 주석 확인)
SELECT * -- 테이블의 코멘트 확인
FROM ALL_TAB_COMMENTS
WHERE table_name = UPPER('tbl_address');
SELECT * -- 테이블의 코멘트 확인(대문자로 해야 정확히 확인 가능)
FROM ALL_COL_COMMENTS
WHERE table_name = UPPER('tbl_address');▷ 실습에 사용할 데이터 추가(INSERT INTO)
-- 1. tbl_employee
INSERT INTO TBL_EMPLOYEE (
emp_id, emp_name, gender, age,
hire_date, dept_id, address_id )
VALUES(
1, '김유신', '남성', 56,
TO_DATE('2023-07-01', 'YYYY-MM_DD'), 1, 2);
INSERT INTO TBL_EMPLOYEE (
emp_id, emp_name, gender, age,
hire_date, dept_id, address_id )
VALUES(
2, '신사임당', '여성', 34,
TO_DATE('2023-07-01', 'YYYY-MM_DD'), 1, 1);
INSERT INTO TBL_EMPLOYEE (
emp_id, emp_name, gender, age,
hire_date, dept_id, address_id )
VALUES(
3, '홍길동', '남성', 45,
TO_DATE('2023-07-01', 'YYYY-MM_DD'), 3, 2);
INSERT INTO TBL_EMPLOYEE (
emp_id, emp_name, gender, age,
hire_date, dept_id, address_id )
VALUES(
4, '강감찬', '남성', 23,
TO_DATE('2023-07-01', 'YYYY-MM_DD'), 2, 3);
INSERT INTO TBL_EMPLOYEE (
emp_id, emp_name, gender, age,
hire_date, dept_id, address_id )
VALUES(
5, '세종대왕', '남성', 45,
TO_DATE('2023-07-01', 'YYYY-MM_DD'), 4, 4);-- 2. tbl_dept
INSERT INTO TBL_DEPT (dept_id, dept_name)
VALUES(1, '회계팀');
INSERT INTO TBL_DEPT (dept_id, dept_name)
VALUES(2, '경영팀');
INSERT INTO TBL_DEPT (dept_id, dept_name)
VALUES(3, '전산팀');
INSERT INTO TBL_DEPT (dept_id, dept_name)
VALUES(4, '마케팅팀');-- 3. tbl_address
INSERT INTO TBL_ADDRESS (address_id, city, gu, address_name)
VALUES (
1, '서울특별시', '중구', '새문안로 12');
INSERT INTO TBL_ADDRESS (address_id, city, gu, address_name)
VALUES (
2, '서울특별시', '서대문구', '연희로 15길');
INSERT INTO TBL_ADDRESS (address_id, city, gu, address_name)
VALUES (
3, '서울특별시', '영등포구', '여의대로 99');
INSERT INTO TBL_ADDRESS (address_id, city, gu, address_name)
VALUES (
4, '서울특별시', '강남구', '테해란로 33');-- 추가한 데이터 확인
SELECT * FROM TBL_EMPLOYEE;
SELECT count(*) FROM TBL_DEPT;
SELECT count(*) FROM TBL_ADDRESS;▷ 분리한 테이블을 조인하기
- 사원테이블은 부서테이블, 주소테이블과 연결되어있음
-> 원하는 것은 이 사원의 부서정보를 얻는 것
- 왼쪽 레코드에 대하여 오른쪽 레코드가 1개만 일치하므로 전체레코드(카테시안 곱)에서 inner join을 통해
일치하는 일부레코드들을 출력
1) 사원 - 부서 테이블 조인
1-1) 카테시안 곱(cross join) ver
-> 사원테이블의 각 사원레코드가 가능한 모든 부서 보직의 가능성을 전부 출력(말이 안되는 자료)
-- 테이블 두개를 연결하여 사원-부서테이블 만들기
-- 카테시안곱(Cartesian Product) : 컬럼의 결과가 경우의 수 개수 형태로 곱셈이 되어 출력
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
td.DEPT_ID, td.DEPT_NAME, td.USE_YN
FROM
TBL_EMPLOYEE te,
TBL_DEPT td;-> 내부조인(equal join)으로 해결하기
※ 내부조인 (inner join : equal join)
- WHERE절에서 같음(동등 : =)연산자를 사용하는 조인으로, 연결고리컬럼(=조인컬럼)을 비교하여 테이블에서 데이터를 연결하는 방법
- inner join이란 왼쪽테이블을 기준으로 오른쪽테이블의 레코드 전체에서 일치하는 내용만 보여라(교집합과 유사)
즉, 전체에서 일치하는 부분내용만 보여준다고해서 inner join이라고 한다
- 오라클 조인(Oracle Join)과 안시 조인(ANSI JOIN)을 사용
* 오라클
[구문]
- 앞에 나오는 t1이 기준테이블
SELECT
t1.* [또는 t1.컬럼리스트],
t2.* [또는 t2.컬럼리스트]
FROM
table1 t1,
table2 t2
WHERE
t1.외부키컬럼 = t2.기본키컬럼; -- 조인컬럼1-2) 사원 - 부서 테이블 내부조인 오라클 ver
-- 내부조인으로 해결하기
-- 일치하는 정보만 출력하기
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
td.DEPT_ID, td.DEPT_NAME, td.USE_YN
FROM
TBL_EMPLOYEE te,
TBL_DEPT td
WHERE
-- 사원테이블의 외부키(forgein)는 부서테이블에서 왔으므로 부서코드와 같아야함
-- 선택된 정보가 사원-부서테이블에 추가할 내용
te.DEPT_ID = td.DEPT_ID;
* ANSI(기술이나 규격 표준화를 주도하는 국제기구에서 제안한 SQL문법)
[구문]
- INNER JOIN이라는 키워드절을 사용하여 기준테이블과 관련테이블로 구분한다
- WHERE절은 전체 테이블에 제한을 주는 조건으로 사용하고 두 테이블사이의 연결제한 조건은 ON이라는 연산자 사용
SELECT
t1.* [또는 t1.컬럼리스트],
t2.* [또는 t2.컬럼리스트]
FROM
table1 t1 -- 기준테이블
INNER JOIN
table2 t2
ON t1.외부키컬럼 = t2.외부키컬럼
WHERE
전체 제한 조건
ORDER BY
정렬조건;1-3) 사원 - 부서 테이블 내부조인 ANSI ver
-- ANSI표준 사용하여 조인
SELECT
te.*,
ta.*
FROM
TBL_EMPLOYEE te
INNER JOIN
TBL_ADDRESS ta
ON te.ADDRESS_ID = ta.ADDRESS_ID
ORDER BY
te.EMP_ID;2) 사원 - 주소 테이블 조인
2-1) 카테시안 곱 ver
-> 사원테이블의 각 사원레코드가 가능한 모든 주소의 가능성을 전부 출력(말이 안되는 자료)
-- 사원테이블과 주소테이블 합치기(카테시안 곱)
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
ta.ADDRESS_ID, ta.CITY, ta.GU, ta.ADDRESS_NAME
FROM
TBL_EMPLOYEE te,
TBL_ADDRESS ta;2-2) 내부조인 오라클 ver
-- 사원테이블의 주소번호컬럼과 주소테이블의 주소번호컬럼이 같은(ADDRESS_ID) 정보 추출
-- 사원테이블의 사원이 사는 곳 정보 출력하기(inner join)
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
ta.ADDRESS_ID, ta.CITY, ta.GU, ta.ADDRESS_NAME
FROM
TBL_EMPLOYEE te,
TBL_ADDRESS ta
WHERE te.ADDRESS_ID = ta.ADDRESS_ID;2-3) 내부조인 ANSI ver
-- ANSI표준 사용하여 조인
SELECT
te.*,
ta.*
FROM
TBL_EMPLOYEE te
INNER JOIN
TBL_ADDRESS ta
ON te.ADDRESS_ID = ta.ADDRESS_ID
ORDER BY
te.EMP_ID;※ 내부조인 연습하기
▷ 사원테이블의 정보 가져오기(부서, 주소테이블과 결합하기)
- 그 결과에서 남성만 조회하기
1) 오라클
SELECT -- ID는 사원테이블만 출력하기
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
td.DEPT_NAME, td.USE_YN,
ta.CITY, ta.GU, ta.ADDRESS_NAME
FROM
TBL_EMPLOYEE te,
TBL_DEPT td,
TBL_ADDRESS ta
WHERE
te.DEPT_ID = td.DEPT_ID AND
te.ADDRESS_ID = ta.ADDRESS_ID AND
te.GENDER = '남성';
2) ANSI
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
td.DEPT_NAME, td.USE_YN,
ta.CITY, ta.GU, ta.ADDRESS_NAME
FROM
TBL_EMPLOYEE te
INNER JOIN
TBL_DEPT td
ON te.DEPT_ID = td.DEPT_ID
INNER JOIN
TBL_ADDRESS ta
ON te.ADDRESS_ID = ta.ADDRESS_ID
WHERE
te.GENDER = '남성';
▷ 사원테이블 기준으로 부서, 주소테이블과 결합하여 나이 40 ~ 50사이 구하기
1) 오라클
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
td.DEPT_NAME, td.USE_YN,
ta.CITY, ta.GU, ta.ADDRESS_NAME
FROM
TBL_EMPLOYEE te,
TBL_DEPT td,
TBL_ADDRESS ta
WHERE
te.DEPT_ID = td.DEPT_ID AND
te.ADDRESS_ID = ta.ADDRESS_ID AND
te.AGE BETWEEN 40 AND 50;2) ANSI
SELECT
te.EMP_ID, te.EMP_NAME, te.GENDER, te.AGE, te.HIRE_DATE,
td.DEPT_NAME, td.USE_YN,
ta.CITY, ta.GU, ta.ADDRESS_NAME
FROM
TBL_EMPLOYEE te
INNER JOIN
TBL_DEPT td
ON te.DEPT_ID = td.DEPT_ID
INNER JOIN
TBL_ADDRESS ta
ON te.ADDRESS_ID = ta.ADDRESS_ID
WHERE
te.AGE BETWEEN 40 AND 50;'백엔드 > 데이터베이스' 카테고리의 다른 글
| 데이터베이스 (서브쿼리) (0) | 2023.08.03 |
|---|---|
| 데이터베이스(OUTER JOIN) (0) | 2023.08.02 |
| 데이터베이스(INSERT, DELETE, UPDATE) (0) | 2023.08.01 |
| 데이터베이스(SELECT) - 2 (0) | 2023.07.13 |
| 집합 연산(UNION, UNION ALL, INTERSECT, MINUS) (0) | 2023.07.12 |
