[DBMS_08] 집합 연산자 DISTINCT, UNION

중복 제거와 관련된 키워드 집합 연산자

DISTINCT, UNION, INTERSECT

---

1. distinct : 중복 제거 명령어

SELECT DISTINCT job_id FROM employees;

 

-- 부서번호가 90이거나 80인 사람들의 부서번호 오름차순, 급여 오름차순으로 정렬

SELECT distinct department_id, salary
FROM employees
WHERE department_id = 90 OR department_id = 80
ORDER BY department_id, salary;

 

---

2. union

union을 통해 위의 코드 수정하기

 

SELECT department_id, salary FROM employees
WHERE department_id = 90
union
SELECT department_id, salary FROM employees
WHERE department_id = 80;

---

union 은 서로 다른 테이블의 자원을 동시에 조회하는것이 가능하다!

SELECT department_id FROM departments
union
SELECT salary FROM employees;

---

3. INTERSECT

-- inetersect : 두 결과의 교집합된 행을 반환

SELECT salary FROM employees WHERE department_id > 90
INTERSECT
SELECT salary FROM employees WHERE department_id <= 80;​

--급여 오름차순, 부서 번호를 출력하되, 부서번호가 100번인 사원은 제외

SELECT distinct salary, department_id 
FROM employees
WHERE department_id not in(100)
ORDER BY salary;

---

4. MINUS

-- 첫번째 SELECT문에 의해 반환되는 행 중에서 두번째 SELECT문이 검색한 내용을 제외하고 출력

SELECT distinct salary, department_id FROM employees
MINUS
SELECT distinct salary, department_id FROM employees
WHERE department_id = 100;