MySQL - INNER JOIN(CROSS JOIN), OUTER JOIN(LEFT JOIN, RIGHT JOIN), UNION

• JOIN은 두 개 이상의 테이블에서 관련된 데이터를 결합하여 새로운 결과를 생성하는 데이터베이스 연산이다.
• 데이터베이스는 중복을 최소화하기 위해 데이터를 여러 테이블에 나눠 저장하는데 실제 사용 시에는 분산된 데이터를 통합해야 할 때가 많기 때문에 JOIN이 필요하다.
• JOIN은 테이블 간 공통 열(예: 외래 키와 기본 키)을 기준으로 데이터를 결합한다.
• MySQL에서는 INNER JOIN, LEFT JOIN, RIGHT JOIN을 지원하며, FULL OUTER JOIN은 지원하지 않는다.

 

1. INNER JOIN

• 두 테이블에서 조건에 맞는 데이터만 결합하고 조건에 맞지 않는 데이터는 제외된다.
• MySQL에서 JOIN, INNER JOIN, CROSS JOIN은 기본적으로 동일한 결과를 생성하지만 ON 조건 여부에 따라 결과가 달라진다.
• ON 조건 없이 사용할 때는 INNER JOIN을 CROSS JOIN 이라고 부른다.

2. OUTER JOIN

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

2-1. LEFT JOIN (LEFT OUTER JOIN)

• 왼쪽 테이블을 기준으로 결합한다.
• 조건에 맞는 데이터가 없으면 오른쪽 테이블의 열은 NULL로 채워진다.

2-2. RIGHT JOIN (RIGHT OUTER JOIN)

• 오른쪽 테이블을 기준으로 결합한다.
• 조건에 맞는 데이터가 없으면 왼쪽 테이블의 열은 NULL로 채워진다.

3. UNION

• 두 테이블의 모든 데이터를 포함하며, 조건이 맞지 않는 경우 NULL로 채운다.
• LEFT JOIN과 RIGHT JOIN을 UNION으로 결합한다.
• MySQL 은 FULL JOIN을 지원하지 않기 때문에 UNION 으로 결합한다.
  
  

4. JOIN 사용 시 주의사항

•  MySQL에서 INNER JOIN이나 OUTER JOIN 사용 시 ON 조건을 지정하지 않으면 CROSS JOIN 결과(모든 조합)가 출력된다.
• JOIN은 보통 외래 키와 기본 키를 기준으로 수행 하며 외래 키가 참조하는 열은 기본 키 또는 고유 키여야 데이터 무결성을 보장한다.
• 대량 데이터에서 JOIN은 성능 저하를 유발할 수 있으므로, 적절한 인덱스 생성 권장한다(예: tb_student.grade에 인덱스 추가).

---

• 테이블 생성하기

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;

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');

 

• CROSS JOIN

select * 
from tb_student
join tb_grade;

 

• INNER JOIN

-- inner join 을 사욜할 때 on 이라는 조건을 설정한다.
-- no | name | gender | age | grade | score
select *
from tb_student
inner join tb_grade
on tb_student.grade = tb_grade.grade; 

select tb_student.no, tb_student.name, tb_student.gender, tb_student.age, tb_student.grade,
		tb_grade.score
from tb_student
inner join tb_grade
on tb_student.grade = tb_grade.grade;

 

• LEFT JOIN

-- left join 문법 사용
-- tb_student 과 tb_grade 테이블
select s.no, s.name, s.grade, g.score
from tb_student as s
left join tb_grade as g 
	on s.grade = g.grade;

 

• RIGHT JOIN

-- right join
-- 오른쪽 테이블 기준으로 결합(조건에 맞는 데이터가 없으면 왼쪽 테이블은 null 로 채워짐)
-- 사용 결과 예시: 모든 등급을 조회하되, 해당 등급을 가진 학새잉 없는 경우도 포함이 됨
select s.no, s.name, s.grade, g.score
from tb_student as s
right 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
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;

 

---

• 데이터생성 후 실습하기

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;
select * from tb_student;
select * from tb_grade;

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);  -- 등급이 없는 학생 추가

 

• INNER JOIN 학년이 'A' 또는 'B'인 학생 조회하기

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

 

• LEFT JOIN 모든 여학생 조회하기

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

 

• LEFT JOIN 나이가 24세 이상인 학생 조회하기

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 s.age >= 24;

 

• RIGHT JOIN 점수가 60 이상인 등급 조회

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;

 

• 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;

 

• RIGHT JOIN 기준 테이블 변경하기

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

 

---

• employees 테이블로 JOIN 실습하기

select *
from dept_emp;

select *
from departments;

select de.emp_no, de.dept_no, d.dept_name
from dept_emp as de
inner join departments as d
on de.dept_no = d.dept_no;


select * from dept_manager;
select * from titles;

select m.dept_no, m.emp_no, t.title
from dept_manager as m
inner join titles as t
on m.emp_no = t.emp_no;


select d.dept_no, de.dept_no, d.dept_no
from departments as d
right join dept_emp de
on d.dept_no = de.dept_no;

select de.emp_no, de.dept_no, d.dept_name
from dept_emp as de
left join departments as d
on de.dept_no = d.dept_no;