43일 2024-04-26

데이터베이스

8장 JOIN

두 개 이상의 테이블을 연결하여 하나의 테이블처럼 출력할 때 사용하는 방식.

 

# 등가 조인

테이블을 연결한 후에 출력 행을 각 테이블의 특정 열에 일치한 데이터를 기준으로 선정하는 방식.

------------------
-- 08-2 조인의 종류
------------------

-- 등가 조인 : 같은 값끼리 연결시키는 조인 (우리가 사용하는 대부분의 조인방법)
-- EX) 사원 번호가 101번인 사원의 이름과, 부서명, 그리고 근무도시를 가져오시오.
select first_name, department_name, city
from employees, departments, locations
where 
    employees.department_id = departments.department_id
    and departments.location_id = locations.location_id
    and employee_id = 101;
    
-- EX) 사원 번호가 101번인 사원의 이름과, 부서ID, 그리고 근무도시를 가져오시오.
select first_name, employees.department_id, city  -- employees.department_id 또는 departments.department_id 상관없다. 조인을 해주었기 때문.
from employees, departments, locations
where 
    employees.department_id = departments.department_id
    and departments.location_id = locations.location_id
    and employee_id = 101;

-- EX) 사원 번호가 101번인 사원의 이름과, 부서ID, 근무ID, 그리고 근무도시를 가져오시오.
select first_name, employees.department_id, locations.location_id, city
from employees, departments, locations
where 
    employees.department_id = departments.department_id
    and departments.location_id = locations.location_id
    and employee_id = 101;

-- EX) 사원 번호가 101번인 사원의 이름과, 부서ID, 근무ID, 그리고 근무도시를 가져오시오.
select first_name as 성,
    e.department_id as 부서ID,
    l.location_id as 근무ID, 
    city as 근무도시
from employees e, departments d, locations l
where 
    e.department_id = d.department_id
    and d.location_id = l.location_id
    and employee_id = 101;

-- Ex) 사원 번호가 101번인 사원의 직무명(job_title), 부서명, 도시, 근무대륙을 가져오시오
select e.employee_id as 사원번호,
        e.first_name as 사원이름,
        j.job_title as 직무명,
        d.department_name as 부서명,
        l.city as 도시,
        r.region_name as 근무대륙
from employees e, jobs j, locations l, regions r, departments d, countries c
where 
    e.job_id = j.job_id
    and e.department_id = d.department_id
    and d.location_id = l.location_id
    and l.country_id = c.country_id
    and c.region_id = r.region_id
    and employee_id = 101;

 

 

 

-- EX) 사원 번호가 101번인 사원의 이름과, 부서ID, 그리고 근무도시를 가져오시오.
select first_name, employees.department_id, city
from employees, departments, locations
where 
    employees.department_id = departments.department_id
    and departments.location_id = locations.location_id
    and employee_id = 101;

이 코드 부분에서 department_id컬럼은 employees테이블과 departments테이블에 각각 존재한다.

하지만 각각의 테이블에서 해당 컬럼의 개수는 일치하지 않다.

그래도 join을 해주었기 때문에 employees.department_id 또는 departments.department_id으로 select해도 상관없다.

 

# 비등가 조인

# 종류별 JOIN

-- 2) 비등가 조인 (범위 연결)
create table salgrade (
    grade   number(1) primary key,
    lowsal  number not null,
    highsal number not null
);
insert into salgrade values(1, 1000, 5000);
insert into salgrade values(2, 5000, 10000);
insert into salgrade values(3, 10000, 15000);
insert into salgrade values(4, 15000, 20000);
insert into salgrade values(5, 20000, 30000);
commit;
drop table salgrade;

-- 101번 사원의 월급과 월급 등급을 가져오시오.
select employee_id, salary, grade
from employees e, salgrade s
where salary between lowsal and highsal
    and employee_id = 101;

-- 101번 사원의 월급과 직무 동일 직무의 최소 월급과 최대 월급을 가져오시오.
select employee_id, salary, j.job_title, min_salary, max_salary
from employees e, jobs j
where 
    e.job_id = j.job_id and
    salary = 17000;
    
