Union/Union All, Intersect , Minus , =All, Distinct, Update

유동현·2022년 11월 13일

union 오라클

오라클

목록 보기

13/18

우리가 만든 혹은 실무에서 만들 테이블 중
시간이 지나면 지날수록 늘어나는 테이블도 있고,
시간이 지나도 유지되는 테이블도 있다.
그 중, TBL_JUMUN 은 계속 늘어나는 테이블
입력한 내용을 특정 테이블에 넣을 때,
테이블이 무한정 커질 수 없는 상황이기 때문에 새로운 테이블을 만들어 데이터를 넣지 않는다.
A 테이블에 데이터 가득 차게 되면, 늘어난 데이터를 다른 테이블에 백업
A 테이블에 데이터가 가득 차면, 또 백업하는 방식으로 진행한다.

ex)
TBL_JUMUN 테이블이 너무 무거워진 상황
어플리케이션과의 연동으로 인해 주문 내역을 다른 테이블에
저장될 수 있도록 만드는 것은 불가능한 상황
기존의 모든 데이터를 덮어놓고 지우는 것도 불가능한 상황
현재까지 누적된 주문 데이터들 중
금일 발생한 주문 내역을 제외하고
나머지 데이터를 다른 테이블(TBL_JUMUNBACKUP)로 데이터 이관을 수행할 계획

이때의 문제점은 다시 테이블을 다시 합쳐서 조회할 때가 필요하다는것

그때 쓰는것이 Union/Union All 이다.

컬럼과 컬럼의 관계를 고려하여 테이블을 결합하고자 하는 경우
JOIN 을 사용하지만
레코드와 레코드를 결합하고자 하는 경우
UNION / UNION ALL 을 사용할 수 있다.
Union

UNION 은 항상 결과물의 첫 번째 컬럼을 기준으로 오름차순 정렬을 수행한다.

이로 인해 UNION 이 부하가 더 크다. (리소스 소모가 더 크다.)
또한, UNION 은 결과물에 중복된 행이 존재할 경우
중복을 제거하고 1개 행만 조회된 결과를 반환하게 된다.

SELECT *
FROM TBL_JUMUN
UNION
SELECT *
FROM TBL_JUMUNBACKUP;

Union All

UNION ALL 은 결합된 순서대로(테이블을 쿼리문에서 명시한 순서대로)
조회한 결과를 반환한다.(즉, 정렬 기능 없음 = 기능이 없으므로 성능면에서 더 좋다)
중복 제거하지 않고 있는 그대로 더 봐야할 경우가 있을수 있고
UNION 은 부하가 크기 때문에,
UNION 보다 UNION ALL 을 더 많이 써야 한다.

SELECT *
FROM TBL_JUMUN
UNION ALL
SELECT *
FROM TBL_JUMUNBACKUP;

Intersact

교집합

--TBL_JUMUNBACKUP 테이블과 TBL_JUMUN 테이블에서
--제품코드와 수문수량의 값이 똑같은 행만 추출하고자 한다.

SELECT JECODE, JUSU
FROM TBL_JUMUNBACKUP
INTERSECT
SELECT JECODE, JUSU
FROM TBL_JUMUN;
/*
감자깡	20
맛동산	30
홈런볼	10
*/

MINUS

차집합
다만 SQL여러개를 실행한 결과 MINUS는 집합의 개념을 가짐으로써
사용시 결과가 구분할수 없는 동일한 데이터라면 1개 취급을 하는 듯하다
SELECT 문 사용시 유의하자


SELECT JECODE, JUSU
FROM TBL_JUMUNBACKUP
MINUS
SELECT JECODE, JUSU
FROM TBL_JUMUN;
/*
꼬깔콘	20
꼬북칩	20
다이제	10
빼빼로	10
사또밥	20
새우깡	10
스윙칩	20
오감자	20
오레오	10
죠리퐁	10
치토스	20
포스틱	10
포스틱	20
포카칩	20
*/

