[Day14 | MySQL] Join(ANSI Join, inner join, outer join, cross join etc.)

JOIN 문 사용하는 법 

 

 

다양한 데이터 구조 속에서 데이터를 출력하는 실습이 필요해요! 

언어로 이해하고 --> 그다음에 짜기.

우선 생각없이 select from 쓰고, 그 후 연결 데이터 찾아 써주고 이런 식으로!

 

나중에 데이터 베이스 설계할 때 정작 능력은 고객의 요구를 한글로 표현할 수 있다! 이게 제일 중요해욤!! 

 

 

 

Q. KING 이름, 부서명, 부하직원 이름, 부서명? 

이렇게 구조잡기

 

 

Q. 4 급여등급의 해당하는 직원들의 이름, 사번, 부서명, 급여등급과 그 직원들의 부하직원의 이름, 사번, 부서명, 급여등급은?

- m은 매니저

- e는 부하직원 

이런 구조로 생각해서 풀어가기 

 

 

 

Join01.SQL

-- Join01.sql --

-- SMITH의 이름, 소속 부서 이름은? 

SELECT ename, dname
FROM emp, dept 
WHERE (emp.deptno = dept.deptno) 
AND (ename = 'SMITH');


-- research 부서 소속 직원들의 부서명, 위치, 직원이름, 사번은? 

select  dname, loc, ename, empno
from emp, dept 
where (emp.deptno = dept.deptno)
and (dname = 'research');



-- sales 부서의 직원들의 이름, 사번, 부서이름은?
SELECT ename, empno, dname
from emp, dept 
where (emp.deptno = dept.deptno)
and (dname = 'sales');



-- King의 이름, 사번, job, 부서명, 위치는 ?

select ename, empno, job, dname, loc 
from emp, dept 
where (emp.deptno = dept.deptno)
and ename = 'king';


-- smith의 이름, 사번, 부서명, 부서번호는 ? deptno 어떤 것을 써야하는지 정해줘야 
SELECT emp.ename, emp.empno, dept.dname, emp.deptno
from  emp , dept 
where (emp.deptno = dept.deptno)
AND emp.ename = 'SMITH';


-- smith의 이름, 사번, 부서명, 부서번호는 ? 별칭 주기  
SELECT e.ename, e.empno, d.dname, e.deptno
from  emp e, dept d 
where (e.deptno = d.deptno)
AND e.ename = 'SMITH';


-- 부서 이름별 인원수? --'별' 붙으면 정렬 아니면 소팅 
SELECT d.dname, count(e.empno)
from emp e, dept d 
where ( d.deptno  = e.deptno ) -- 넘나드는 다리 그대로 써주면 됨 , 나중에 조인문은 노가다 
group by d.dname; -- 그루핑하면 무엇으로 그루핑했지 select 뒤에 써준다.


-- 부서 이름별 평균 급여? 
SELECT d.dname, AVG(sal) 
from emp e, dept d 
where ( d.deptno  = e.deptno ) -- 넘나드는 다리 그대로 써주면 됨 , 나중에 조인문은 노가다 
group by d.dname;

-- 부서별 평균 급여가 2000이 넘는 부서와 평균 급여? 
SELECT d.dname, avg(e.sal)
from emp e, dept d 
where ( d.deptno  = e.deptno )
group by d.dname
having (avg(e.sal)>=2000) ;



-- 스미스씨의 이름, 급여, 급 range는 어디에 있을까? 

SELECT e.ename , e.sal, s.grade 
from emp e, salgrade s
where (e.sal between s.losal AND s.hisal)
and (e.ename = 'SMITH') ;


-- 3 급여등급의 급여등급, 해당 직원의 이름, 급여는?
SELECT s.grade , e.ename , e.sal 
from emp e, salgrade s 
where (s.grade = 3);



-- 1,4 급여등급의 급여등급, 해당 직원의 이름, 급여는?
SELECT s.grade, e.ename, e.sal
from emp e, salgrade s 
where (s.grade = 1 or s.grade = 4)
order by s.grade ;

-- 급여등급별 인원수는?
select s.grade, count(empno)
from emp e, salgrade s 
where (e.sal BETWEEN s.losal and s.hisal)
group by s.grade;


-- sales 부서의 부서명, 소속직원이름, 사번, 급여, 급여등급은?
select d.dname, e.ename, e.empno, e.sal, s.grade 
from emp e, dept d , salgrade s 
where (e.sal BETWEEN s.losal and s.hisal)
and d.dname = 'sales';


-- research 부서 소속 직원들의 부서명, 부서번호, 이름, 사번, 급여, 급여 등급은? 
SELECT d.dname , d.deptno , e.ename, e.empno , e.sal , s.grade 
from emp e , dept d , salgrade s 
WHERE (e.sal BETWEEN s.losal and s.hisal)
and d.dname = 'research';


