0203 오라클 DB 입문 - 조인(JOIN), 서브쿼리, DML

1. JOIN 214P

1) 정의

JOIN : 테이블을 좌우로 합쳐주는 방식

등가 JOIN : 같다라는 조건의 조인을 걸어주는 방식  예시 ( ~~ WHERE E.DEPTNO = D.DEPTNO)

비등가 JOIN : 

 

2) 등가 JOIN

--14 rows
SELECT * FROM EMP ORDER BY EMPNO;
--4 rows
SELECT * FROM DEPT ORDER BY DEPTNO;
--14*4=56 rows
--부서정보가 4개라 1*4의 방식으로 같은 이름이 4명이 만들어짐.
SELECT * FROM DEPT, EMP ORDER BY EMPNO;

SELECT * FROM DEPT, EMP ORDER BY EMPNO;

--내부 조인 = 등가조인 = 단순조인
--보통 말하는 조인의 경우에 해당함
SELECT * FROM EMP,DEPT WHERE EMP.DEPTNO = DEPT.DEPTNO
ORDER BY EMPNO;

--테이블 별칭 사용
SELECT * FROM EMP E, DEPT D WHERE E.DEPTNO = D.DEPTNO 
ORDER BY EMPNO;

--실무 방식 (주석같은 느낌으로 확인할 수 있게)
SELECT E.EMPNO , E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO,
D.DNAME, D.LOC
FROM EMP E, DEPTNO D
WHERE E.DEPTNO = D.DEPTNO 
ORDER BY EMPNO;

같은 결과값

--동시에 같은 열을 가지고 있는 경우
--DEPTNO은 둘 다 가지고 있어서 어디서 가지고 와야 하는지 알려줘야 함.
--(D.를 쓰던지 E.를 쓰던지) (DEPTNO)
SELECT EMPNO,ENAME,DEPTNO,DNAME,LOC FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO;
--열의 출처를 구체적으로 명시해야 함  (D.DEPTNO)
SELECT EMPNO,ENAME,D.DEPTNO, DNAME,LOC FROM EMP E, DEPT D 
WHERE E.DEPTNO = D.DEPTNO;

D.DEPTNO

--조건 추가
--조건은 반드시 최소 테이블 개수 - 1개 만큼 필요함 (조건의 개수 = 등가 조인 -1개)
SELECT E.EMPNO,E.ENAME ,E.SAL,D.DEPTNO,D.DNAME ,D.LOC
FROM EMP E, DEPT D
WHERE E.DEPTNO =D.DEPTNO 
AND SAL >= 3000;
--문제
SELECT E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME,D.LOC 
FROM EMP E,DEPT D 
WHERE E.DEPTNO=D.DEPTNO
AND SAL <= 2500
AND EMPNO <= 9999;

--조건이 추가된다는 것의 의미
SELECT E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME,D.LOC 
FROM EMP E,DEPT D 
WHERE E.DEPTNO=D.DEPTNO
AND E.EMPNO=E.MGR
AND SAL <= 2500
AND EMPNO <= 9999;

SELECT E.EMPNO,E.ENAME,E.SAL,D.DEPTNO,D.DNAME,D.LOC FROM EMP E,DEPT D WHERE E.DEPTNO=D.DEPTNO AND SAL <= 2500 AND EMPNO <= 9999;

3) 비등가 조인

--비등가조인 = 등가 조인 이외의 조인 방식
SELECT * FROM EMP; 
SELECT * FROM SALGRADE;

--BETWEEN X AND Y -> 이렇게 한묶음
SELECT * FROM EMP E, SALGRADE S 
WHERE E.SAL BETWEEN S.LOSAL AND S.HISAL;

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

4) 자체 조인

--자체 조인 = 같은 테이블끼리 조인
SELECT E1.EMPNO,E1.ENAME,E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO;

----외부조인
--LEFT OUTER JOIN (+이 없는 곳이 기준점임)
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR = E2.EMPNO (+)
ORDER BY E1.EMPNO;

--직속상관이 없는 사람도 나옴
--RIGHT OUTER JOIN
SELECT E1.EMPNO, E1.ENAME, E1.MGR,
E2.EMPNO AS MGR_EMPNO,
E2.ENAME AS MGR_ENAME
FROM EMP E1, EMP E2
WHERE E1.MGR(+) = E2.EMPNO
ORDER BY E1.EMPNO;
--부하직원이 없는 사람도 나옴

 

5) NATURAL JOIN (등가 조인 방식)

SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,DEPTNO,
D.DNAME,D.LOC
FROM EMP E NATURAL JOIN DEPT D
ORDER BY DEPTNO,E.EMPNO;

--JOIN ~ USING
--조인할 공통 열을 지정해야 함
--USING을 써서 어떤거랑 같은 것인지 확인시켜야 함.
--USING = WHERE 같은 느낌 (DEPTNO)와 같다면
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,DEPTNO,
D.DNAME,D.LOC
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL >= 3000
ORDER BY DEPTNO, E.EMPNO;

