MySQL JOIN

테이블에 기준을 FROM 절에 나오는 테이블 기준입니다.

 

JOIN은 두 개 이상의 테이블에서 관련된 데이터를 결합하여 새로운 결과를 생성하는 데이터베이스 연산입니다.

1.1. JOIN이 필요한 이유

• 데이터베이스는 중복을 최소화하기 위해 데이터를 여러 테이블에 나눠 저장합니다.
• 실제 사용 시, 분산된 데이터를 통합해야 할 때가 많습니다.
• 예: 학생 정보(tb_student)와 성적 등급 정보(tb_grade)를 결합하여 학생별 점수를 확인.

1.2. JOIN의 기본 개념

• JOIN은 테이블 간 공통 열(예: 외래 키와 기본 키)을 기준으로 데이터를 결합합니다.
• MySQL에서는 INNER JOIN, LEFT JOIN, RIGHT JOIN을 지원하며, FULL OUTER JOIN은 지원하지 않습니다.

---

2.1. 테이블 생성

학생

컬럼명	데이터 타입	제약사항	제약사항 설명
no	INT	NOT NULL, PRIMARY KEY	고유 식별자, NULL 값 불허
name	VARCHAR(20)	NOT NULL	학생 이름, NULL 값 불허
gender	ENUM('F', 'M')	NOT NULL	성별, 'F' 또는 'M'만 가능, NULL 값 불허
grade	CHAR(1)	FOREIGN KEY REFERENCES tb_grade(grade)	다른 테이블의 기본키 참조, 학년 정보

 

성적 등급

컬럼명	데이터 타입	제약사항	제약사항 설명
grade	CHAR(1)	PRIMARY KEY	학년, 고유 식별자로 기본키 설정
score	INT		점수, 별도의 제약사항 없음

-- drop database school;
create database school;
use school;
-- 학생, 성적 등급 테이블 생성 
create table tb_grade(
   grade char(1) primary key, 
   score int
);

create table tb_student(
	no int primary key, 
    name varchar(20) not null, 
    gender enum('F', 'M') not null, 
    age int,
	grade char(1), 
	foreign key(grade) references tb_grade(grade)
);

insert into tb_grade(grade, score) values
('A', 100),
('B', 80),
('C', 60),
('D', 40),
('E', 20),
('F', 0);

select * from tb_grade;

-- tb_student 데이터 삽입
INSERT INTO tb_student (no, name, gender, age, grade) VALUES
    (20170001, '조이', 'F', 25, 'B'),
    (20170020, '앤드류', 'M', 26, 'B'),
    (20180800, '데이지', 'F', 24, 'A'),
    (20190123, '다나', 'F', 23, 'A'),
    (20201000, '스카이', 'M', 22, 'D');

---

3. 실습

3.1. INNER JOIN

• 설명: 두 테이블에서 조건에 맞는 데이터만 결합. 조건에 맞지 않는 데이터는 제외됨.
• MySQL 특징: MySQL에서 JOIN, INNER JOIN, CROSS JOIN은 기본적으로 동일한 결과를 생성. 그러나 ON 조건 여부에 따라 결과가 달라짐.
  • ON 조건 없이 사용 시 CROSS JOIN 결과(모든 행의 조합).
  • ON 조건 사용 시 조건에 맞는 데이터만 결합.

예제 1: CROSS JOIN (ON 조건 없이 - CROSS JOIN은 INNER JOIN 의 특수한 형태)

SELECT *
FROM tb_student
JOIN tb_grade;

• 결과: 학생 5명 × 등급 6개 = 30행(모든 조합)

예제 2: INNER JOIN (ON 조건 사용)

SELECT s.no, s.name, s.gender, s.age, s.grade, g.score
FROM tb_student as s
INNER JOIN tb_grade as g
ON s.grade = g.grade;

• 결과: 학생의 grade와 등급의 grade가 일치하는 데이터만 결합.

no      | name   | gender | age | grade | score
--------|--------|--------|-----|-------|-------
20170001| 조이   | F      | 25  | B     | 80
20170020| 앤드류 | M      | 26  | B     | 80
20180800| 데이지 | F      | 24  | A     | 100
20190123| 다나   | F      | 23  | A     | 100
20201000| 스카이 | M      | 22  | D     | 40

3.2. OUTER JOIN

OUTER JOIN은 조건에 맞지 않는 데이터도 포함하며, 기준 테이블에 따라 LEFT JOIN과 RIGHT JOIN으로 나뉩니다.

3.2.1. LEFT JOIN (LEFT OUTER JOIN)

