Day - 35 [Maria DB] JOIN

JOIN

• 서브쿼리 만을 사용 한다면 쿼리가 굉장히 복잡해 진다.
• 그래서 우리는 JOIN 을 활용한다.

 

---

JOIN 개념

• 둘 이상의 테이블을 연결하여 데이터를 검색하는 방법
• 두 개의 테이블을 select 문장으로 조인하려면 적어도 하나의 컬럼이 공통으로 존재해야 한다.
• 그래서 일반적으로 pk 와 fk 를 사용한다. (공통된 컬럼만 있어도 되는데 pk,fk 는 너무 확실하니까)

 

JOIN 의 종류

• CROSS JOIN, EQUI JOIN(등가 조인, 내부조인)
• NON EQUI JOIN (거의 사용하지 않음 : 일반적으로 교재에는 수록 되어 있음)
• SELF JOIN, * OUTER JOIN

 

0. CROSS JOIN (X)

• CROSS JOIN 은 카다시안 곱을 수행 한다.
• 그 결과를 반환 한다.
• FROM [tableA] CROSS JOIN [tableB]
• 어느 테이블에 뭘 가져왔는지가 JOIN 의 핵심이다.
• CROSS JOIN 은 생략 가능 하다.

select * from emp; -- 10
select * from dept; -- 4

select e.ename, d.deptname from emp e cross join dept d;

-- CROSS JOIN 은 생략 가능 하다.
select e.ename, d.deptname from emp e , dept d;

• CROSS JOIN 은 양 테이블이ㅡ 모든 컬럼의 조합을 부여주므로 의미있는 데이터를 뽑아내기가 어렵다.
• 안그래도 복잡한데 더 복잡하게 한다.
• 그래서 조건을 추가하여 아래 조인들을 수행 한다.

 

1. equi join

가장 일반적으로 사용하는 조인 (=) 을 사용한다.

1) 등가 조인
- emp 와 dept 를 합쳐서 각각의 ename 과 deptname 을 가져와라
- emp 의 특정 데이터가 가지고 있는 deptno 와 dept의 특정 데이터가 가지고 있는 deptno 가 같은것만 보여줘

select e.ename, d.deptname from emp e , dept d 
	where e.deptno = d.deptno;
    
-- 양쪽 테이블에 동등하게 있어야만 보여 준다.
insert into dept(deptno,deptname,loc)values(5,'dev02','FLOLIDA');
select * from dept;

2) 내부 조인
-- 테이블과 테이블 사이에 inner join 키워드를 넣는다.
-- 조인의 조건을 where 가 아닌 on 으로 넣는다.

select e.ename, d.deptname from emp e inner join dept d 
	on e.deptno = d.deptno;
    
-- inner 는 생략 가능, on 대신 using 을 사용 할 수 있다.
-- using 은 () 안에 서브쿼리가 들어갈 수 있다.

select e.ename, d.deptname from emp e join dept d using (deptno);

3) NATURAL JOIN
-- 양 테이블에 공통된 칼럼을 기준으로 합치는 것은 자연스러운(natural) 것이므로 조건을 주지 않는다.
-- 남이 보기에 가독성이 좋아야 하는데, 가독성이 좋지 않기 때문에 잘 사용하지 않는다.
select e.ename, d.deptname from emp e natural join dept d;

 

2. 외부 조인

• 등가 조인은 두개의 테이블에 모두 데이터가 존재해야만 보여 준다.
• 어느 한 테이블에만 데이터가 존재 할 경우 등가조인은 보여 줄 수 가 없다.
• 이때 특정 테이블에 데이터가 더 많은 경우 사용하는 OUTER JOIN 이 있다.

FROM [tableA] [LEFT|RIGHT|FULL] OUTER JOIN [tableB] ON 조건절

-- LEFT OUTER JOIN : 왼쪽 테이블을 기준으로 더 많은(데이터 양 x 매칭되지 않고 남은) 데이터를 보여줌
-- RIGHT OUTER JOIN : 오른쪽 테이블을 기준으로 더 많은 데이터를 보여줌
-- FULL OUTER JOIN : 양쪽 테이블에 각각 더 있는 값을 보여줌

 

RIGHT OUTER JOIN

-- outer 는 생략 가능
-- 오른쪽(dept) 에 사용하지 않은 데이터가 있어 더 보여줘야 하므로... right outer join 을 사용
-- dept 의 deptno 3 과 5번은 매칭되는 emp 값이 없어 null 로 표시
select d.deptno , e.ename , d.deptname from emp e right outer join dept d 
	on e.deptno = d.deptno;

 

DEPT 테이블에 없는 값을 EMP 테이블에 추가

부모 자식 관계라 안되는데=> 부모 자식 관계를 삭제

-- dept 테이블에 없는 값을 emp 테이블에 추가(부모자식 관계라 안될거임)
-- Cannot add or update a child row: a foreign key constraint fails
insert into emp (ename,job,deptno,hiredate)
	values('kim','manager',6,str_to_date('14-06-02','%Y-%m-%d'));

-- 부모자식 관계를 삭제(FK 삭제)
-- 부모 자식 관계 함부러 끊지 않기
-- ALTER TABLE [테이블명] DROP CONSTRAINT [제약조건 이름]
select * from information_schema.table_constraints 
	where TABLE_NAME = 'emp';

alter table emp drop constraint emp_ibfk_1;

 

LEFT OUTER JOIN

select * from emp; -- 3,5 번 없고
select * from dept; -- 6번이 없다.

-- FROM [tableA] [LEFT|RIGHT|FULL] OUTER JOIN [tableB] ON 조건절
select e.deptno, e.ename , d.deptname from emp e left outer join dept d 
	on e.deptno = d.deptno;


-- dept 는 6번이 없고, emp 는 3번, 5번이 없다.
-- 이 양쪽에 없는 걸 다 보여주고 싶을 경우 full outer join 을 사용 한다.(maria db 와 mysql 에서는 지원하지 않음)
-- union 을 이용하여 같은 효과를 낸다.

 

자기 조인(SELF JOIN)

-- 자기 조인(self join)
-- 등가 조인과 같다.
-- 다만 두개의 테이블이 아닌 하나의 테이블을 대상으로 한다.
-- 자기 조인을 하면 두 데이터간에 카다시안 곱을 해준다.
select 
	a.deptno
	,a.ename
	,b.job 
from emp a, emp b where a.deptno = b.deptno;