E와 D의 DEPTNO와 같은 애들을 기준으로 급여가 3000이상인 애들

ORDER BY DEPTNO, E.EMPNO를 생략한 경우

6) JOIN USING

--JOIN ~ USING
--조인할 공통 열을 지정해야 함
--USING을 써서 어떤거랑 같은 것인지 확인시켜야 함.
--USING = WHERE 같은 느낌 (DEPTNO)와 같다면
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,DEPTNO,
D.DNAME,D.LOC
FROM EMP E JOIN DEPT D USING (DEPTNO)
WHERE SAL >= 3000
ORDER BY DEPTNO, E.EMPNO;

7) JOIN ON

--JOIN ~ ON
--조인할 공통 열을 지정해야 함 = 같다는 조건으로
SELECT E.EMPNO,E.ENAME,E.JOB,E.MGR,E.HIREDATE,E.SAL,E.COMM,E.DEPTNO,
D.DNAME,D.LOC
FROM EMP E JOIN DEPT D ON (E.DEPTNO = D.DEPTNO)
WHERE SAL <= 3000
ORDER BY E.DEPTNO, EMPNO;

8) OUTER JOIN ON (LEFT) // 앞서 자체 테이블 조인한 것과 같은 결과

-- OUTER JOIN ON 
-- LEFT OUTER JOIN ON
-- +을 넣은 것과 같은 이지 
-- 괄호는 조건을 의미
SELECT E1.EMPNO,E1.ENAME,E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
       FROM EMP E1 LEFT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
       ORDER BY E1.EMPNO;

--RIGHT OUTER JOIN ON 
SELECT E1.EMPNO,E1.ENAME,E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
       FROM EMP E1 RIGHT OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
       ORDER BY E1.EMPNO;
       --FULL OUTER JOIN ON 
SELECT E1.EMPNO,E1.ENAME,E1.MGR,
       E2.EMPNO AS MGR_EMPNO,
       E2.ENAME AS MGR_ENAME
       FROM EMP E1 FULL OUTER JOIN EMP E2 ON (E1.MGR = E2.EMPNO)
       ORDER BY E1.EMPNO;

-- 직속상관이 없는 사람 + 부하직원이 없는 사람

LEFT

 

RIGHT

FULL

2. 서브쿼리 (242P)

1) 특징

기본적으로 WHERE 절 뒤 ,FROM 절 뒤, SELECT 절 뒤에 온다.

* ORDER BY를 쓰지 않음

--단일행 서브쿼리 소재

SELECT SAL
  FROM EMP
 WHERE ENAME = 'JONES';

SELECT *
  FROM EMP
 WHERE SAL > 2975;

-- 실제 단일행 서브쿼리

SELECT *
  FROM EMP
 WHERE SAL > (SELECT SAL
                FROM EMP
               WHERE ENAME = 'JONES');

 

2) 실행결과가 하나인 단일행 서브쿼리

--단일행서브쿼리 & 날짜형 데이터

SELECT *
  FROM EMP
 WHERE HIREDATE < (SELECT HIREDATE
                     FROM EMP
                    WHERE ENAME = 'SCOTT');

3) 단일행 서브쿼리와 함수 (꺼내쓰기)

--단일행서브쿼리 & 함수
  SELECT E.EMPNO,
         E.ENAME,
         E.JOB,
         E.SAL,
         E.DEPTNO,
         D.DNAME,
         D.LOC
    FROM EMP E, DEPT D
   WHERE E.DEPTNO = D.DEPTNO
   AND E.DEPTNO = 20
   AND E.SAL > (SELECT AVG(SAL) FROM EMP);

4) 다중행 서브쿼리

- IN : OR 연산자의 기능 TRUE

- ANY, SOME : 하나 이상이면 TRUE

- ALL : AND 연산자의 기능 TRUE

- EXISTS : 결과가 존재하면 TRUE 

--  다중행서브쿼리
--  다중행 연산자 IN
  SELECT * FROM EMP WHERE DEPTNO IN (20,30); 
  SELECT * FROM EMP WHERE SAL IN (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

-- 같다는 OR 조건 여러 개와 동일
-- 다중행 연산자 ANY
 SELECT * FROM EMP WHERE SAL = ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);
 
-- 다중행 연산자 SOME
 SELECT * FROM EMP WHERE SAL = SOME (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

SELECT * FROM EMP WHERE SAL = ANY (SELECT MAX(SAL) FROM EMP GROUP BY DEPTNO);

--ANY/SOME
--결과는 최대값보다 작은 것과 같다  
--950 1250 1500 1600 2850
 SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30)
 ORDER BY  SAL, EMPNO;
 
 SELECT * FROM EMP WHERE SAL < SOME (SELECT SAL FROM EMP WHERE DEPTNO = 30)
 ORDER BY  SAL, EMPNO;

SELECT * FROM EMP WHERE SAL < ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30) ORDER BY SAL, EMPNO;