-- KING의 이름, 사번, 급여, 급여등급, 부서명은? 
SELECT e.ename ,e.empno ,e.sal , s.grade ,d.dname 
from emp e , dept d , salgrade s 
where e.deptno = d.deptno 
and (e.sal BETWEEN s.losal and s.hisal )
and (e.ename = 'king');






-- smith의 이름과 직속상관의 이름은? 
SELECT a1.ename , a2.ename as 'manager'
from emp a1, emp a2
where a1.ename = 'smith'
and  a2.empno = a1.mgr ;


-- ford의 이름, 사번, 부하직원의 이름, 사번은?
SELECT a1.ename , a1.empno , a2.ename as '부하직원', a2.empno 
from emp a1, emp a2
WHERE a1.ename = 'ford'
and a1.empno = a2.mgr ;


-- King 의 이름, 부서명, 부하직원의 이름, 부서명은? 
-- 테이블 4개이므로 최소 join조건 3개. 
SELECT e1.ename , d1.dname , e2.ename , d2.dname 
from emp e1, dept d1, emp e2, dept d2
where e1.deptno = d1.deptno 
and e2.deptno = d2.deptno 
and e1.empno = e2.mgr
and e1.ename = 'king';


-- 왜 dept 어차피 같은 논리로 연결하는데 테이블 꼭 나눠서 써야하나요? 
-- 안나눠쓰면 세 테이블에 동시 만족하는 경우만 나오기 때문입니다 

select me.ename,dd.dname , ee.ename, dd.dname 
from emp me, dept dd, emp ee
where ( me.deptno = dd.deptno )
 and  ( me.empno = ee.mgr )
 and  ( ee.deptno = dd.deptno )
 and  (me.ename = 'King');




-- 4 급여등급의 해당하는 직원들의 이름, 사번, 부서명, 급여등급과
--    그 직원들의 부하직원의 이름, 사번, 부서명, 급여등급은?

SELECT e1.ename , e1.empno , d1.dname , s1.grade , 
	   e2.ename , e2.empno ,d2.dname , s2.grade 
from emp e1, salgrade s1, dept d1,
	 emp e2, salgrade s2, dept d2
where e1.deptno = d1.deptno 
and e2.deptno = d2.deptno 
and e1.empno = e2.mgr
and e1.sal BETWEEN s1.losal and s1.hisal 
and e2.sal BETWEEN s2.losal and s2.hisal 
and s1.grade = 4;

-- --------------------------------------------


drop table dept2; -- 테이블 삭제 

-- dept에 있는 모든 컬럼을 dept2에 복사해라 
create table dept2  
select * from dept;

drop table emp2;

-- k가 들어있거나 s로 끝나는 애들만 복사해라 
create table emp2
select ename,empno,deptno 
from emp 
where ename like '%K%' or ename like '%S';

select * from emp2;
select * from dept2;


-- 데이터 입력
INSERT into emp2 values('Tom', 7777, null);


-- emp2, dept2 
-- 전 직원의 이름, 사번, 위치, 부서번호? 

SELECT * 
from emp2 e ,dept2 d 
where e.deptno = d.deptno ;


-- tom? operation 부서? 연결고리가 없어서 안나옴 
-- ANSI JOIN 273p
select *
from emp2 e inner join dept2 d on(e.deptno = d.deptno);

SELECT *
from emp2 e left outer join dept2 d on (e.deptno = d.deptno);

SELECT *
from emp2 e right outer join dept2 d on (e.deptno = d.deptno);

-- mysql에서는 full outer join 이 안됨. 
SELECT *
from emp2 e full outer join dept2 d on (e.deptno = d.deptno);


-- 따라서 따로 따로 구한걸 합쳐야

SELECT *
from emp2 e left outer join dept2 d on (e.deptno = d.deptno)
union
SELECT *
from emp2 e right outer join dept2 d on (e.deptno = d.deptno);

join 의 형태는 두 가지가 있다. 

1. 일반 join : where 조건  // 개발자들은 이걸 선호하고

2. ANSI join : from 테이블 (    )  join 테이블  on ( join 조건 ) // 요즘 프로그램등 시험은 이걸로 쓴다

 

고로 둘 다 알아야! 

 

Join02.sql

-- JOIN02 -- 


-- tom? operation 부서? 연결고리가 없어서 안나옴 
-- ANSI JOIN 273p
select *
from emp2 e inner join dept2 d on(e.deptno = d.deptno);

SELECT *
from emp2 e left outer join dept2 d on (e.deptno = d.deptno);

SELECT *
from emp2 e right outer join dept2 d on (e.deptno = d.deptno);

-- mysql에서는 full outer join 이 안됨. 
SELECT *
from emp2 e full outer join dept2 d on (e.deptno = d.deptno);