• 설명: 왼쪽 테이블(tb_student)을 기준으로 결합. 조건에 맞는 데이터가 없으면 오른쪽 테이블(tb_grade)의 열은 NULL로 채워짐.
• 사용 예: 모든 학생을 조회하되, 등급 점수가 없는 경우도 포함.

예제:

SELECT s.no, s.name, s.gender, s.age, s.grade, g.score
FROM tb_student s
LEFT JOIN tb_grade g
ON s.grade = g.grade;

• 결과: 모든 학생이 포함되며, grade가 tb_grade에 없는 경우 score는 NULL.

no      | name   | gender | age | grade | score
--------|--------|--------|-----|-------|-------
20170001| 조이   | F      | 25  | B     | 80
20170020| 앤드류 | M      | 26  | B     | 80
20180800| 데이지 | F      | 24  | A     | 100
20190123| 다나   | F      | 23  | A     | 100
20201000| 스카이 | M      | 22  | D     | 40

3.2.2. RIGHT JOIN (RIGHT OUTER JOIN)

• 설명: 오른쪽 테이블(tb_grade)을 기준으로 결합. 조건에 맞는 데이터가 없으면 왼쪽 테이블(tb_student)의 열은 NULL로 채워짐.
• 사용 예: 모든 등급을 조회하되, 해당 등급을 가진 학생이 없는 경우도 포함.

예제

SELECT g.grade, g.score, s.no, s.name, s.gender, s.age
FROM tb_student s
RIGHT JOIN tb_grade g
ON s.grade = g.grade;

• 결과: 모든 등급이 포함되며, 해당 등급을 가진 학생이 없으면 학생 정보는 NULL.

grade | score | no      | name   | gender | age
------|-------|---------|--------|--------|-----
A     | 100   | 20180800| 데이지 | F      | 24
A     | 100   | 20190123| 다나   | F      | 23
B     | 80    | 20170001| 조이   | F      | 25
B     | 80    | 20170020| 앤드류 | M      | 26
C     | 60    | NULL    | NULL   | NULL   | NULL
D     | 40    | 20201000| 스카이 | M      | 22
E     | 20    | NULL    | NULL   | NULL   | NULL
F     | 0     | NULL    | NULL   | NULL   | NULL

 

3.3. UNION 

• 설명: 두 테이블의 모든 데이터를 포함하며, 조건에 맞지 않는 경우 NULL로 채움.
• MySQL 대안: LEFT JOIN과 RIGHT JOIN을 UNION으로 결합.
• 예제 (대안):

-- UNION 
-- 두 테이블의 모든 데이터를 포함하며, 조건이 맞니 않는 경우 NULL 채움 
-- MYSQL 은 FULL JOIN 지원하지 않기 때문에 UNION 으로 결합. 
-- LEFT JOIN 과 RIGHT JOIN 을 UNOIN 으로 결합 
use school; 

select s.no, s.name, s.gender, g.score, g.grade 
from tb_student as s 
left join tb_grade as g 
	on s.grade = g.grade
union
select s.no, s.name, s.gender, g.score , g.grade
from tb_student as s 
right join tb_grade as g 
	on s.grade = g.grade
-- where s.grade is null ;   기본 값 
-- where s.grade is not null ;

---

4. JOIN 사용 시 주의사항

1. ON 조건 필수:
   • MySQL에서 INNER JOIN이나 OUTER JOIN 사용 시 ON 조건을 지정하지 않으면 CROSS JOIN 결과(모든 조합)가 출력됨.
   • 예: 학생 5명과 등급 6개가 조합되면 30행 반환.
2. 외래 키와 기본 키 매핑:
   • JOIN은 보통 외래 키와 기본 키를 기준으로 수행. 외래 키가 참조하는 열은 기본 키 또는 고유 키여야 데이터 무결성 보장.
3. 성능 고려:
   • 대량 데이터에서 JOIN은 성능 저하를 유발할 수 있으므로, 적절한 인덱스 생성 권장(예: tb_student.grade에 인덱스 추가).

---

연습 문제

CREATE TABLE tb_grade (
    grade CHAR(1) PRIMARY KEY,
    score INT
);

CREATE TABLE tb_student (
    no INT NOT NULL PRIMARY KEY,
    name VARCHAR(20) NOT NULL,
    gender ENUM('F', 'M') NOT NULL,
    age INT,
    grade CHAR(1),
    FOREIGN KEY (grade) REFERENCES tb_grade(grade)
);

