데이터베이스 집합연산자(UNION ,INTERSECT,MINUS ),JOIN,서브쿼리

집합연산자

여러개의 select를 하나로 연결

 

UNION : 두결과의 데이터값을 "합집합" 으로 합치기(중복값은 빼버림)

SELECT EMPNO, ENAME, SAL, DEPTNO 
    FROM EMP
WHERE DEPTNO >= 10
UNION --ALL (ALL을 붙이면 중복제거없이 중복된 데이터도 출력)
SELECT EMPNO, ENAME, SAL, DEPTNO --컬럼이 위의 식과 일치해야함
    FROM EMP
WHERE DEPTNO = 20;

-- 10이상과 20의 값을 합집합되어 출력되어 중복된 20의 해당 데이터는 한번만 출력

 

INTERSECT : 교집합

--INTERSECT(교집합)
SELECT EMPNO,ENAME,SAL
    FROM EMP
WHERE SAL>1000
INTERSECT 
SELECT EMPNO,ENAME,SAL
    FROM EMP
WHERE SAL<2000
--1000이상 2000미만의 데이터만 출력(중복제거)

 

MINUS : 차집합

--MINUS(차집합)
SELECT EMPNO,ENAME,SAL
    FROM EMP
WHERE SAL>1000
MINUS 
SELECT EMPNO,ENAME,SAL
    FROM EMP
WHERE SAL<2000
--2000초과인 데이터만 출력

 

 

---

JOIN 

: 여러개의 테이블을 하나로 합쳐서 하나로 합쳐서 출력

SELECT * FROM EMP,DEPT;

이런식으로 해도 두 테이블의 데이터가 나오긴함 EMP.DEPTNO에 일치되는 DEPT.DEPTNO의 값만 나오는게 아니라

모든 경우의 수를 싹 다 출력되어 원하는 데이터가 아닌 모든 경우의 수 데이터가 나오게

 

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

이런식으로 EMP.   DEPT. 어느 테이블의 DEPTNO값이 일치하는 것만 출력한다는 조건식이 있어야 모든 경우의 수가아닌

DEPTNO와 일치하는 데이터만 나옴

 

INNER JOIN

내부 조인

SELECT EMP.JOB 직책, EMP.DEPTNO 부서번호, DEPT.DNAME 부서명 --원하는 컬럼만 뽑아내기
    FROM EMP INNER JOIN DEPT
    ON EMP.DEPTNO = DEPT.DEPTNO
WHERE EMP.DEPTNO = 20; --조건식넣기

 

OUTER JOIN

외부 조인

SELECT EMP.ENAME 사원이름, EMP.DEPTNO 부서번호, DEPT.DNAME 부서명
    FROM EMP RIGHT OUTER JOIN DEPT --OUTER JOIN은 일치하지않은 데이터도 일단 출력
    ON EMP.DEPTNO = DEPT.DEPTNO; 
    
--    일단 EMP는 EMP.ENAME, EMP.DEPTNO, DEPT.DNAME에 맞는 데이터만 출력
--    FROM EMP RIGHT OUTER JOIN DEPT에서 RIGHT이므로 오른쪽의 테이블 DEPTNO의 데이터가 모두 출력
--    그 모든 출력된 데이터에서 조건의  ON EMP.DEPTNO = DEPT.DEPTNO 데이터만 추려내서 출력
--    그 중 DEPT에는 있지만 EMP에 없는 데이터는 NULL값으로 출력

NULL이 나온이유 : DEPT의 값을 모두 출력한다 EMP에서는  EMP.ENAME, EMP.DEPTNO, DEPT.DNAME 에 해당되는 값만 출력되고 EMP에는 OPERATION에 대한 데이터가 없으므로 NULL로 나옴

 

더 쉬운 예시)

EMP : 1234567
DEPT : 12345678910

SELECT number
    FROM EMP RIGHT OUTER JOIN DEPT --OUTER JOIN은 일치하지않은 데이터도 일단 출력
    ON EMP.1234567 = DEPT.1234567;
    
    결과
    EMP    DEPTNO
    1		1
    2		2
    3		3
    4		4
    5		5
    6		6
    7		7
    NULL	8
    NULL	9
    NULL	10

---

