[Mysql || MariaDB] JOIN 총 정리

오늘은 Mysql || MariaDB JOIN 개 (개념) 이 (문법 및 예시) 시작하겠습니다.

JOIN

개념

---

JOIN 연산은 두 테이블을 결합하는 연산입니다.

 

즉, 테이블은 각자에 맞는 데이터를 저장하고 있는데, 서로 다른 테이블에서 데이터를 가져오려면 JOIN 연산을 해야 합니다.

 

종류

---

Inner JOIN

 

Cross JOIN || 카티전 조인 || 상호 조인

 

OUTER JOIN || External JOIN

1.LEFT OUTER

2.RIGHT OUTER

3.FULL OUTER JOIN

 

 

문법에 들어가기 앞서, 공통 테이블 예시

CREATE TABLE people (

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
age INT

)

CREATE TABLE card_company(

id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(64),
amount_of_payment INT,
payment_location VARCHAR(64),
paymentdate DATETIME,
people_id INT 
)

INSERT INTO people VALUES(1,'김사원',24);

INSERT INTO people (NAME,age) VALUES('구대리',28);

INSERT INTO people (NAME,age) VALUES('허차장',42);

INSERT INTO people (NAME,age) VALUES('차부장',45);

INSERT INTO people (NAME,age) VALUES('홍임원',54);


insert into card_company VALUES(1,'NH',30000,'배달서비스','2019-11-09 23:02',1);

insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('shinhan',7700,'편의점','2019-11-09 10:10',1);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('KB',4500,'편의점','2019-11-09 15:21',1);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('KB',8550,'당구장','2019-11-09 19:35',1);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('shinhan',330000,'명품신발','2019-11-09 05:00',2);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('shinhan',4500000,'명품옷','2019-11-09 07:00',2);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',400000,'돈 인출','2019-11-09 11:00',3);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',1300000,'사성냉장고','2019-11-09 15:00',3);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',10000000,'골프','2019-11-09 17:00',4);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',15000000,'유흥','2019-11-09 03:00',4);
insert into card_company (name,amount_of_payment,payment_location,paymentdate,people_id) VALUES('NH',500,'문방구','2019-11-09 04:00',6);

people table (사람 정보 테이블)

card_company (카드 지출내역 테이블)

INNER JOIN

 

개념

---

두 테이블에 *교집합 이라고 생각을 하면 된다. 두 테이블에 공통된 값이 있으면 출력

INNER JOIN은 조인하는 테이블의 ON 절의 조건이 일치하는 결과만 출력됩니다.

 

*교집합 : 집합론에서, 두 집합 A와 B의 교집합 A ∩ B는 그 두 집합이 공통으로 포함하는 원소로 이루어진 집합이다.

 

 

문법

---

#inner join
SELECT * FROM people JOIN card_company ON people.id = card_company.people_id

간단 설명

---

ON people.id = card_company.people_id

사람테이블 id와 카드 테이블의 사람_id 맞는 정보만 가져왔다.

그 이유로 홍임원은 지출이 없어서 결과에 나오지 않았음. 

 

LEFT OUTER JOIN

 

개념

 

---

두 테이블 중에서 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우에 사용을 하게 됩니다. 왼쪽 테이블 (Table A)의 값은 모두 다 나오지만 오른쪽 테이블 (Table B)의 값은 매칭이 되는게 없으면 출력이 되지 않음.

말 그대로 왼쪽 테이블 정보는 무조건 다 나옴

왼쪽 테이블 정보는 무조건 다 나옴

왼쪽 테이블 정보는 무조건 다 나옴

 

문법

---

#LEFT OUTER join
SELECT * FROM people LEFT OUTER JOIN card_company ON people.id = card_company.people_id

간단 설명

---

위에서 3번 강조한 것처럼 왼쪽!! 테이블정보가 다 출력되어서 홍임원은 지출내역이 없지만 출력된 모습.

 

RIGHT OUTER JOIN

 

개념

---

두 테이블 중에서 오른쪽 테이블에 조인시킬 컬럼의 값이 없는 경우에 사용을 하게 됩니다. 오른쪽 테이블 (Table B)의 값은 모두 다 나오지만 왼쪽 테이블 (Table A)의 값은 매칭이 되는게 없으면 출력이 되지 않음.

말 그대로 오른쪽 테이블 정보는 무조건 다 나옴

오른쪽 테이블 정보는 무조건 다 나옴

오른쪽 테이블 정보는 무조건 다 나옴

 

문법

---

#RIGHT OUTER JOIN
SELECT * FROM people RIGHT OUTER JOIN card_company ON people.id = card_company.people_id

