DAY 28 - SELF JOIN / UNION / UNION ALL / INTERSECT / MINUS (2024.08.09)

---

[ self 조인 ]

---

select employee_id, manager_id, last_name from employees;

 

[e]

사원번호 - 상사 - 이름

---

select employee_id, last_name from employees order by 1;

 

[m]

사원번호 - 이름

101 - Kochhar - King

102 - De Haan - King

 

• 방법 1

select e.employee_id as 사원번호, e.last_name as 사원이름, m.last_name as 관리자
from employees e, employees m
where m.employee_id = e.manager_id 
order by 1;

 

• 방법 2

select e.employee_id as 사원번호, e.last_name as 사원이름, m.last_name as 관리자
from employees e
join employees m on(m.employee_id = e.manager_id)
order by 1;

---

select * from countries, locations;

select * from countries cross join locations;

되도록 사용하지 말기 !!

---

 

create table salgrade(
salvel varchar2(2),
lowst number, highst number);

insert into salgrade values('A', 20000, 29999);
insert into salgrade values('B', 10000, 19999);
insert into salgrade values('C', 0, 9999);
commit;

select * from salgrade;

---

select last_name, salary, salvel
from employees
join salgrade on(salary between lowst and highst)
order by salary desc;

---

[문제5] 학생 중에 동명이인을 검색하여 이름으로 오름차순하고, 만약 같은 이름은 번호로 오름차순하시오 (SELF JOIN)
테이블 : STUDENT
컬럼 : SNO, SNAME

 

select distinct A.sno, A.sname
from student A, student B
where A.sname = B.sname and A.sno != B.sno
order by 2, 1;

select distinct A.sno, A.sname
from student A
join student B on(A.sname = B.sname and A.sno != B.sno)
order by 2, 1;

 

여기서 실수한 점 !! sname만 같은 사람 찾으면 다 나온다.

sno가 다른 것도 조건으로 걸어야 한다는 점 !!!!

그리고 distinct 이 것도 해야지 중복되는 거 안 나온다.

 

---

[SET operator] - 집합연산자

두 개 이상의 쿼리 결과를 하나로 결합시키는 연산자

 

1. UNION : 양쪽 쿼리를 모두 포함(중복 결과는 1번만 포함) → 합집합

2. UNION ALL : 양쪽 쿼리를 모두 포함(중복 결과도 모두 포함)

3. INTERSECT : 양쪽 쿼리 결과에 모두 포함되는 행만 표현 → 교집합

4. MINUS : 쿼리1 결과에 포함되고 쿼리2 결과에는 포함되지 않는 행만 표현 → 차집합

 

 

오라클의 집합연산자(SET operator) UNION, INTERSECT, MINUS 는 order by 한다.

→ UNION을 사용할 경우 합쳐진 결과에서 중복을 제거한 결과를 반환한다.

     중복을 제거하기 위해서 order by 하기 때문에 컬럼이 많으면 느려진다.

     수가 작은 튜플로 가공 후 사용 하는게 좋다

 

→ UNION ALL 는 order by 하지 않고 무조건 합해준다

     Order by를 하려면 두 번째 쿼리에 작성해야 한다.

 

---

[ 테이블 구조만 복사하는 방법 ]

create table employees_role as select * from employees where 1=0;

 

      → 테이블 구조만 복사된다. 레코드는 들어오지 않는다.

 

select * from employees;

---

select * from employees_role;

구조만 복사된 거 확인할 수 있다.

---

insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);
insert into employees_role values(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);
insert into employees_role values(101, 'Nee', 'Ko', 'NKOCHHAR', '515.123.4568', 
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);
insert into employees_role values(200, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', 
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);
insert into employees_role values(200, 'Nee', 'Kochhar', 'NKOCHHAR', '515.123.4568', 
'1989-09-21', 'AD_VP', 17000.00, NULL, 100, 90);
insert into employees_role values(300, 'GilDong', 'Hong', 'NKOCHHAR', '010-123-4567', 
'2009-03-01', 'IT_PROG', 23000.00, NULL, 100, 90);

commit;

---

[ UNION ]

select employee_id, last_name from employees
union
select employee_id, last_name from employees_role;

---

[ UNION ALL]

select employee_id, last_name from employees
union all
select employee_id, last_name from employees_role;

---

select salary from employees where department_id=10
union all
select salary from employees where department_id=30 order by 1;

---

[ MINUS ]

select employee_id, last_name from employees
minus
select employee_id, last_name from employees_role;

---

[ INTERSECT ]

select employee_id, last_name from employees
intersect
select employee_id, last_name from employees_role;

---

select last_name 사원이름, job_id 업무ID, department_id 부서ID 
from employees
where department_id = 10
UNION
select last_name 사원이름, job_id 업무ID, department_id 부서ID 
from employees_role
where job_id = 'IT_PROG';

---

• 방법1 (join, in연산자 이용)

select last_name, job_title
from employees
join jobs using(job_id)
where job_title in('Stock Manager', 'Programmer');

 

• 방법2 (join, union 이용)

select last_name, job_title
from employees
join jobs using(job_id)
where job_title='Stock Manager'
union
select last_name, job_title
from employees
join jobs using(job_id)
where job_title='Programmer'
order by 2;

---

 

컬럼개수 맞춰야한다 !! 그래서 NULL 쓴 것 / 데이터타입도 맞춰야한다.

select last_name, employee_id, hire_date
from employees
where department_id=20
union
select department_name, department_id, NULL
from departments
where department_id=20;