Join & Union & Intersact & Minus 실습

--○TBL_JUMUNBACKUP 테이블과 TBL_JUMUN 테이블을 대상으로
-- 제품코드와 주문량의 값이 똑같은 행의 정보를
-- 주문번호, 제품코드, 주문량, 주문일자 항목으로 조회한다.

SELECT T2.JUNO "주문번호", T.JECODE "제품코드", T.JUSU "주문량", T2.JUDAY "주문일자"
FROM
(
SELECT JECODE, JUSU
FROM TBL_JUMUNBACKUP
INTERSECT
SELECT JECODE, JUSU
FROM TBL_JUMUN
)T 
JOIN
(
SELECT *
FROM TBL_JUMUNBACKUP
UNION ALL
SELECT *
FROM TBL_JUMUN
)T2 ON T.JECODE = T2.JECODE
WHERE T.JUSU = T2.JUSU
ORDER BY 2,4;

--방법 1.
SELECT T2.JUNO "주문번호", T1.JECODE "제품코드"
     , T1.JUSU "주문수량", T2.JUDAY "주문일자"
FROM 
(
    SELECT JECODE, JUSU
    FROM TBL_JUMUNBACKUP
    INTERSECT
    SELECT JECODE, JUSU
    FROM TBL_JUMUN
) T1
JOIN
(
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUNBACKUP
    UNION ALL
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUN
) T2
ON  T1.JECODE = T2.JECODE
AND T1.JUSU = T2.JUSU;
--==>>
/*
    1	빼빼로	20	2001-11-01 09:10:12
    3	모옹쉘	30	2001-11-01 11:10:05
   19	빼빼로	20	2001-11-19 15:16:17
   20	빼빼로	30	2001-11-20 16:17:18
98781	빼빼로	30	2022-03-02 14:13:47
98765	빼빼로	20	2022-03-02 09:30:30
98776	모옹쉘	30	2022-03-02 11:28:12
*/

--방법 2.
SELECT T.*
FROM 
(
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUNBACKUP
    UNION ALL
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUN
) T
WHERE T.JECODE IN('빼빼로', '모옹쉘')
  AND T.JUSU IN (20, 30);
--==>>
/*
    1	빼빼로	20	2001-11-01 09:10:12     ◀
    3	모옹쉘	30	2001-11-01 11:10:05     ◀
   19	빼빼로	20	2001-11-19 15:16:17     ◀
   20	빼빼로	30	2001-11-20 16:17:18     ◀   
98780	모옹쉘	20	2022-03-02 14:13:43    CHECK~!!! → 얘는 안나와야 하는데 나옴
98781	빼빼로	30	2022-03-02 14:13:47     ◀
98765	빼빼로	20	2022-03-02 09:30:30     ◀
98776	모옹쉘	30	2022-03-02 11:28:12     ◀
*/
-- 모옹쉘 20 개가 나온 이유는,
-- 제품코드, 주문수량 안에 다 포함되기 때문


SELECT T.*
FROM 
(
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUNBACKUP
    UNION ALL
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUN
) T
WHERE CONCAT(T.JECODE,T.JUSU)
    IN ('모옹쉘30', '빼빼로20', '빼빼로30');
--==>>
/*
    1	빼빼로	20	2001-11-01 09:10:12
    3	모옹쉘	30	2001-11-01 11:10:05
   19	빼빼로	20	2001-11-19 15:16:17
   20	빼빼로	30	2001-11-20 16:17:18
98781	빼빼로	30	2022-03-02 14:13:47
98765	빼빼로	20	2022-03-02 09:30:30
98776	모옹쉘	30	2022-03-02 11:28:12
*/


