[Oracle] 오라클 집합

-- 2024년 2월 1일(목)

 

-- SELECT로 질의된 결과를 하나의 집합으로 인식해서 집합연산을 하는 것

-- 합집합(UNION), 교집합(INTERSECT), 차집합(MINUS)

 

 

*** 교집합(Intersect)

 

select employee_id, first_name

from employees

where employee_id in (145, 147, 158)



intersect



select employee_id, first_name

from employees

where first_name like 'A%';

 

 

 

*** 차집합(Minus)

 

select employee_id, first_name

from employees

where employee_id in (145, 147, 158)



minus



select employee_id, first_name

from employees

where first_name like 'A%';

 

 

-- 위의 집합의 순서를 바꿔서

select employee_id, first_name

from employees

where first_name like 'A%'



minus



select employee_id, first_name

from employees

where employee_id in (145, 147, 158);

 

 

 

*** 합집합(Union) : 중복된 데이터는 1개만 출력됨

 

select employee_id, first_name

from employees

where first_name like 'A%'



union



select employee_id, first_name

from employees

where employee_id in (145, 147, 158);

 

 

-- 중복된 데이터도 다 나오게

select employee_id, first_name

from employees

where first_name like 'A%'



union all



select employee_id, first_name

from employees

where employee_id in (145, 147, 158)

order by first_name;

 

 

** 집합에서 오류가 난 경우

-- 1) 컬럼의 개수가 다르면 다른 집합으로 인지됨

select employee_id, first_name, salary

from employees

where first_name like 'A%'



union all



select employee_id, first_name

from employees

where employee_id in (145, 147, 158);

 

 

-- 2) 컬럼의 개수는 같지만 타입이 다르면 오류

select employee_id, first_name, hire_date

from employees

where first_name like 'A%'



union all



select employee_id, first_name, salary

from employees

where employee_id in (145, 147, 158);

 

 

-- 3) 실행 오류가 아닌 논리적인 오류

-- 타입과 개수는 같지만 컬렴이 달라서 결과가 이상해짐 (의미없는 코드)

select employee_id, first_name, last_name

from employees

where first_name like 'A%'



union



select employee_id, first_name, phone_number

from employees

where employee_id in (145, 147, 158);