-- 3) 셀프 조인 (자기 자신의 테이블과 연결)
-- 101번 사원의 관리자(상사)의 이름(first_name)과 전화번호(phone_number)를 가져오시오.
select man.first_name as "매니저 이름", man.phone_number as "매니저 전화번호",
        emp.first_name as "사원 이름", emp.phone_number as "사원 핸드폰 번호"
from employees emp, employees man
where emp.employee_id = 101
    and emp.manager_id = man.employee_id;

-- 4) 외부 조인
-- 한쪽의 데이터는 모두 나오게하고, 다른쪽 테이블의 값이 없는 경우는 null로 출력
-- Ex) 사원 이름과 부서 이름을 출력하시오. (사원 전체)
select employee_id, first_name, department_name
from employees e, departments d
where e.department_id = d.department_id(+)
order by employee_id asc;
-- 사원은 다 나오게하되, 부서가 null인 값도 출력하라.

-- 응용 : 부서가 없는 사원의 아이디와 이름은?
select employee_id, first_name, department_name
from employees e, departments d
where e.department_id = d.department_id(+)
    and d.department_id is null     -- d.department_id는 department_name으로 사용해도 똑같다.
order by employee_id asc;

-- Ex) 부서 이름과 사원 이름을 출력하시오. (부서 전체)
select d.department_id, department_name, first_name
from employees e, departments d
where e.department_id(+) = d.department_id
order by department_id asc;
-- (+)가 붙어있는 쪽은 null값 까지 강제로 출력하라는 의미.
-- 이렇게 출력해야할 이유가 있을 것인가? --> 있다. ex) 사원이 없는 부서를 찾는 경우.


-- 응용: 사원이 없는 부서의 아이디와 이름을 출력하시오.
select d.department_id, department_name, first_name
from employees e, departments d
where e.department_id(+) = d.department_id
    and e.department_id is null;
order by department_id asc;

---------------------------------
-- 08-3 SQL 표준 문법을 사용한 조인
---------------------------------

-- 1) NATURAL JOIN(등가 조인)
select first_name, department_name
from employees e, departments d
where e.department_id = d.department_id
    and employee_id = 101;

-- 조건: 두 테이블에 동일한 이름의 컬럼끼리 (PK/FK)끼리 자동 조인
select first_name, department_name
from employees e natural join departments d
where employee_id = 101;

-- 2) JOIN~USING(등가 조인)
-- 조건: 두 테이블에 주어진 이름의 컬럼끼리 (PK/FK)끼리 자동 조인
select first_name, department_name
from employees e join departments d using(department_id)
where employee_id = 101;

-- 3) JOIN~ON(등가 조인)
select first_name, department_name
from employees e join departments d on (e.department_id = d.department_id)
where employee_id = 101;

-- 여러개를 조인하고 싶다면? 그냥 예시코드
select first_name, department_name
from employees e 
    join departments d on (e.department_id = d.department_id),
    join departments d on (e.department_id = d.department_id),
    join departments d on (e.department_id = d.department_id)
where employee_id = 101;

-- 4) 외부 조인
select employee_id, first_name, department_name
from employees e, departments d
where e.department_id(+) = d.department_id
order by employee_id asc;

-- 정석 문법으로 한다면,
select employee_id, first_name, department_name
from employees e right join departments d
    on (e.department_id = d.department_id)
order by employee_id asc;
-- 조금 헷갈린다. 확실히 공부하자.
-- 실제 프로젝트에서는 등가 조인만 쓴다.

 

 

9장 SQL문 속 또 다른 SQL문, 서브쿼리

# 서브쿼리(subquery)

: SQL문을 실행하는 데 필요한 데이터를 추가로 조회하기 위해 SQL문 내부에서 사용하는 SELECT문을 의미한다.

---------------
-- 09-1 서브쿼리
---------------

-- 서브쿼리는 항상 괄호 '()'로 감싸야한다.
-- 사원 Nancy가 받는 월급보다 더 많은 사원의 이름과 월급은?
select first_name, salary
from employees
where salary > (
    select salary
    from employees
    where first_name = 'Nancy');

-- 도시 Seattle에 근무하는 사원의 이름과 전화번호
select first_name, phone_number
from employees
where department_id in(10, 30, 90, 100);

