오라클 데이터 베이스 집합 연산자

1. 오라클 집합 연산자

• 여러개의 SELECT를 하나로 연결
• 집합 연산자는 잘 사용하지 않음

 

1-1. UNION : 사이에 넣으면 결과를 하나로 합쳐주고 중복 제거 (UNION ALL을 하면 중복 제거 안함)

SELECT EMPNO, ENAME, SAL, DEPTNO
    FROM EMP
WHERE DEPTNO = 10
UNION
SELECT EMPNO, ENAME, SAL, DEPTNO --컬럼이 일치해야됨
    FROM EMP
WHERE DEPTNO = 20;

왼쪽이 UNION, 오른족 UNION ALL

 

 

1-2. INTERSECT : 교집합 (중복제거)

SELECT EMPNO, ENAME, SAL
    FROM EMP
WHERE SAL>1000
INTERSECT --교집합 (중복제거)
SELECT EMPNO, ENAME, SAL
    FROM EMP
WHERE SAL<2000;

 

1-3. MINUS : 차집합

SELECT EMPNO, ENAME, SAL
    FROM EMP
WHERE SAL>1000
MINUS --차집합
SELECT EMPNO, ENAME, SAL
    FROM EMP
WHERE SAL<2000;

 

2. 오라클 JOIN

• 여러개의 테이블을 하나로 합쳐서 출력 (정규화로 쪼깬 테이블들을 하나로 합쳐준다)

 

JOIN 없이 단순 SELECT를 사용해서 여러테이블에 있는 정보를 하나로 표시할

SELECT *
    FROM EMP, DEPT
WHERE EMP.DEPTNO = DEPT.DEPTNO;

 

2-1 JOIN으로 처리했을때 (합친것)

 

INNER JOIN (내부 조인)

--INNER JOIN(내부 조인)
SELECT *
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO;

 

원하는 데이터만 합쳐서 표시도 가능하다

SELECT EMP.JOB, EMP.DEPTNO, DEPT.DNAME
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO;

 

조건도 넣어서 사용이 가능하다 

SELECT EMP.JOB, EMP.DEPTNO, DEPT.DNAME
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
WHERE EMP.DEPTNO = 20;

 

 

 

 

OUTER JOIN (RIGHT, LEFT)

SELECT EMP.ENAME, EMP.DEPTNO, DEPT.DNAME
    FROM EMP RIGHT OUTER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO;

---

CREATE TABLE 구매현황(
   고객번호 NUMBER(4),
   상품 VARCHAR2(50)
);

CREATE TABLE 고객(
   고객번호 NUMBER(4),
   이름 VARCHAR2(50),
   회원등급 VARCHAR2(50)
);

INSERT INTO 구매현황 VALUES(1001, '셔츠');
INSERT INTO 구매현황 VALUES(1002, '바지');
INSERT INTO 구매현황 VALUES(1003, '반팔티');
INSERT INTO 구매현황 VALUES(9999, '악세사리');

INSERT INTO 고객 VALUES(1001, '유재석', '일반');
INSERT INTO 고객 VALUES(1002, '노홍철', '우수');
INSERT INTO 고객 VALUES(1003, '정준하', 'VIP');
INSERT INTO 고객 VALUES(5555, '하동훈', '일반');

---

INNER JOIN

제품 구매를 한 회원 조회 

SELECT 고객."고객번호", 고객."이름", 고객."회원등급", 구매현황."상품"
    FROM 고객 INNER JOIN 구매현황
    ON 고객."고객번호" = 구매현황."고객번호";

 

LEFT OUTER JOIN - 내부조인

제품 구매 회원과 제품 구매를 하지않는 회원까지 조회

SELECT 고객."고객번호", 고객."이름", 고객."회원등급", 구매현황."상품"
    FROM 고객 LEFT OUTER JOIN 구매현황
    ON 구매현황."고객번호" = 고객."고객번호";

 

제품을 한번도 구매를 하지 않는 회원만 조회하고 싶을때

WHERE를 이용해 조건 넣기

SELECT 고객."고객번호", 고객."이름", 고객."회원등급", 구매현황."상품"
    FROM 고객 LEFT OUTER JOIN 구매현황
    ON 구매현황."고객번호" = 고객."고객번호"
    WHERE 구매현황."상품" IS NULL;

 

 

 

 

RIGHT OUTER JOIN - 아우터 조인

제품을 판매는 했지만 제품을 구매한 회원에 대한 정보가 없음

오류를 찾을 수 있다. (처음부터 제약조건을 잘 설계해 놓으면 오류가 발생될 일이 없어진다)

SELECT 고객."고객번호", 고객."이름", 고객."회원등급", 구매현황."상품"
    FROM 고객 RIGHT OUTER JOIN 구매현황
    ON 구매현황."고객번호" = 고객."고객번호";

 

SELECT 고객."고객번호", 고객."이름", 고객."회원등급", 구매현황."상품"
    FROM 고객 RIGHT OUTER JOIN 구매현황
    ON 구매현황."고객번호" = 고객."고객번호"
WHERE 고객."고객번호" IS NULL;

---

FULL OUTER JOIN이라는 것도 있지만 잘 사용은 안한다.

SELECT 고객."고객번호", 고객."이름", 고객."회원등급", 구매현황."상품"
    FROM 고객 FULL OUTER JOIN 구매현황
    ON 구매현황."고객번호" = 고객."고객번호";

---

- 복습

--EMP, DEPT 테이블이용
--부서번호, 부서명, 사원번호, 이름, 급여를 출력
--급여가 2000이상인 데이터만 출력
SELECT DEPT.DEPTNO, DEPT.DNAME, EMP.EMPNO, EMP.ENAME, EMP.SAL
    FROM DEPT INNER JOIN EMP
    ON DEPT.DEPTNO = EMP.DEPTNO
WHERE EMP.SAL>=2000;

 

 

--EMP, DEPT 사용(부서번호가 일치하는 대상만)
--부서명별 급여의 합계를 출력
SELECT DEPT.DNAME, SUM(EMP.SAL)
    FROM DEPT INNER JOIN EMP
    ON DEPT.DEPTNO = EMP.DEPTNO
    GROUP BY DEPT.DNAME;