-- 테이블 구조 확인 및 기존 데이터 삭제처리 
delete from tb_student 

INSERT INTO tb_student (no, name, gender, age, grade) VALUES
    (20170001, '조이', 'F', 25, 'B'),
    (20170020, '앤드류', 'M', 26, 'B'),
    (20180800, '데이지', 'F', 24, 'A'),
    (20190123, '다나', 'F', 23, 'A'),
    (20201000, '스카이', 'M', 22, 'D'),
    (20210001, '제임스', 'M', 21, NULL);  -- 등급이 없는 학생 추가

---

5. 연습 문제 풀어 보기

문제 1: INNER JOIN - 학년이 'A' 또는 'B'인 학생 조회

• 설명: INNER JOIN을 사용하여 학점이 'A' 또는 'B'인 학생의 이름, 나이, 학년, 점수를 조회하세요.
• 힌트:
  • tb_student와 tb_grade를 INNER JOIN으로 결합.
  • ON 조건으로 학년(grade) 매핑.
  • WHERE 절에서 학년 조건 추가.

select s.name, s.age, s.grade, g.score
from tb_student as s
inner join tb_grade as g
	on s.grade = g.grade
where s.grade IN ('A', 'B');

 

문제 2 LEFT JOIN - 모든 여학생 조회

• 설명: LEFT JOIN을 사용하여 모든 여학생(gender = 'F')의 이름, 학년, 점수를 조회하세요. 점수가 없는 경우에도 학생 정보가 표시되어야 합니다.
• 힌트:
  • tb_student를 기준 테이블로 설정.
  • WHERE 절에서 성별 조건 추가.
  • LEFT JOIN으로 점수가 없어도 학생 데이터 포함.

select s.name, s.grade, g.score
from tb_student as s
left join tb_grade as g
on s.grade = g.grade
where s.gender = 'F';

 

문제 3: LEFT JOIN - 나이가 24세 이상인 학생 조회

• 설명: LEFT JOIN을 사용하여 나이가 24세 이상인 학생의 이름, 나이, 학년, 점수를 조회하세요. 점수가 없는 경우에도 학생 정보가 표시되어야 합니다.
• 힌트:
  • tb_student를 기준 테이블로 설정.
  • WHERE 절에서 나이 조건 추가.
  • LEFT JOIN으로 점수가 없어도 학생 데이터 포함.

select s.name, s.age, s.grade, g.score
from tb_student as s
left join tb_grade as g
on s.grade = g.grade
where age >= 24;

 

문제 4: RIGHT JOIN - 점수가 60 이상인 등급 조회

• 설명: RIGHT JOIN을 사용하여 점수가 60 이상인 등급과 해당 등급을 가진 학생의 이름, 학년, 점수를 조회하세요. 학생이 없는 등급도 표시되어야 합니다.
• 힌트:
  • tb_grade를 기준 테이블로 설정.
  • WHERE 절에서 점수 조건 추가.
  • RIGHT JOIN으로 학생이 없어도 등급 데이터 포함.

select s.name, s.grade, g.score
from tb_student as s
right join tb_grade as g
on s.grade = g.grade 
where g.score >= 60;

 

문제 5: INNER JOIN과 LEFT JOIN 비교

• 설명: INNER JOIN과 LEFT JOIN을 각각 사용하여 모든 학생의 이름, 학년, 점수를 조회하고 결과를 비교하세요.
• 힌트:
  • 두 쿼리를 각각 실행.
  • INNER JOIN은 매칭 데이터만, LEFT JOIN은 모든 학생 포함.

select s.name, s.grade, g.score
from tb_student as s
inner join tb_grade as g
on  s.grade = g.grade;

select s.name, s.grade, g.score
from tb_student as s
left join tb_grade as g
on  s.grade = g.grade;

 

문제 6: RIGHT JOIN - 기준 테이블 변경

• 설명: RIGHT JOIN을 사용하여 tb_grade를 기준으로 모든 등급과 해당 학생의 이름, 나이를 조회하세요. 학생이 없는 등급도 표시되어야 합니다.
• 힌트:
  • tb_grade를 기준 테이블로 설정.
  • 필요한 열만 선택.

select g.grade, s.name, s.age
from tb_student as s
right join tb_grade as g
on s.grade = g.grade;

---

/*
select g.grade, s.name, s.age
from tb_student as s <-- left join 일 경우 from 뒤 테이블을 기준으로 함
(right,left) join tb_grade as g <-- right join 일 경우 join 뒤 테이블을 기준으로 함
on s.grade = g.grade;
*/