select department_id
from departments
where location_id = 1700;
-- 서브쿼리로 위의 코드들을 합친 효과를 내보자.
select first_name, phone_number
from employees
where department_id in(
    select department_id
    from departments
    where location_id = (
        select location_id
        from locations
        where city = 'Seattle'));

-- 서브쿼리 대신에 join을 사용할 수 있다.
select first_name, phone_number
from employees e, departments d, locations l
where
    e.department_id = d.department_id and
    d.location_id = l.location_id and
    city = 'Seattle';
-- 대부분의 경우에 서브쿼리가 join문보다 빠르다.

 

# 단일행 서브쿼리

: 실행 결과가 단 하나의 행으로 나오는 서브쿼리.

: 단일행 연산자를 사용하여 비교한다. ( <, <=, >, >=, =, <>, ^=, !=)

------------------------------------
-- 09-2 실행 결과가 하나인 단일행 서브쿼리
------------------------------------

-- 평균 봉급보다 적게 받아가는 사원의 이름과 봉급은?
select first_name, salary
from employees
where salary < (select avg(salary) from employees);

 

# 다중행 서브쿼리

: 실행 결과 행이 여러 개로 나오는 서브쿼리

: 다중행 연산자를 사용해야 메인쿼리와 비교할 수 있다. ( IN, ANY / SOME, ALL, EXISTS )

 

IN : 메인쿼리의 데이터가 서브쿼리의 결과 중 하나라도 일치한 데이터가 있다면 true

ANY 또는 SOME : 메인쿼리의 조건식을 만족하는 서브쿼리의 결과가 하나 이상이면 true

ALL : 메인쿼리의 조건식을 서브쿼리의 결과 모두가 만족하면 true

EXISTS : 서브쿼리의 결과가 존재하면(즉, 행이 1개 이상일 경우) true

---------------------------------------
-- 09-3 실행 결과가 여러 개인 다중행 서브쿼리
---------------------------------------

-- 10번 부서와 20번 부서에 근무하는 사원의 이름과 전화번호
select first_name, phone_number
from employees
where department_id in (10, 20);

select department_id
from departments
where department_name in ('Administration', 'Marketing');
-- 위의 코드를 합쳐서 표현하면?
select first_name, phone_number
from employees
where department_id in (
    select department_id
    from departments
    where department_name in ('Administration', 'Marketing'));

 

# 다중열 서브쿼리

: 서브쿼리의 select절에 비교할 데이터를 여러 개 지정하는 방식.

---------------------------------------
-- 09-4 비교할 열이 여러 개인 다중열 서브쿼리
---------------------------------------

-- 사원의 부서, 직무, 입사날짜
select department_id, job_id, hire_date
from employees;

-- 가장 마지막에 입사한 사원의 부서, 직무, 입사날짜
select department_id, job_id, max(hire_date)
from employees
where department_id is not NULL
group by department_id, job_id
order by department_id asc;

-- 부서 아이디가 10이고, 직무 아이디가 'AD_ASST'이며, 고용일자가 2003-09-17인 사람의 이름과 봉급은?
select first_name, salary
from employees
where (department_id, job_id, hire_date) = ((10, 'AD_ASST', '2003-09-17'));

select first_name, salary
from employees
where (department_id, job_id, hire_date) 
    IN ((10, 'AD_ASST', '2003-09-17'),
        (100, 'FI_MGR', '2002-08-17'));

-- 부서별, 직무별 입사날짜가 가장 마지막인 사원의 이름과 봉급은?
select first_name, salary
from employees
where (department_id, job_id, hire_date) IN(
    select department_id, job_id, max(hire_date)
    from employees
    where department_id is not NULL
    group by department_id, job_id
);

 

---

outer join쪽이 많이 헷갈린다.. 복습하며 연습해보자!

 

# 과제

다음 주 화요일부터 스프링을 다시 배울 것이다.

그 전에 우리가 해야할 것은?

ERD 테이블이 있으면 우리가 물리적 테이블로 만들어야 한다.

그렇게 DB를 구축해야한다.

팀장 데스크탑에 Oracle 설치

 

dto라는 패키지의 데이터 테이블과 동일한 객체를 설계

ex) Departments.java

그리고 필드 설정.

private int departmentId;

 

@Data 어노테이션 붙이기