반응형
JOIN
정의
- n개 이상의 관계성이 존재하는 테이블을 참조하여 하나의 테이블을 만들어내는 연산이다.
- 튜플의 조합 집합을 만들어내는 연산이다.
* JOIN은 RDBMS에서 가장 강력하고 중요한 연산이다.
JOIN 시나리오
- 책과 작가를 소개하는 사이트다.
- 책은 제목, 작가, 책 소개글이 필요하다.
- 작가는 이름, 사는도시, 직업, 직업 소개글이 필요하다.
CREATE TABLE topiktbl
( id INT NOT NULL PRIMARY KEY,
title VARCHAR(40) NOT NULL,
description VARCHAR(100),
name VARCHAR(20),
city VARCHAR(20),
job_title VARCHAR(40),
job_description VARCHAR(100)
);
INSERT topiktbl VALUES ('1', '쉬운 html', 'html 책입니다.', '홍길동', '서울', '강사', '훈련교강사');
INSERT topiktbl VALUES ('2', '쉬운 JAVA', 'JAVA 책입니다.', '김철수', '광주', '개발자', '웹 개발자');
INSERT topiktbl VALUES ('3', '쉬운 DB', 'DB 책입니다.', '홍길동', '서울', '강사', '훈련교강사');
→ 문제점
- 중복되는 데이터를 수정할 시 중노동으로 이어진다. EX) 홍길동이 이사를 가면 city를 하나하나 바꿔주어야한다.
- 중복되는 데이터가 공간을 낭비한다. EX) 홍길동이 책을 쓸 수록 name의 홍길동이 계속 생겨난다.
- 이상현상(Anomaly)이 발생한다.
더보기
이상현상 (Anomaly)
테이블 내의 데이터들이 불필요하게 중복되어 테이블을 조작할 때 발생되는 데이터 불일치 현상이다.
→ 해법
- topiktbl과 authortbl로 테이블을 분산해 준다.
- topitktbl에 FOREIGN KEY, authortbl에 PRIMARY KEY를 생성하여 두키의 관계성을 형성 시킨다. 이때 두 키의 데이터 타입은 반드시 동일해야한다.
drop table testdb.topiktbl; -- drop set 1. drop table testdb.authortbl; -- drop set 2. CREATE TABLE authortbl -- 1. 작가 테이블 생성 ( id INT NOT NULL PRIMARY KEY, name VARCHAR(20) NOT NULL, city VARCHAR(20), job_title VARCHAR(10), job_description VARCHAR(10) ); CREATE TABLE topiktbl -- 2. 토픽 테이블 생성 ( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY, title VARCHAR(40) NOT NULL, description VARCHAR(100), author_id INT NOT NULL, FOREIGN KEY (author_id) REFERENCES authortbl (id) ); INSERT authortbl VALUES ('1', '홍길동', '서울', '강사', '훈련교강사'); -- 3. 작가 테이블 정보 입력. INSERT authortbl VALUES ('2', '김철수', '광주', '백엔드 개발자', '웹 개발자'); INSERT authortbl VALUES ('3', '정상록', '서울', '풀스택 개발자', '구글 개발자'); INSERT topiktbl VALUES (null, '쉬운 html', 'html 책입니다.', '1' ); -- 4. 토픽 테이블 정보 입력. INSERT topiktbl VALUES (null, '쉬운 JAVA', 'JAVA 책입니다.', '2' ); INSERT topiktbl VALUES (null, '쉬운 DB', 'DB 책입니다.', '1' ); INSERT topiktbl VALUES (null, '쉬운 SQL', 'SQL 책입니다.', '3' ); INSERT topiktbl VALUES (null, '쉬운 PYTHON', 'PHTON 책입니다.', '3' ); ALTER TABLE topiktbl AUTO_INCREMENT = 0; -- + AUTO INCREMENT의 시작값을 0으로 설정한다. SELECT * FROM topiktbl; SELECT * FROM authortbl
* 해당 해법의 단점
- 구조적인 설계 변경이 일어난다.
- 복잡도가 증가한다.
JOIN의 종류
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- FULL OUTER JOIN
- CROSS JOIN
SQL Joins Visualizer
Please select how do you want to do SQL JOIN between two table Copy SQL
sql-joins.leopard.in.ua
LEFT OUTER JOIN
A테이블을 기준으로 겹치는 A와 B를 가져온다.
명령식
SELECT [Column] FROM [Atable] LEFT JOIN [Btable] ON [Atable.FK] = [Btable.PK]적용
SELECT * FROM topiktbl LEFT JOIN authortbl ON topiktbl.author_id = authortbl.id;
→문제점 : author id와 authortbl의 id를 생략하지 못했다.
해법 : SELECT문 다음에 가져올 Column들을 설정한다.
SELECT topiktbl.id, title, description, name, city, job_title, job_description FROM topiktbl LEFT JOIN authortbl ON topiktbl.author_id = authortbl.id;
☞ 예제 : 홍길동이 쓴 책만 출력하라.
SELECT name, title, description, city, job_title, job_description FROM topiktbl LEFT JOIN authortbl ON topiktbl.author_id = authortbl.id WHERE name = '홍길동';
RIGHT OUTER JOIN
'RIGHT OUTER JOIN' 은 'LEFT OUTER JOIN' 의 다른 형태다.
'RIGHT JOIN은 오른쪽의 것은 모두 출력되어야 한다.' 의 의미입니다. LEFT도 동일합니다.
FROM [Btable] RIGHT JOIN [Atable] 로 A와 B의 위치만 다르다.
즉 기본 메커니즘이 같으며 어떤 지점을 기준으로 하느냐에 따라 없는 정보의 NULL을 출력하지 않는다.
SELECT name, title, description, city, job_title, job_description
FROM authortbl RIGHT JOIN topiktbl ON topiktbl.author_id = authortbl.id
WHERE name = '홍길동';
INNER JOIN
개념
- A, B에 공통적으로 존재하는 행만 JOIN 한다.
- NULL은 제외하여 성능상의 이점이 있다.
- NULL이 제외된다는 것은 연산의 대상 자체가 되지 않음을 의미한다.
SELECT * FROM topiktbl INNER JOIN authortbl ON topiktbl.author_id = authortbl.id;
select 상품.상품코드, 재고수.재고수 from 상품 INNER JOIN 재고수 on 상품.상품코드 = 재고수.상품코드;
select 상품2.상품명, 메이커.메이커명 from 상품2 Inner Join 메이커;
방언
▶ Oracle
SELECT 상품3.상품명, 재고수.재고수
FROM 상품3.재고수
WHERE 상품3.상푸모드 = 재고수.상품코드 (+);
▶ MSSQL
(*= or =*) 사용 외부결합
▶ 표준 SQL
INNER JOIN
LEFT JOIN, RIGHT JOIN
FULL OUTER JOIN
개념
- A, B에 모두 존재하는 행을 JOIN 연산한다.
- LEFT JOIN + RIGHT JOIN = FULL OUTER JOIN
- 중복은 제외한다.
(SELECT * FROM topiktbl LEFT JOIN authortbl ON topiktbl.author_id = authortbl.id)
UNION
(SELECT * FROM topiktbl RIGHT JOIN authortbl ON topiktbl.author_id = authortbl.id);
더보기
거의 사용하지 않는다.
▶ UNION ALL
- 중복을 고려 하지 않습니다.
- 합하하려는 두 컬럼이 유니크 하다면 UNION ALL을 사용하는게 효율적입니다.
CROSS JOIN
한쪽 테이블의 모든 행들과 다른 쪽 테이블의 모든 행을 조인시키는 기능입니다.
두 테이블을 곱한 데이터 수를 가집니다.
테스트로 사용할 많은 용량의 데이터를 생성할 때 주로 사용합니다.
ON 구문을 사용 할 수 없습니다.
대량의 데이터를 생성하면 시스템이 다운되거나 디스크 용량이 모두 찰 수 있어 COUNT(*) 함수로 개수만 카운드 합니다.
▶ 기본 문법
SELECT * FROM 테이블1, 테이블2
JOIN 읽는 법
- JOIN을 한 테이블들을 확인한다.
- Column을 확인한다.
- WHERE를 확인한다.
- 기타 조건 확인한다.
+ 예제
author table 분리하여 Profile table을 만들고 3개의 Table을 Join 하라. (다중 JOIN)
CREATE TABLE protbl -- 0. 프로파일 테이블 생성
(
id INT PRIMARY KEY NOT NULL,
job_title VARCHAR(10),
job_description VARCHAR(10)
);
CREATE TABLE authortbl -- 1. 작가 테이블 생성
(
id INT NOT NULL PRIMARY KEY,
name VARCHAR(20) NOT NULL,
city VARCHAR(20),
pro_id INT,
FOREIGN KEY(pro_id) REFERENCES protbl(id)
);
CREATE TABLE topiktbl -- 2. 토픽 테이블 생성
( id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
title VARCHAR(40) NOT NULL,
description VARCHAR(100),
author_id INT,
FOREIGN KEY (author_id) REFERENCES authortbl (id)
);
홍길동이 쓴 책을 조회하고 Column명을 한국어로 설정하여 조회하라.
SELECT topiktbl.id AS '아이디', title AS '책 제목', description AS '책 설명', name AS '저자', city AS '저자 주소', job_title AS '저자 직업', job_description AS '직업 상세'
FROM topiktbl
LEFT JOIN authortbl ON topiktbl.author_id = authortbl.id
LEFT JOIN protbl ON authortbl.pro_id = protbl.id
WHERE name ='홍길동';
name에 오름차순으로 정렬하라.
SELECT topiktbl.id, title , description , name , city , job_title , job_description
FROM topiktbl
LEFT JOIN authortbl ON topiktbl.author_id = authortbl.id
LEFT JOIN protbl ON authortbl.pro_id = protbl.id
ORDER BY authortbl.name DESC;
반응형
'SQL' 카테고리의 다른 글
| [MySQL] 예약어 (0) | 2022.09.14 |
|---|---|
| [MySQL] 실행, 종료 (MAC) (0) | 2022.09.14 |
| [MySQL] SubQuery(서브쿼리), ORDER BY, DISTINCT, GROUP BY, HAVING, VIEW (0) | 2022.03.30 |
| [MySQL] CRUD / 예약어 (0) | 2022.03.29 |
| [MySQL] 설치 (MAC) (0) | 2022.03.28 |