INNER JOIN , OUTER JOIN

EX)

 

 

고객 테이블

구매현황 테이블

INNER JOIN 

select 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        from 고객 inner join 구매현황
        on 고객."고객번호" =  구매현황."고객번호";

 

INNER JOIN 출력된 데이터 결과

 

OUTER JOIN

select 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        from 고객 LEFT OUTER join 구매현황
        on 고객."고객번호" =  구매현황."고객번호";
        
--WHERE 구매현황."상품" IS NULL;(조건식을 넣으면 상품을 구매하지않은 사람의 정보만 볼수있다)

 

OUTER JOIN 출력된 데이터 결

구매하지않은 사람의 정보까지 볼수있음

 

RIGHT OUTER JOIN

select 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        from 고객 RIGHT OUTER join 구매현황
        on 구매현황."고객번호" = 고객."고객번호"
        
        --WHERE 고객."고객번호" IS NULL;
        (고객정보가 NULL 인 데이터만 출력,문제있는 데이터만 출력)

팔리긴했지만 고객정보가 안뜬다 -> 고객의 정보가 잘못입력,잘못된 데이터를 쉽게 찾을 수 있다

 

FULL

select 고객."고객번호",
        고객."이름",
        고객."회원등급",
        구매현황."상품"
        from 고객 FULL OUTER join 구매현황
        on 구매현황."고객번호" = 고객."고객번호";

싹 다 나오게 해준다

 

---

EX)

EMP,DEPT 테이블 활용

부서번호, 부서명, 사원번호,이름,급여를 출력

급여가 2000이상인 데이터만 출력

 

정답

SELECT DEPT.DEPTNO, DEPT.DNAME,EMP.EMPNO, EMP.ENAME, EMP.SAL
    FROM EMP INNER JOIN DEPT
    ON DEPT.DEPTNO = EMP.DEPTNO
WHERE EMP.SAL >= 2000;

 

 

EX)

 EMP,DEPT 테이블 사용 (부서번호가 일치하는 대상만)
부서명별 급여의 합계를 출력

 

정답

SELECT DEPT.DNAME, SUM(EMP.SAL)
    FROM DEPT INNER JOIN EMP
    ON DEPT.DEPTNO = EMP.DEPTNO
GROUP BY DEPT.DNAME;

---

 단일행 서브쿼리

SQL문 안에 SELECT를 작성

(SELECT~~~FROM~~~WHERE)

 

서브쿼리에서의 값은 하나만

비교연산자는 > < = <= >= !=

결과가 여러개가 나오면 오류가 뜬다

 

EX)

DEPT테이블에서 사원이름 (EMP테이블에 있음)이 KING인 사원의 부서명을 출력

SELECT DNAME FROM DEPT WHERE ??????

1.EMP테이블에서 이름이 KING인 사원의 부서번호를 검색

2.DEPT테이블에 부서번호랑 KING사원 부서번호거 같은거를 찾아라

 

SELECT DNAME
    FROM DEPT
WHERE DEPTNO = (SELECT DEPTNO   --KING의 부서번호 찾기 그리고 DEPT테이블내 DEPTNO데이터랑 비교
                    FROM EMP
                WHERE ENAME = 'KING');

 

EX)

EMP테이블 사용 급여가 이름이 CLARK인 급여보다 큰사람들의 모든 정보를 출력

SELECT * 
    FROM EMP
WHERE SAL > (SELECT SAL		--CLARK의 급여데이터를 찾음 그리고 EMP테이블내 급여데이터랑 비교
                FROM EMP
             WHERE ENAME = 'CLARK');

 

EX)

BLAKE보다 먼저 입사한 사람들의 레코드를 조회

SELECT * 
    FROM EMP
WHERE HIREDATE < (SELECT HIREDATE --BLAKE의 입사일데이터를 불러옴 그리고 EMP테이블내 입사일데이터랑 비교
                FROM EMP
             WHERE ENAME = 'BLAKE');

 

EX)

급여가 총급여평균보다 큰 사람들의 레코드를 조회

SELECT * 
    FROM EMP
WHERE SAL > (SELECT AVG(SAL) FROM EMP);

 

다중행 서브쿼리

서브쿼리에서 결과물을 여러개 나오게 작업