(MariaDB)조인

조인

두 개 이상의 테이블을 서로 묶어서 하나의 결과 집합으로 만드는 것

• 조인은 기본키와 외래키사이의 공통적인 데이터를 기준으로 묶는다

테이블 2개

INNER JOIN(내부조인)

내부 조인은 둘 이상의 테이블에 존재하는 공통 속성의 값이 같은 것을 결과로 추출

1.
SELECT username,addr, u.userid,b.userid,prodname,price,amount
FROM usertbl u JOIN buytbl b
ON u.userID=b.userid;

2.
SELECT username,addr, u.userid,b.userid,prodname,price,amount
FROM usertbl u INNER JOIN buytbl b
ON u.userID=b.userid;

3.
SELECT username,addr, u.userid,b.userid,prodname,price,amount
FROM usertbl u , buytbl b
WHERE u.userid=b.userid;

위의 코드는 모두 아래와 같은 결과가 출력된다

OUTER JOIN(외부조인)

INNER JOIN처럼 공통적인 속성이 모두 나오는 것이 아니고 하나의 테이블에 맞춰서 다른 테이블과 묶는 것
LEFT OUTER JOIN
왼쪽의 모든 행에 있는 것을 출력

SELECT u.userid,username,addr, prodname,price,amount
FROM usertbl u LEFT OUTER JOIN buytbl b
ON u.userID=b.userid;

SELECT u.userid,username,addr, prodname,price,amount
FROM usertbl u RIGHT OUTER JOIN buytbl b
ON u.userID=b.userid;

---

테이블 3개

왼쪽부터 순서대로 stdtbl , stdclubtbl , clubtbl

내부조인

1.
SELECT S.stdname,S.addr,C.clubname,C.roomno
FROM stdtbl S 
INNER JOIN stdclubtbl SC ON S.stdname=SC.stdname 
INNER JOIN clubtbl C ON SC.clubname=C.clubname;

2.
SELECT C.clubname,C.roomNO,S.stdname,S.addr
FROM stdtbl S
INNER JOIN stdclubtbl SC ON SC.stdname =S.stdname
INNER JOIN clubtbl C ON C.clubname=SC.clubname
ORDER BY C.clubname;

외부조인

SELECT s.stdname,s.addr,c.clubname,c.roomno
FROM stdtbl s 
LEFT OUTER JOIN stdclubtbl sc
ON s.stdname=sc.stdname
LEFT OUTER JOIN clubtbl c
ON sc.clubname=c.clubname
ORDER BY s.stdname;

SELECT c.clubname,c.roomno,s.stdname,s.addr
FROM stdtbl s 
LEFT OUTER JOIN stdclubtbl sc
ON s.stdname=sc.stdname
RIGHT OUTER JOIN clubtbl c
ON sc.clubname=c.clubname
ORDER BY c.clubname;

full조인

union을 사용할 때 열의 개수가 같아야 하고 데이터형식도 각 열단위로 같거나 서로 호환되는 데이터형식이여야 한다

• union : 두 쿼리의 결과를 행으로 합치는 것

-- FULL JOIN
SELECT s.stdname,s.addr,c.clubname,c.roomno
FROM stdtbl s 
LEFT OUTER JOIN stdclubtbl sc
ON s.stdname=sc.stdname
LEFT OUTER JOIN clubtbl c
ON sc.clubname=c.clubname

UNION 

SELECT s.stdname,s.addr,c.clubname,c.roomno
FROM stdtbl s 
LEFT OUTER JOIN stdclubtbl sc
ON s.stdname=sc.stdname
RIGHT OUTER JOIN clubtbl c
ON sc.clubname=c.clubname;

CROSS JOIN(상호조인)

한쪽 테이블의 모든 행들과 다른쪽 테이블의 모든 행을 조인시키는 기능

• 결과 개수 : 두 테이블개수의 곱

---

연습문제

-- Q1) 사원테이블과 부서테이블에서 사원들의 이름, 부서번호, 부서이름을 출력하자.
SELECT E.ename,E.deptno,D.dname
FROM emp E JOIN dept D
ON E.DEPTNO=D.DEPTNO;

-- Q2) 사원테이블과 부서테이블에서 'DALLAS'에서 근무하는 사원의 이름, 직위, 부서번호, 부서이름을 출력하자.
SELECT E.ename,E.job,E.deptno,D.dname
FROM emp E JOIN dept D
ON E.DEPTNO=D.DEPTNO
WHERE D.LOC='dallas';

-- Q3) 사원테이블과 부서테이블에서 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하자.
SELECT E.ename,D.dname
FROM emp E JOIN dept D
ON E.deptno=D.deptno
WHERE E.ENAME LIKE "%A%";

-- Q4) 사원테이블과 부서테이블에서 사원이름과 그 사원이 속한 부서의 부서명, 월급을 출력하자.
-- 단 월급이 3000 이상인 사원들을 출력하자.
SELECT E.ename ,D.dname,E.sal
FROM emp E JOIN dept D ON E.deptno =D.deptno
WHERE E.sal>=3000;

-- Q5) 사원테이블과 부서테이블에서 직업이 'SALESMAN'인 사원들의 직업과 사원이름, 속한 부서이름을 출력하자.
SELECT E.ename ,D.dname
FROM emp E JOIN dept D ON E.deptno =D.deptno
WHERE E.job='salesman';

-- Q6) 사원테이블과 급여테이블(SALGRADE)에서 커미션이 책정된 사원들의 사원번호, 이름, 연봉,
-- 연봉+커미션, 급여등급을 출력하자.
-- 단, 각각의 컬럼명을 '사원번호', '사원이름', '연봉', '실급여', '급여등급'으로 출력하자.
SELECT E.empno,E.ename,E.sal*12,E.sal*12+comm,S.grade
FROM emp E JOIN salgrade S ON E.SAL BETWEEN S.LOSAL AND S.HISAL
WHERE E.comm IS NOT null;

-- Q7) 사원테이블과 부서테이블, 급여테이블에서 부서번호가 10번인 사원들의
-- 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하자.
-- E.deptno,D.dname,E.ename,E.sal,S.grade
SELECT E.deptno,D.dname,E.ename,E.sal,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
WHERE D.deptno =10;

-- Q8) 사원테이블과 부서테이블, 급여테이블에서 부서번호가 10번이거나 20번인 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하자.
-- 단, 부서번호가 낮은 순으로(오름차순), 월급이 높은 순으로(내림차순) 출력하자.
SELECT E.deptno,D.dname,E.ename,E.sal,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
WHERE D.deptno =10 OR D.deptno=20
ORDER BY E.DEPTNO ASC , E.sal desc;

-- Q9) 사원테이블에서 사원번호와 사원이름, 그리고 그 사원을 관리하는 관리자의 사원번호와 사원이름을 출력하자.
-- 단, 각각의 컬렴명을 '사원번호', '사원이름', '관리자번호', '관리자이름'으로 출력하자.
-- 셀프조인
SELECT ea.EMPNO,ea.ENAME,ea.mgr,eb.empno,eb.ename
FROM emp ea JOIN emp eb ON ea.mgr=eb.empno;

-- Q10) 사원테이블과 부서테이블에서 해당 부서의 모든 사원에 대한 부서이름, 위치, 사원 수 및
-- 평균 급여를 출력하자.
-- 단, 각각의 컬럼명을 DNAME, LOC, NUMBER OF PEOPLE, SALARY 로 출력하자.
SELECT D.dname,D.loc,COUNT(D.deptno),AVG(E.sal)
FROM emp E JOIN dept D
ON E.deptno=D.deptno
GROUP BY D.dname,D.loc;