간단 설명

---

위에서 3번 강조한 것처럼 오른쪽!! 테이블정보가 다 출력되어서 people 테이블에 6번이라는 id를 가진 사람은 없지만 출력이 된 모습.

제가 일부러 안맞는 데이터를 하나씩 넣어두었습니다.

 

 

CROSS JOIN(상호 조인,카티전 조인)

 

개념

---

한 쪽 테이블의 모든 행들과 다른 테이블의 모든 행을 조인시키는 기능

 

모든 경우의 수, 즉 A 테이블 row 개수 X B 테이블 row 개수 만큼의 row를 가진 테이블이 출력

*Cartesian Product, 곱집합 이용

 

 

문법

---

#CROSS JOIN
SELECT * FROM people CROSS JOIN card_company

..데이터 중간 생략

간단 설명

---

people row 5개

card_company 11개

5X11=55개의 ROW가 출력됨.

 

사용 이유

---

cross join은 사용이유가 궁금하니 이것만 특별하게 사용 이유를 알아보자.

대개 테스트로 사용할 대용량의 테이블을 생성할 경우에 사용된다.

예를 들어, 각 1 만건과 10 만건의 테이블을 CROSS JOIN 하면 10억건의 데이터를 생성시킬 수 있다.

 

 

SELF JOIN

 

개념

---

별도의 구문이 있는 것이 아니라, 자기 자신과 자기 자신이 조인한다는 의미

SELECT * FROM people SELF JOIN card_company 같은 문법이 있는게 아니라

말 그대로 자기 자신의 테이블 컬럼끼리 조인을 한다고 보면 된다.

 

문법

---

SELF JOIN을 위한 예시 테이블

CREATE table ugaga_tribes (
id INT AUTO_INCREMENT PRIMARY KEY ,
name VARCHAR(64),
classes_id INT
)

insert into ugaga_tribes VALUES(1,'족장_우가콜라',null);

insert into ugaga_tribes (name,classes_id) VALUES('부족장_우가펩시',1);
insert into ugaga_tribes (name,classes_id) VALUES('부하1_우가팔일오',2);
insert into ugaga_tribes (name,classes_id) VALUES('부하2_우가우간다',3);
insert into ugaga_tribes (name,classes_id) VALUES('부하3_우가막내',4);

 

SELF JOIN-INNER JOIN 사용

#Self join-Inner join
SELECT ut_a.id,ut_a.name AS 부하 ,ut_b.name AS 상관
FROM ugaga_tribes ut_a 
JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id;

간단 설명

---

 

ON ut_a.classes_id = ut_b.id; - inner join의 특징은 서로 일치하는 값을 표현하는것이라고 위에서 말했다.

상관의 ID(ut_a.classes_id) 부하의 ID(ut_b.id)가 일치하는 값만 출력하기 때문에 위와 같은 결과가 나왔다.

 

상관이 없는 부족들까지 출력하기.

SELF JOIN-LEFT JOIN 사용

#Self join-Left join
SELECT ut_a.id,ut_a.name,ut_b.name 
FROM ugaga_tribes ut_a 
LEFT JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id;

간단 설명

---

ugaga_tribes ut_a  LEFT JOIN ugaga_tribes ut_b , 즉 같은 테이블이긴 하지만 위에 LEFT JOIN처럼 왼쪽!! 테이블 정보가 모두 출력되어 저 결과을 출력 할 수 있다.

 

 

JOIN 조건 걸기

---

JOIN을 사용하다 보면 굉장히 복잡한 쿼리문이 탄생하는데요,

실무에 투입되면 분명 JOIN에 조건을 걸어야 할 일이 생깁니다.

 

바로 알아보겠습니다.

위에 SELF JOIN-LEFT JOIN 사용 예제에 조건을 달아보겠습니다.

 

ut_b에 id가 2 이상인 데이터만 출력하기

SELECT ut_a.id,ut_a.name,ut_b.name 
FROM ugaga_tribes ut_a 
LEFT JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id AND ut_b.id > 2;

ut_b에 name이 '부족'이 포함된 데이터만 출력하기

SELECT ut_a.id,ut_a.name,ut_b.name 
FROM ugaga_tribes ut_a 
LEFT JOIN ugaga_tribes ut_b 
ON ut_a.classes_id = ut_b.id AND ut_b.name LIKE '%부족%';

 

JOIN에 조건을 주고 싶을 땐, on절에 and를 이용하여 값을 추가하면 됩니다. 

 

 

예제 파일 제공

---

[MariaDB] JOIN.sql

0.00MB