[MySQL] JOIN

JOIN

:  둘 이상의 테이블을 연결하여 데이터를 검색

 

• Primary Key 및 Foreign Key 값을 사용하여 조인
• 두 개의 테이블을 SELECT문장 안에서 조인하려면 적어도 하나의 열(컬럼)이 두 테이블 사이에서 공유되어야 함

---

 

[equi join]

단순조인, 내부 조인

: 조인 조건에서 '='를 사용하여 값들이 정확하게 일치하는 경우에 사용

주로 PK, FK 관계 작성할 때 사용

 

▶ 사원명(emp), 급여(emp), 부서 코드(emp, dept). 부서명(dept)을 선택하라

- , 사용(권장하지 않음)
select ename, sal, emp.deptno, dname
from emp, dept
where emp.deptno = dept.deptno;

-- join 사용
select ename, sal, dept.deptno, dname
from emp join dept
on emp.deptno = dept.deptno;

-- join, 변수 사용
select e.ename, e.sal, e.deptno, d.dname
from emp e join dept d
on e.deptno = d.deptno;

deptno를 통해 join

 

 

▶ 사원번호, 담당업무, 급여, 부서명, 부서위치를 선택하라
    단 30번 부서의 사원은 제외하고 급여는 10% 인상한 급여와 지급액(급여+보너스) 출력하라

select empno, job, (sal*1.1), sal+ifnull(comm,0), dept.dname, dept.loc
from emp join dept
on emp.deptno = dept.deptno and dept.deptno != 30;

-- where 사용도 가능
select empno, job, (sal*1.1), sal+ifnull(comm,0), dept.dname, dept.loc
from emp join dept
on emp.deptno = dept.deptno
where dept.deptno != 30;

 

 

[from절 서브 쿼리 + join문]

select a.empno, a.지급액 from
(select empno, job, (sal*1.1) '지급액', sal+ifnull(comm,0), dept.dname, dept.loc
from emp join dept
on emp.deptno = dept.deptno and dept.deptno != 30) a;

 

 

▶ 사원번호, 사원명, 담당업무, 급여, 부서 코드, 부서명을 선택, 사원명 오름차순으로 정렬

-- manager인 사원의 평균 급여보다 많은 사원을 선택
select avg(sal) from emp where job = 'manager';

-- 사원번호, 사원명, 담당업무, 급여, 부서코드, 부서명을 선택, 사원명 오름차순으로 정렬
select e.empno, e.ename, e.job, e.sal, e.deptno, d.dname
from emp e join dept d
on e.deptno = d.deptno
and e.sal>(select avg(sal) from emp where job = 'manager')
order by e.ename asc;

 

 

▶ salesman의 사원번호, 이름, 급여, 부서명, 근무지를 출력하라

select e.empno, e.ename, e.sal, d.dname, d.loc
from emp e join dept d
on e.deptno = d.deptno and job = 'salesman';

 

---

[non equijoin]

: 조인 조건이 정확하지 않을 때, = 이 아닌 다른 연산 기호로 만들어지는 경우에 사용.

주로 학점, 날짜 등에 사용 (BETWEEN AND, IS NULL, IS NOT NULL, IN, NOT IN)

 

▶ 사원 급여가 salgrade 테이블의 최저급여, 최고 급여 사이의 범위에 존재하는 경우만 선택

select e.empno, e.ename, e.sal, s.grade, s.losal, s.hisal
from emp e join salgrade s
on e.sal between s.losal and s.hisal;

-- where 사용해도 됨
select e.empno, e.ename, e.sal, s.grade, s.losal, s.hisal
from emp e, salgrade s
where e.sal between s.losal and s.hisal;

salgrade 테이블

 

 

▶ emp 테이블에서 사원명, 급여, 호봉(grade)을 선택하되, 담당업무가 manager이거나
    salesman인 사원 중 사원명에 A가 포함된 사원을 선택하라

select e.ename, e.sal, s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
and (e.job='manager' or e.job='salesman') and e.ename like '%A%';

-- 위와 동일 in 사용
select e.ename, e.sal, s.grade
from emp e join salgrade s
on e.sal between s.losal and s.hisal
and e.job in ('manager' , 'salesman') and e.ename like  '%A%';

 

 

---

[self join]

:  자체적으로 테이블을 조인할 필요가 있는 경우, 같은 테이블끼리의 조인

 

▶ 사원명, 부서명, 급여, 매니저명, 매니저 부서명, 매니저 급여를 선택하라.

select e1.ename '사원명', d1.dname '사원 부서명', e1.sal '사원 급여',
			e2.ename '매니저명 ', d2.dname '매니저 부서명' , e2.sal ' 매니저 급여'
from emp e1 join emp e2
on e1.mgr = e2.empno
join dept d1 on e1.deptno = d1.deptno
join dept d2 on e2.deptno = d2.deptno;

SMITH의 매니저 FORD 확인

SMITH의 매니저 정보 선택 확인

 

 

▶ emp 테이블에서 "누구의 관리자는 누구이다"는 내용을 출력하라

select concat(e1.ename, '의 관리자는 ', e2.ename,'이다.') '결과'
from emp e1 join emp e2
on e1.mgr = e2.empno;

---

[outer join]

: 한쪽 테이블에 일치하는 행이 없으면 다른 쪽 테이블을 NULL로 하여 그 값을 보여줌

null 데이터 발생하는 테이블이 오른쪽에 있으면 left outer join
null 데이터 발생하는 테이블이 왼쪽에 있으면 right outer join

 

-- 40, 50은 안 나옴
select distinct e.deptno, d.deptno
from dept d join emp e
on d.deptno = e.deptno;

 

-- emp에는(왼쪽) 부서 번호가 40, 50번 부서가 없으니까 right outer join
select ename, e.deptno, d.dname
from emp e right outer join dept d
on e.deptno = d.deptno;

-- emp에는(오른쪽) 부서 번호가 40, 50번 부서가 없으니까 right outer join
select distinct e.deptno, d.deptno
from dept d  left outer join emp e
on d.deptno = e.deptno;

---

[다중 조인]

 

▶ 사원명, 급여, 부서 코드, 부서명, 호봉을 선택하라.

select e.ename, e.sal,  d.dname, s.grade
from emp e join dept d
on e.deptno = d.deptno
join salgrade s
on e.sal between s.losal and s.hisal;

 

 

▶ 사원번호, 사원명, 입사일, 급여, 부서명, 호봉, 호봉의 최고 급여를 선택하되
    급여가 2500 이상인 사원 급여 순으로 내림차순 정렬하라

select * from emp where sal>=2500;
select e.empno, e.ename, e.hiredate, e.sal, d.dname, s.grade, s.hisal
from emp e join dept d
on e.deptno = d.deptno and e.sal>=2500
join salgrade s
on e.sal between s.losal and s.hisal
order by e.sal desc;

 

---

[natural join]

: 두 테이블에 같은 필드명을 가지는 컬럼은 모두 알아서 조인해줌 (Equijoin과 동일)

select ename, sal, d.deptno, dname from emp natural join dept d;

join ~ using

: natural join에서 동일한 컬럼이 두 개 이상일 경우 JOIN~USING문장으로 조인되는 컬럼을 제어

select ename, sal, d.deptno, d.dname from emp join dept d using (deptno);