SELECT T.*
FROM 
(
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUNBACKUP
    UNION ALL
    SELECT JUNO, JECODE, JUSU, JUDAY
    FROM TBL_JUMUN
) T
WHERE CONCAT(T.JECODE,T.JUSU) IN (SELECT CONCAT(JECODE, JUSU)
                                  FROM TBL_JUMUNBACKUP
                                  INTERSECT
                                  SELECT CONCAT(JECODE, JUSU)
                                  FROM TBL_JUMUN
                                 );
--==>>
/*
    1	빼빼로	20	2001-11-01 09:10:12
    3	모옹쉘	30	2001-11-01 11:10:05
   19	빼빼로	20	2001-11-19 15:16:17
   20	빼빼로	30	2001-11-20 16:17:18
98781	빼빼로	30	2022-03-02 14:13:47
98765	빼빼로	20	2022-03-02 09:30:30
98776	모옹쉘	30	2022-03-02 11:28:12
*/

실무에서 Max()함수를 못쓸때는?

=All 를 쓰면된다.
예시

--○TBL_EMP 테이블에서 급여가 가장 많은 사원의
-- 사원번호, 사원명, 직종명, 급여 항목을 조회하는 쿼리문을 구성한다.


SELECT EMPNO, ENAME, JOB, SAL
FROM TBL_EMP
WHERE SAL = (SELECT MAX(SAL) FROM TBL_EMP); --Max를 쓸수있다면 이렇게 쓸수있지만 쓸수없다면?
-- 7839	KING	PRESIDENT	5000

-- 『=ALL』 자바에서 AND와 같은 것 안에들어있는 모든 조건을 만족해야 한다.

SELECT EMPNO, ENAME, JOB, SAL
FROM TBL_EMP
WHERE SAL =ANY(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300, 1500, 2000, 1700, 2500, 1000);
/*
7369	SMITH	CLERK	800
7499	ALLEN	SALESMAN	1600
7521	WARD	SALESMAN	1250
7566	JONES	MANAGER	2975
7654	MARTIN	SALESMAN	1250
7698	BLAKE	MANAGER	2850
7782	CLARK	MANAGER	2450
7788	SCOTT	ANALYST	3000
7839	KING	PRESIDENT	5000
7844	TURNER	SALESMAN	1500
7876	ADAMS	CLERK	1100
7900	JAMES	CLERK	950
7902	FORD	ANALYST	3000
7934	MILLER	CLERK	1300
8001	김태민	CLERK	1500
8002	조현하	CLERK	2000
8003	김보경	SALESMAN	1700
8004	유동현	SALESMAN	2500
8005	장현성	SALESMAN	1000
*/
SELECT EMPNO, ENAME, JOB, SAL
FROM TBL_EMP
WHERE SAL >=ANY(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300, 1500, 2000, 1700, 2500, 1000);
--위와 같은 결과 

SELECT EMPNO, ENAME, JOB, SAL
FROM TBL_EMP
WHERE SAL >=ALL(800, 1600, 1250, 2975, 1250, 2850, 2450, 3000, 5000, 1500, 1100, 950, 3000, 1300, 1500, 2000, 1700, 2500, 1000);
--7839	KING	PRESIDENT	5000 MAX를 쓰지 않더라고 최대값을 구할 수 있다.

SELECT EMPNO, ENAME, JOB, SAL
FROM TBL_EMP
WHERE SAL >=ALL(SELECT SAL FROM TBL_EMP);
--7839	KING	PRESIDENT	5000

--○TBL_EMP 테이블에서 20번 부서에 근무하는 사원들 중
-- 급여가 가장 많은 사원의
-- 사원번호, 사원명, 직종명, 급여 항목을 조회하는 쿼리문을 구성한다.

SELECT EMPNO , ENAME, JOB, SAL
FROM TBL_EMP
WHERE DEPTNO = 20
      AND SAL >= ALL(SELECT SAL FROM TBL_EMP WHERE DEPTNO = 20);
      
      
