[MySQL] join 정복하기

혼자 공부한거라 불친절 한 글입니다 !! 질문은 댓글로 !!!

 

join 먼저 할때는 일단 cross join 을 통해 전체 모습을 확인해보는게 좋다 

select 는 반복문이라고 생각하면 좋다 (모든 row 를 검색해보는 그런것)

 

select * from emp,dept;

 

inner join -> join 되는 것만 나옴 !!!

select * from emp,dept where emp.deptno = dept.deptno;

left join

select * from emp left join dept on emp.deptno = dept.deptno;

가명 사용해보기

select * from emp e inner join dept d on e.deptno = d.deptno;

 

 

가명 쓸때 주의할 점 : 띄어쓰기를 사용하게 되면 문자열로 묶어줘야한다

select e.*,d.dname 부서이름,d.loc '부서 위치' from emp e inner join dept d on e.deptno = d.deptno;

 

 

self join

왼쪽엔 사원 오른쪽엔 상사

select * from emp e left join emp m on e.mrg=m.empno;

 

select e.* , m.ename, m.job from emp e left join emp m on e.mgr =m.empno order by e.mgr;

출력 결과를 줄여주면 좀 더 가독성 좋게 볼 수 있다. 

 

 

하지만 ! self join 은 무한 루프에 빠질 수 있다 ! 조심조심

이렇게 쪼인쪼인쪼인쪼인쪼인 해서 무한루프에 빠질 수 있다 !

 

 

accounting 부서 소속 사원의 이름과 입사일 출력

(서브쿼리 사용해보기)

select * from emp where deptno in (select deptno FROM dept where dname = 'accounting');

서브쿼리가 먼저 동작 하고 그 다음에 메인 쿼리가 동작한다 

join 사용해보기 

select * from emp e inner join dept d on  d.dname = 'accounting';

 

dallas 에 사는 사람 출력 (emp+dept)

 

직급이 manager 인 사람 

 

 

급여에 따른 등급 출력하기 

잘 모르겠으면 일단 cross join 부터 

 select * from emp cross join salgrade;

 

losal 과 hisal 사이의 값으로 구해준다

select * from emp left join salgrade on emp.sal between salgrade.losal and salgrade.hisal;

sal 과 comm 더한 값의 등급 출력하기 (null 일 수 있으니 ifnull 을 써주면 된다)

select * from emp left join salgrade on emp.sal+IFNULL(emp.comm,0) between salgrade.losal and salgrade.hisal;

 

 

등급이 3등급 이상인 애 출력

select e.*,g.grade from emp e left join salgrade g on e.sal between g.losal and g.hisal

    -> where g.grade >= 3;

 

smith 씨랑 같은 근무지에서 일하는 사람을 출력하기

select * from emp where deptno =(select deptno from emp where ename='smith');

 

 

평균 급여보다 작은 사람들 

select * from emp where sal < (select AVG(sal) from emp);

 

 

scott(7788) 과 같은 부서에서 일하는 사람 

select * from emp where deptno=(select deptno from emp where empno='7788');

 

martin(7654) 와 동일한 job 인 사람들 

select * from emp where job = (select job from emp where empno=7654) and empno <> 7654;

empno <>7654 를 하면 martin을 제외하고 출력 된다

 

SALES(영업부) 부서에서 근무하는 모든 사원의 이름과 급여를 출력하시오.

select ename,sal from emp where deptno = (select deptno from dept where dname='sales');

 직속상관이 KING인 사원의 이름과 급여를 출력하시오.

select ename,sal from emp where mgr = (select empno from emp where ename='king');

select e.* ,m.ename from emp e inner join emp m on e.mgr= m.empno where m.ename='king' order by m.ename;

 

다중행 서브쿼르 사용을 위해서는 (서브쿼리의 결과가 여러개가 나오는경우) in 을 사용해야한다 

select * from emp where deptno in (select distinct deptno from emp where IFNULL(comm,0)>0);

select distinct deptno from emp where IFNULL(comm,0)>0 의 결과가 여러개가 나오기 때문에 in 을 사용해 처리해줘야한다 

 

 

급여를 3000 이상 받는 사원이 소속된 부서와 동일한 부서에서 근무하는 사원 이름, 급여, 부서명을 출력하세요

select ename from emp where deptno in (select distinct deptno from emp where sal >= 3000);

 

 

 

부서별로 가장 급여를 많이 받는 사원의 정보(사원 번호, 사원 이름, 급여, 부서 번호)를 출력하시오.

그루핑을 하면 집합함수를 쓸 수 있다.  집합함수만 사용할 수 있다 

select * from emp  where sal in (select MAX(sal) from emp where deptno is not null group by deptno);

 

 

30번 소속 사원들 중에서 급여를 가장 많이 받는 사원보다 더 많은 급여를 받는 사람의 이름, 급여를 출력하세요

ALL. => and 연산 

1. 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 모든 값이 일치하면 참입니다.
2. 찾아진 값에 대해서 AND 연산을 해서 모두 참이면 참이 되는 셈이 됩니다. > ALL 은 “모든 비교값 보다 크냐”고 묻는 것이 되므로 최대값보다 더 크면 참이 됩니다.

 

order by 할때 내림차순으로 하는게 유리하다  , distinct 

 

select * from emp where sal > ALL(select sal from emp where deptno=30);

 

 

영업 사원들 보다 급여를 많이 받는 사원들의 이름과 급여, 직급(담당 업무)을 출력하되 영업 사원은 출력하지 않습니다.

select * from emp where sal >all(select sal from emp where job='salesman');

 

 

 

ANY => or 연산 

      1. 메인 쿼리의 비교 조건이 서브 쿼리의 검색 결과와 하나 이상이 일치하면 참입니다.
      2. > ANY() 는 찾아진 값에 대해서 하나라도 크면 참이 되는 셈이 됩니다. 그러므로 찾아진 값 중에서 가장 작은 값 즉, 최소 값 보다 크면 참이 됩니다. 

 

order by 할때 오름차순으로 하는게 유리하다  , distinct 

 

 

EXIST
메인 쿼리의 비교 조건이 서브 쿼리의 결과 중에서 만족하는 값이 하나라도 존재하면 참입니다.

in 이랑 비슷하다 마치 join 을 하듯이 사용이 가능하다

 

select * from dept where exists (select * from emp where emp.deptno=dept.deptno);

 

 

50번인 부서 번호가 있으면 SMITH씨의 부서를 50번으로 바꾸세요. (50번 부서가 없기 때문에 실행 0)

update emp set deptno=50 where ename='smith' and exists(select * from dept where deptno=50);