[ORACLE] 조인(JOIN)

 

 

---

JOIN은 테이블과 테이블을 연결하기 위해 사용됩니다.

이런 조인을 알아보기 위해 테이블을 만들어서 비교해보려고 합니다.

DROP TABLE X,S,M;
CREATE TABLE M
(M1 CHAR(6), M2 VARCHAR(10));
CREATE TABLE S
(S1 CHAR(6), S2 VARCHAR(10));
CREATE TABLE X
(X1 CHAR(6), X2 VARCHAR(10));

INSERT INTO M VALUES('A','1');
INSERT INTO M VALUES('B','1');
INSERT INTO M VALUES('C','3');
INSERT INTO M VALUES(NULL, '3');

INSERT INTO S VALUES('A','X');
INSERT INTO S VALUES('B','Y');
INSERT INTO S VALUES(NULL, 'Z');

INSERT INTO X VALUES('A','DATA');
COMMIT;

 

위의 코드를 입력해서 M, S, X 테이블을 만들고 각 테이블에 값을 인서트 해주세요!

 

1. INNER JOIN

교집합과 같은 형태입니다.

--ANSI 표준
SELECT * FROM M INNER JOIN S ON M1 = S1;

 

--ORACLE 표준
SELECT * FROM M,S WHERE M1 = S1;

 

 

 

 

2. CROSS JOIN

-- ANSI 표준
SELECT * FROM M CROSS JOIN S;

 

 

--ORACLE 표준
SELECT * FROM M,S;

 

 

 

 

3. OUTER JOIN

--ANSI 표준
SELECT * FROM M LEFT OUTER JOIN S ON M1 = S1;

 

--ORACLE 표준
SELECT * FROM M,S WHERE M1 = S1(+);

 

 

 

--ANSI 표준
SELECT * FROM M RIGHT OUTER JOIN S ON M1 = S1;

 

--ORACLE 표준
SELECT * FROM M,S WHERE M1(+) = S1;

 

 

 

4. NON EQUO JOIN

테이블의 어떤 컬럼도 JOIN할 테이블의 컬럼과 일치하지 않을 때

SELECT ENAME, SAL, GRADE
FROM EMP JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL);

 

SELECT E.ENAME, E.SAL, S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

 

 

 

 

5. SELF JOIN

--Q) 모든 사원의 사번, 이름, 관리자의 사번, 관리자의 이름을 출력하자.

SELECT 사원.EMPNO, 사원.ENAME, 관리자.EMPNO, 관리자.ENAME
FROM EMP 사원, EMP 관리자
WHERE 사원.MGR = 관리자.EMPNO;

 

---

--Q1) 사원테이블과 부서테이블에서 사원들의 이름, 부서번호, 부서이름을 출력하자.

--ANSI 표준
SELECT ENAME, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO);

 

--ORACLE 표준
SELECT E.ENAME, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO;

 

 

--Q2) 사원테이블과 부서테이블에서 'DALLAS'에서 근무하는 사원의 이름, 직위, 부서번호, 부서이름을 출력하자.

--ANSI 표준
SELECT ENAME, JOB, DEPTNO, DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE LOC = 'DALLAS';

 

--ORACLE 표준
SELECT E.ENAME, E.JOB, E.DEPTNO, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND D.LOC = 'DALLAS';

 

 

--Q3) 사원테이블과 부서테이블에서 이름에 'A'가 들어가는 사원들의 이름과 부서이름을 출력하자.

--ANSI 표준
SELECT ENAME, DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE ENAME LIKE '%A%';

 

--ORACLE 표준
SELECT E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.ENAME LIKE '%A%';

 

 

 

--Q4) 사원테이블과 부서테이블에서 사원이름과 그 사원이 속한 부서의 부서명, 월급을 출력하자.

--단 월급이 3000 이상인 사원들을 출력하자.

--ANSI 표준
SELECT ENAME, DNAME, SAL
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE SAL >= 3000;

 