SELECT EMPNO , ENAME, JOB, SAL
FROM TBL_EMP
WHERE DEPTNO = 20
      AND SAL = (SELECT MAX(SAL) FROM TBL_EMP WHERE DEPTNO = 20);



--○TBL_EMP 테이블에서 수당(커미션, COMM)이 가장 많은 사원의
-- 사원번호, 사원명, 부서번호, 직종명, 커미션 항목을 조회한다.
SELECT *
FROM TBL_EMP
WHERE COMM >= ALL( SELECT NVL(COMM,0) FROM TBL_EMP);

SELECT *
FROM TBL_EMP
WHERE COMM = ( SELECT MAX(COMM) FROM TBL_EMP);
--MAX는 NULL값 무시하는듯

SELECT COMM
FROM TBL_EMP
WHERE COMM IS NOT NULL;
/*
300
500
1400
0
10
10
*/

Distinct

중복 행(레코드)을 제거하는 함수


SELECT T2.ENAME, T2.EMPNO, T2.JOB
FROM
(
SELECT MGR
FROM TBL_EMP
GROUP BY MGR
HAVING MGR IS NOT NULL
)T1
JOIN
(SELECT *
FROM TBL_EMP
)T2
ON T1.MGR = T2.EMPNO;

SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE EMPNO = (관리자로 등록된 번호);

SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE EMPNO IN (SELECT MGR
                FROM EMP);
--==>
/*
7566	JONES	MANAGER
7698	BLAKE	MANAGER
7782	CLARK	MANAGER
7788	SCOTT	ANALYST
7839	KING	PRESIDENT
7902	FORD	ANALYST
*/

--위의 코드는 아래와 같다.
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE EMPNO IN (7902, 7698, 7698, 7839, 7698, 7839, 7839, 7566, NULL, 7698, 7788, 7698, 7566, 7782);
--> 번호 중복되는 거 있어서,
--  같은 건데 계속 찾게 됨

-- 그래서 쓰게 되는게
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE EMPNO IN (SELECT DISTINCT(MGR)
                FROM EMP);
--==>>
/*
7566	JONES	MANAGER
7698	BLAKE	MANAGER
7782	CLARK	MANAGER
7788	SCOTT	ANALYST
7839	KING	PRESIDENT
7902	FORD	ANALYST
*/

--위의 코드는 아래와 같다.
SELECT EMPNO, ENAME, JOB
FROM EMP
WHERE EMPNO IN (7839, NULL, 7782, 7698, 7902, 7566, 7788);
--즉 NULL을 걸러주지는 못한다.

SELECT DISTINCT(JOB)
FROM EMP;
--==>>
/*
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
*/

SELECT DISTINCT(DEPTNO)
FROM EMP;
--==>>
/*
30
20
10
*/

Update

테이블에서 특정 데이터를 수정한다.

UPDATE TBL_SAWON
SET SANAME = '똘똘이'; -- WHERE -> SET -> UPDATE문 작성
-- 위와 같은 방식으로 하면 모든 SANAME이 똘똘이로 바뀐다.

항상 작성순서는 Where -> set -> update 순으로 하자

UPDATE TBL_SAWON
SET SANAME = (SELECT SANAME FROM TBL_SAWONBACKUP WHERE SANO = TBL_SAWON.SANO)
WHERE SANAME = '똘똘이';
-- 위와같이 다른 테이블을 참조해서 바꿀 수도 있다.

변경할 내용인 SET 은 SET A=B, C=D, F=H 이런식으로 여러개를 동시에 바꿀 수 있으며
SET (A,B,C) = (SELECT A, B, C FROM ~~~) 해서 바꿀수있다.

유동현

이전 포스트

Join

다음 포스트

Union/Union All, Intersect , Minus , =All, Distinct, Update

오라클

Union

Union All

Intersact

MINUS

Join & Union & Intersact & Minus 실습

실무에서 Max()함수를 못쓸때는?

Distinct

Update

Join

정규화

0개의 댓글