Oracle- INTERSECT, MINUS(교집합/ 차집합)

SELECT JECODE, JUSU
FROM TBL_JUMUNBACKUP
INTERSECT
SELECT JECODE, JUSU
FROM TBL_JUMUN;
--==>> 
/*
빼빼로	20
새우깡	10
쿠크다스	10
포카칩	20
허니버터칩	10
홈런볼	10
*/

SELECT JECODE, JUSU
FROM TBL_JUMUNBACKUP
MINUS
SELECT JECODE, JUSU
FROM TBL_JUMUN;
--==>> 교집합을 뺀 나머지
/*
새우깡	    50
썬칩	    20
초코파이	20
초코파이	30
쿠크다스	20
포카칩	    30
홈런볼	    20
홈런볼	    30
*/

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

답 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 JECODE, JUSU, JUNO, JUDAY
    FROM TBL_JUMUNBACKUP
    UNION ALL
    SELECT JECODE, JUSU, JUNO, JUDAY
    FROM TBL_JUMUN
) T2
ON T1.JECODE = T2.JECODE
   AND
  T1.JUSU = T2.JUSU;
--==>>
/*
1	    포카칩	20	2001-12-22
2	    쿠크다스	10	2001-12-25
7	    새우깡	10	2002-01-15
10	    허니버터칩	10	2002-02-07
12	    빼빼로	20	2002-02-10
15	    포카칩	20	2002-02-17
16	    홈런볼	10	2002-02-19
938765	홈런볼	10	2025-07-04
938767	포카칩	20	2025-07-04
938768	허니버터칩	10	2025-07-04
938769	빼빼로	20	2025-07-04
938770	홈런볼	10	2025-07-04
938771	새우깡	10	2025-07-04
938776	쿠크다스	10	2025-07-04
*/

답2
-- ★★★ 꼭 알아두세요. 
SELECT T.*
FROM
(
    SELECT JECODE, JUSU, JUNO, JUDAY
    FROM TBL_JUMUNBACKUP
    UNION ALL
    SELECT JECODE, JUSU, JUNO, 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); 
-- 특정컬럼을 연결 후 단일데이터 구성시 문자열 결합시켜 붙여버리면 원하는 데이터만 추출됨.    
/*
포카칩	20	1	2001-12-22
쿠크다스	10	2	2001-12-25
새우깡	10	7	2002-01-15
허니버터칩	10	10	2002-02-07
빼빼로	20	12	2002-02-10
포카칩	20	15	2002-02-17
홈런볼	10	16	2002-02-19
홈런볼	10	938765	2025-07-04
포카칩	20	938767	2025-07-04
허니버터칩	10	938768	2025-07-04
빼빼로	20	938769	2025-07-04
홈런볼	10	938770	2025-07-04
새우깡	10	938771	2025-07-04
쿠크다스	10	938776	2025-07-04
*/

-- CHECK~!!! NATURAL 
SELECT DEPTNO, DNAME, ENAME, SAL 
FROM EMP NATURAL JOIN DEPT;
-- 최대한 자연스럽게 결과를 만들어봐. NATURAL 

-- CHECK~!!!USING문 
SELECT DEPTNO, DNAME, ENAME, SAL
FROM EMP E JOIN DEPT D
USING(DEPTNO); 
-- DEPTNO를 활용해서 결합시켜줘