-- 단일행 서브쿼리로 대체
SELECT * FROM EMP WHERE SAL < (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30)
 ORDER BY  SAL, EMPNO;
 
 SELECT * FROM EMP WHERE SAL > ANY (SELECT SAL FROM EMP WHERE DEPTNO = 30)
 ORDER BY  SAL, EMPNO;
 
  SELECT * FROM EMP WHERE SAL > SOME (SELECT SAL FROM EMP WHERE DEPTNO = 30)
 ORDER BY  SAL, EMPNO;
 
  SELECT * FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30)
 ORDER BY  SAL, EMPNO;

SELECT * FROM EMP WHERE SAL > (SELECT MIN(SAL) FROM EMP WHERE DEPTNO = 30) ORDER BY SAL, EMPNO;

< all :최소값보다 작다 (작다는 최소값)

> all : 최대값보다 크다 (크다는 최대값)

-- ALL 다중행 연산자
-- < ALL
-- 결과는 최소값보다 작은 것과 같다.
  SELECT * FROM EMP WHERE SAL < ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30;
  -- > ALL
-- 결과는 최대값보다 큰 것과 같다.
  SELECT * FROM EMP WHERE SAL > ALL (SELECT SAL FROM EMP WHERE DEPTNO = 30);
-- 단일행
  SELECT * FROM EMP WHERE SAL > (SELECT MAX(SAL) FROM EMP WHERE DEPTNO = 30);

--  EXISTS 다중행 연산자
SELECT * FROM DEPT;
  SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT  WHERE DEPTNO = 10);
  SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT  WHERE DEPTNO = 50);

SELECT * FROM EMP WHERE EXISTS (SELECT DNAME FROM DEPT WHERE DEPTNO = 10);

5) 다중열 서브쿼리 - 실무적으로 외우기

--다중열 서브쿼리
--여러 개의 열을 비교하는 경우는 실무에서 꽤 많이 사용하므로 기억해두기
SELECT *
  FROM EMP
 WHERE (DEPTNO, SAL) IN (  SELECT DEPTNO, MAX (SAL)
                             FROM EMP
                         GROUP BY DEPTNO);

6) 인라인뷰 - FROM 뒤

--FROM절 뒤에서 사용하는 서브쿼리 = 인라인뷰                         
SELECT E10.EMPNO,
       E10.ENAME,
       E10.DEPTNO,
       D.DNAME,
       D.LOC
  FROM (SELECT *
          FROM EMP
         WHERE DEPTNO = 10) E10,
       (SELECT * FROM DEPT) D
 WHERE E10.DEPTNO = D.DEPTNO;

 

* 데이터가 클 때는 인라인뷰를 자주 사용함.

 

7) 조건을 별칭으로 바꿔주는 기능 - WITH 

--인라인뷰가 많을 때는 WITH절로 먼저 별칭으로 선언해서 사용하면 가독성에 좋다.
WITH
    E10
    AS
        (SELECT *
           FROM EMP
          WHERE DEPTNO = 10),
    D AS (SELECT * FROM DEPT)
SELECT E10.EMPNO,
       E10.ENAME,
       E10.DEPTNO,
       D.DNAME,
       D.LOC
  FROM E10, D
 WHERE E10.DEPTNO = D.DEPTNO;

* 조건을 달 때 별칭 설정은 (이름) AS (조건)이 된다. (SELECT와 다른 별칭 지정)

-- SELECT 절에서 사용하는 서브쿼리 = 스칼라 서브쿼리

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

SELECT DNAME
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO;

--

SELECT EMPNO,
       ENAME,
       JOB,
       SAL,
       (SELECT GRADE
          FROM SALGRADE S
         WHERE E.SAL BETWEEN LOSAL AND HISAL)    AS SALGRADE,
       DEPTNO,
       (SELECT DNAME
          FROM DEPT
         WHERE E.DEPTNO = DEPT.DEPTNO)         AS DNAME
  FROM EMP E;

- 열을 추가할 수 있는 것이 스칼라 서브쿼리임.

 

3. DML (266P)

1) 정의

DML : INSERT, UPDATE, DELETE와 같은 정보를 수정하는 기능 (데이터 조작)

TCL : COMMIT, ROLLBACK와 TCL(트랜잭션 제어 언어)은 데이터베이스의 데이터 변경 사항을 관리하는 데

사용되는 기능

DDL : CREATE, ALTER, RENAME, TRUNCATE, DROP 데이터베이스 스키마를 정의하는 데 사용됩니다.

 

* 데이터베이스 스키마 : 최적화된 검색, 조작 작업

 

핵심

1. JOIN ON 4가지 구문 꼭

2. ON 뒤에는 조건이 들어간다 (WHERE) 같은 느낌으로

3. 조인은 FROM 뒤에서

4. 244P

5. 950 1250 1500 1600 2850

6. 실무의 스타트는 서브쿼리의 연산자를 자유자재로 이용할 수 있어야 함

7. 다중 열 서브쿼리

8. WITH (조건 별칭 지점)