일반적으로 PK(primary key)와 FK(foreign key)를 활용(묶어주기 위해서 사용,필수x)
JOIN 종류
CROSS JOIN(잘 사용X), EQUI JOIN*(등가조인, 내부조인)
NON EQUI JOIN(거의 사용X - 교재에만 수록)
SELF JOIN, OUTER JOIN*
CROSS JOIN(사용X)
CROSS JOIN은 카다시안 곱을 수행 후 결과를 반환
카다시안 곱 : 짝을 지어줄 경우 모든 경우의 수를 계산
CROSS JOIN은 양 테이블의 모든 컬럼의 조합을 보여주므로 의미있는 데이터를 뽑아내기 어려움
select * from emp; -- 10
select * from dept; -- 4
-- FROM [tableA] CROSS JOIN [tableB]
select e.ename, d.deptname from emp e cross join dept d; -- 40
-- CROSS JOIN은 생략가능
select e.ename, d.deptname from emp e, dept d;
EQUI JOIN*
가장 많이 사용 / (=)을 사용
등가조인*
양쪽 테이블에 동등하게 있어야만 보여줌
-- emp와 dept를 합쳐서 각각의 ename과 deptname을 가져와라
-- emp의 특정 데이터가 가지고 있는 deptno와 dept의 특정 데이터가 가지고 있는 deptno가 같은 것만 보여줘
select e.ename, d.deptname from emp e, dept d where e.deptno = d.deptno;
내부조인
테이블과 테이블 사이에 inner join 키워드를 넣음
조인의 조건을 where가 아닌 on으로 넣음
inner 생략 가능, on 대신 using을 사용할 수 있음
using은 () 안에 서브쿼리가 들어갈 수 있음
select e.ename, d.deptname from emp e inner join dept d on e.deptno = d.deptno;
select e.ename, d.deptname from emp e join dept d using(deptno);
NATURAL JOIN
양 테이블에 공통 컬럼을 기준으로 합치는 것은 자연스러운(natural) 것 이므로 조건을 주지 않음
읽는 사람의 입장에서는 알아보기 어려워서 잘 사용하지 않음
select e.ename, d.deptname from emp e natural join dept d;
외부조인(outer join)
등가조인은 두개의 테이블에 모두 데이터가 존재해야만 보여줌
OUTER JOIN은 한 테이블에만 데이터가 더 있을 경우 모든 데이터를 보여줌
FROM [tableA] [LEFT|RIGHT|FULL] OUTER JOIN [tableB] ON 조건절;
LEFT OUTER JOIN : 왼쪽 테이블을 기준으로 더 많은 데이터를 보여줌
RIGHT OUTER JOIN : 오른쪽 테이블을 기준으로 더 많은 데이터를 보여줌
FULL OUTER JOIN(mariadb에는 없음) : 양쪽 테이블에 각각 더 있는 값을 보여줌
OUTER 는 생략가능
RIGHT OUTER JOIN
-- 오른쪽(dept)에 사용하지 않은 데이터가 있어 더 보여줘야 하므로 right outer join을 사용
-- dept의 deptno 3, 5는 매칭되는 emp 값이 없어 null로 표시
select d.deptno, e.ename, d.deptname from emp e right join dept d on e.deptno = d.deptno;
LEFT OUTER JOIN
-- dept 테이블에 없는 값을 emp 테이블에 추가(부모자식 관계라 불가능)
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; -- FK 삭제
insert into emp(ename, job, deptno, hiredate) values('kim','manager',6,str_to_date('14/06/02','%Y/%m/%d'));
select e.deptno, e.ename, d.deptname from emp e left outer join dept d on e. deptno = d.deptno;
FULL OUTER JOIN
양쪽에 없는 걸 다 보여주고 싶은 경우 사용(mariadb, mysql에서는 지원X)
union을 이용하면 가능
자기조인(self join)
등가조인과 같음
다만 두개의 테이블이 아닌 하나의 테이블을 비교
자기조인을 하면 두 데이터 간에 카다시안 곱을 해줌
select
a.deptno
,a.ename
,b.job
from emp a, emp b where a.deptno = b.deptno;
이하 생략
SET
다수의 테이블을 이용해 집합연산을 수행
합집합(UNION, UNION ALL), 교집합(INTERSECT), 차집합(NOT IN)이 있음
select deptno from emp
union
select deptno from dept;
-- union을 통해서 full outer join 효과를 줄 수 있음
select e.deptno, e.ename, d.deptname from emp e left outer join dept d on e. deptno = d.deptno
union
select d.deptno, e.ename, d.deptname from emp e right join dept d on e.deptno = d.deptno;
UNION ALL(합집합 - 중복제거X)
select deptno from emp
union all
select deptno from dept order by deptno;
INTERSECT(교집합)
두 쿼리 사이에 중복된 내용만 가져옴
select deptno from emp
intersect
select deptno from dept order by deptno;
MINUS(차집합) - NOT IN
select distinct deptno from emp; -- 1,2,4,6
select distinct deptno from dept; -- 1,2,3,4,5
-- emp - dept (emp 차집합)
select deptno from emp where deptno not in(select deptno from dept);
-- dept - emp(dept 차집합)
select deptno from dept where deptno not in(select deptno from emp);
-- emp와 dept를 합집합한 내용을 emp와 교집합
(select deptno from dept union all select deptno from emp)
intersect
select deptno from emp;
-- union 보다 distinct를 사용해 중복을 제거하는 것이 빠름
select distinct u.deptno
from
(select deptno from dept
union all
select deptno from emp order by deptno) u;