--ORACLE 표준
SELECT E.ENAME, D.DNAME, E.SAL
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL >= 3000;

 

 

--Q5) 사원테이블과 부서테이블에서 직업이 'SALESMAN'인 사원들의 직업과 사원이름, 속한 부서이름을 출력하자.

--ANSI 표준
SELECT JOB, ENAME, DNAME
FROM EMP JOIN DEPT USING(DEPTNO)
WHERE JOB = 'SALESMAN';

 

--ORACLE 표준
SELECT E.JOB, E.ENAME, D.DNAME
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
AND E.JOB = 'SALESMAN';

 

 

--Q6) 사원테이블과 급여테이블(SALGRADE)에서 커미션이 책정된 사원들의 사원번호, 이름, 연봉, 연봉+커미션, 급여등급을 출력하자.

--단, 각각의 컬럼명을 '사원번호', '사원이름', '연봉', '실급여', '급여등급'으로 출력하자.

--ANSI 표준
SELECT EMPNO 사원번호, ENAME 사원이름, SAL*12 연봉, SAL*12+NVL(COMM,0) 실급여, GRADE 급여등급
FROM EMP JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL)
WHERE COMM IS NOT NULL;

 

--ORACLE 표준
SELECT E.EMPNO, E.ENAME, E.SAL*12, E.SAL*12 + NVL(E.COMM, 0), S.GRADE
FROM EMP E, SALGRADE S
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.COMM IS NOT NULL;

 

 

--Q7) 사원테이블과 부서테이블, 급여테이블에서 부서번호가 10번인 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하자.

--ANSI 표준
SELECT DEPTNO, DNAME, ENAME, SAL, GRADE
FROM EMP JOIN DEPT USING(DEPTNO)
JOIN SALGRADE ON (SAL BETWEEN LOSAL AND HISAL)
WHERE DEPTNO = 10;

 

--ORACLE 표준
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND E.DEPTNO = 10;

 

 

--Q8) 사원테이블과 부서테이블, 급여테이블에서 부서번호가 10번이거나 20번인 사원들의 부서번호, 부서이름, 사원이름, 월급, 급여등급을 출력하자.

--단, 부서번호가 낮은 순으로(오름차순), 월급이 높은 순으로(내림차순) 출력하자.

--ANSI 표준
SELECT DEPTNO, DNAME, ENAME, SAL, GRADE
FROM EMP JOIN DEPT USING(DEPTNO) JOIN SALGRADE ON(SAL BETWEEN LOSAL AND HISAL)
WHERE DEPTNO = 10
OR DEPTNO = 20
ORDER BY DEPTNO ASC, SAL DESC;

 

--ORACLE 표준
SELECT E.DEPTNO, D.DNAME, E.ENAME, E.SAL, S.GRADE
FROM EMP E, DEPT D, SALGRADE S
WHERE E.DEPTNO = D.DEPTNO
AND E.SAL BETWEEN S.LOSAL AND S.HISAL
AND (E.DEPTNO = 10 OR E.DEPTNO = 20);

 

 

--Q9) 사원테이블과 부서테이블에서 해당 부서의 모든 사원에 대한 부서이름, 위치, 사원 수 및 평균 급여를 출력하자.

--단, 각각의 컬럼명을 DNAME, LOC, NUMBER OF PEOPLE, SALARY 로 출력하자.

--ANSI 표준
SELECT DNAME, LOC, COUNT(*) AS "NUMBER OF PEOPLE", AVG(SAL) AS "SALARY"
FROM EMP JOIN DEPT ON EMP.DEPTNO = DEPT.DEPTNO
GROUP BY (DNAME, LOC);

 

--ORACLE 표준
SELECT D.DNAME, D.LOC, COUNT(*) AS "NUMBER OF PEOPLE", AVG(E.SAL) AS "SALARY"
FROM EMP E, DEPT D
WHERE E.DEPTNO = D.DEPTNO
GROUP BY D.DNAME, D.LOC;