MySQL - join (+ case 조건문)

join = 테이블을 설계할 때 데이터의 무결성을 유지하고, 중복되는 데이터 처리를 위해 사용된다.

 

-- students 테이블 생성 

create table students(
	id int auto_increment primary key,
    first_name varchar(100)
);

--papers 테이블 생성 

create table papers(
	title varchar(100),
    grade int,
    student_id int,
    foreign key(student_id) references students(id)
    on delete cascade 
);

foreign key / references

student_id는 papers테이블 내에 있던 자료가 아니라 students테이블에 있는 id 컬럼을 불러와 동일하게 사용한다는 뜻이다.

 

on delete cascade - paper에서 컬럼이나 데이터를 삭제하지 않았음에도 student_id가 삭제되면 paper에서도 삭제됨을 뜻함.

 

-- 테이블에 데이터입력

INSERT INTO students (first_name) VALUES 
('Caleb'), ('Samantha'), ('Raj'), ('Carlos'), ('Lisa');

INSERT INTO papers (student_id, title, grade ) VALUES
(1, 'My First Book Report', 60),
(1, 'My Second Book Report', 75),
(2, 'Russian Lit Through The Ages', 94),
(2, 'De Montaigne and The Art of The Essay', 98),
(4, 'Borges and Magical Realism', 89);

students 테이블과 papers테이블을 join으로 합칠 것이다.

select * 
from students
join papers
	on students.id = papers.student_id;
    
-- 테이블과 컬럼의 이름을 반드시 적어줘야함.

여기서 테이블의 이름을 계속 쓰지 않도록 테이블명을 변수처리 할 수 있다.

select s.first_name, p.title, p.grade
from students s
join papers p
   on s.id = p.student_id
order by p.grade desc;

null data 처리

select s.first_name, ifnull(p.title,'missing') as title ,
ifnull(p.grade,'0') as grade
from students s
join papers p
   on s.id = p.student_id
order by p.grade desc;

-- ifnull을 사용 시 괄호 속 앞쪽에는 데이터, 뒤에는 null을 처리할 문자를 입력한다

case /end 조건문

select s.first_name, ifnull( avg(p.grade) , 0) as average,
	case 
		when ifnull( avg(p.grade) , 0) >= 70
			then 'PASSING'
		else 'Failing'
	end  as 'passing_status'
from students s
left join papers p
   on s.id = p.student_id
group by s.first_name
order by p.grade desc;

조건문에 따른 데이터를 쿼리할 수 있다.

 

-- data insert 에러 발생시 조건
select * from students;
insert into students(first_name)
values('Mike');

insert into papers(title, grade, student_id)
values('The Great paper', 83,6);

-- *오류가 발생함.
insert into papers(title, grade, student_id)
values('The Worst paper', 91,7);
-- papers 테이블 생성시에 student id를 받아와 만들어졌는데, 
-- on delete cascade 때문에 student_id가 없다면 papers에서도 새로운 id를 입력할 수 없다.

 

1. 기본 JOIN(= INNER JOIN)

- 기본적으로 교집합

 

 

2. LEFT JOIN

 - 왼쪽 테이블을 중심으로 오른쪽의 테이블을 매치시킨다.

 - 왼쪽 테이블의 한개의 레코드에 여러개의 오른쪽 테이블 레코드가 일치할 경우, 
   해당 왼쪽 레코드를 여러번 표시하게 된다.

 - 왼쪽은 무조건 표시하고, 매치되는 레코드가 오른쪽에 없으면 NULL을 표시한다.

 

3. RIGHT JOIN

 - 오른쪽 테이블을 중심으로 왼쪽 테이블을 매치시킨다.

 - LEFT JOIN에서 방향을 오른쪽으로만 바꾼것이므로, 역시 해당 레코드가 여러번 표시되거나, NULL이 표시된다.

 

4. OUTER JOIN : 조건에 부합하지 않는 행까지도 포함시켜 결합하는 것

 - 기본적으로 LEFT, RIGHT 또는 FULL이 OUTER JOIN 이다.

 - FULL JOIN 은 사용할 일이 없으며, ODBC에 따라 지원하지 않는 경우도 있다.

 

 

 

 

참고 - superman28.tistory.com/23

JOIN 완전 정리 (INNER JOIN, OUTER JOIN, LEFT JOIN, RIGHT JOIN)