-- 따라서 따로 따로 구한걸 합쳐야

SELECT *
from emp2 e left outer join dept2 d on (e.deptno = d.deptno)
union
SELECT *
from emp2 e right outer join dept2 d on (e.deptno = d.deptno);

-----------------------------------

-- SMITH의 이름, 소속 부서 이름은? 

SELECT ename, dname
FROM emp, dept 
WHERE (emp.deptno = dept.deptno) 
AND (ename = 'SMITH');





SELECT e.ename , d.dname 
from emp e inner join dept d 
	on (e.deptno = d.deptno)
WHERE (e.ename = 'smith');

----------------------------------------------------

-- RESEARCH 부서 소속 직원들의 부서명, 위치, 이름, 사번은? 
SELECT d.dname , d.loc , e.ename , e.empno 
from emp e inner join dept d 
	on (e.deptno  = d.deptno)
where (d.dname= 'research');


-- 부서명별 평균 급여는? 
SELECT d.dname , AVG(e.sal)
from dept d inner join emp e
	on (e.deptno = d.deptno)
group by d.dname ;


-- smith의 이름, 사번, 급여, 급여등급은?
SELECT e.ename , e.empno , e.sal , s.grade 
from emp e inner join dept d on (e.deptno = d.deptno)
 		   inner join salgrade s on (e.sal BETWEEN s.losal and s.hisal)
where e.ename  = 'smith';



-- 3급여등급의 등급, 직원이름, 급여는?
SELECT s.grade , e.ename , e.sal 
FROM emp e	
	inner join salgrade s 
		on(e.sal BETWEEN s.losal and s.hisal)
where s.grade = 3;


-- 급여등급별 직원수는?

select s.grade , count(e.empno)
from emp e inner join salgrade s 
		on (e.sal BETWEEN s.losal and s.hisal)
group by s.grade ;

-- King의 이름, 사번, 부하직원들의 이름, 사번은?

SELECT me.ename , me.empno , ee.ename , ee.empno 
from emp me inner join emp ee 
	on (me.empno  = ee.mgr)
where (me.ename = 'king')




3 급여등급의 급여등급, 해당 직원의 이름, 급여, 부서명은 ?

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

---------------------------------


sales 부서의 부서명, 소속직원이름, 사번, 급여, 급여등급은?;

SELECT d.dname , e.ename ,e.empno ,e.sal ,s.grade 
from emp e inner join salgrade s on (e.sal BETWEEN s.losal and s.hisal)
		   inner join dept d ON (e.deptno = d.deptno)
where d.dname = 'sales';


King 의 이름, 부서명, 부하직원의 이름, 부서명은?;

-- 내 풀

SELECT e1.ename , d1.dname , e2.ename , d2.dname 
from emp e1 inner join dept d1 on (e1.deptno = d1.deptno)
			inner join emp e2 on (e1.empno = e2.mgr)
			inner join dept d2 on (e2.empno = d2.deptno)
where e1.ename = 'king';


-- 해설 
select me.ename, md.dname, ee.ename, ed.dname 
from emp me inner join dept md on (me.deptno = md.deptno)
			inner join emp ee on (me.empno = ee.mgr)
			inner join dept ed on (ee.deptno = ed.deptno)
where (me.ename = 'king');

King 의 이름과 부하직원들의 이름,job,부서명,급여,급여등급은?;
SELECT e1.ename , e2.ename , e2.job , d2.dname , e2.sal , s2.grade 
from emp e1 inner join emp e2 on (e1.empno = e2.mgr)
			inner join dept d2 on (e2.deptno = d2.deptno )
			inner join salgrade s2 on (e2.sal BETWEEN s2.losal and s2.hisal) 
where e1.ename = 'king';

-- 해설
King 의 이름과 부하직원들의 이름,job,부서명,급여,급여등급은?;

select me.ename, ee.ename, ee.job, ed.dname, ee.sal , es.grade
from emp me inner join emp ee on (me.empno = ee.mgr)
			inner join dept ed on (ee.deptno = ed.deptno)
			inner join salgrade es on (ee.sal between es.losal and es.hisal)
where (me.ename = 'king');


-- emp2 , dept2

-- outerjoin 

모든 부서의 직원 수는? 

SELECT d.dname, COUNT(e.empno)
from dept2 d left outer join emp2 e 
	on (d.deptno = e.deptno)
group by d.dname ;

SELECT d.dname, count(e.empno)
from emp2 e right outer join dept2 d 
	on(d.deptno = e.empno)
group by d.dname ;



-- cross join --(모든 경우의 수) 288~289p
select * 
from emp2 e, dept2 d;

SELECT *
from emp2 e cross join dept2 d;



-- selfjoin p290

 

 

다음주부터 과제를 내줄지도 몰라요 

띠̊̈용̊̈

🤦🏻‍♀️