DATABASE JOIN

Let's Just Go·2022년 4월 28일

Database oracle

DATABASE

목록 보기

3/11

DATABASE

DATABASE JOIN

DATABASE JOIN
- Join
  - 두 개의 테이블을 서로 연관해서 조회하는 것
  - 하나 이상의 테이블로부터 데이터를 질의하기 위해 사용
  - 종류
    - INNER JOIN
    - OUTER JOIN : LEFT, RIGHT, FULL
    - CROSS JOIN
    - SELF JOIN : 자신의 테이블을 한번 더 조인할 때
  - FROM 절에서 지정되며 조인 조건이 ON or USING절에 표현
    - EX) table1 (LEFT | RIGHT | FULL) (OUTER) JOIN table2 ON JOIN_CONDITION

INNER JOIN

INNER JOIN (2개 테이블)

가독성이 좋고 많은 DBMS에서 지원하는 조인 문법
테이블의 이름이 길어지는 문제를 별칭을 통해 해결
한 테이블 INNER JOIN 다른 테이블 ON 조인 조건

‘연결하는 테이블 간 일치하는 값만 조회하라’

SELECT
    e.first_name,
    e.last_name,
    e.hire_date,
    e.salary,
    e.job_id,
    e.department_id,
    d.department_name
FROM
         employees e
    INNER JOIN departments d ON e.department_id = d.department_id;

Oracle JOIN

Oracle에서만 사용하는 JOIN 문법

SELECT
    e.first_name,
    e.last_name,
    e.hire_date,
    e.salary,
    e.job_id,
    e.department_id,
    d.department_name
FROM
    employees   e,
    departments d
WHERE
    e.department_id = d.department_id;
--한 테이블만으로 원하는 결과를 가져오지 못할때 두 테이블의 공통된 속성을 가지고 연결하여 원하는 결과 추출

INNER JOIN(2개 이상 테이블)

2개 이상의 테이블을 연결할 수 있음

SELECT
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name,
    j.job_title
FROM
    employees   e,
    departments d,
    jobs        j
WHERE
        e.department_id = d.department_id
    AND e.job_id = j.job_id;
--departments 테이블의 부서 이름은 110이후로도 더 많음 그 뜻은 두 테이블의 일치 값만 가져온다는 뜻
--employees와 department, jobs 테이블 3개를 연결
--employees 테이블과 department 테이블을 연결하기 위해 부서 id로 연결하고 employees테이블과 job테이블을 연결하기 위해 job_id로 연결


SELECT
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name,
    e.job_id,
    j.job_title,
    loc.city
FROM
    employees   e,
    departments d,
    jobs        j,
    locations   loc
WHERE
        e.department_id = d.department_id
    AND e.job_id = j.job_id -- 3,4
    AND d.location_id = loc.location_id -- 2
    AND loc.state_province = 'California'; -- 1
--WHERE 절에 조인 조건과 일반 조건을 같이 작성
--california인 것들을 조회하고 3개의 테이블을 연결해서 select에 있는 컬럼을 최종 조회  

-- 1. table명 약어로 생성 
-- 2. loc 테이블의 province가 califonia 조건에 맞는 값을 대상으로 필터링
-- 3. location_id값과 같은 값을 가지는 데이터를 departments에서 찾아서 조인 
-- 4. 위의 결과와 동일한 department_id를 가진 employees 테이블의 데이터를 찾음
-- 5. 위의 결과와 jobs 테이블을 비교하여 조인하고 최종 결과 출력

OUTER JOIN

OUTER JOIN

어느 한 테이블에 공통 값이 없더라도 해당 row들이 조회 결과에 모두 포함되는 조인
연결하는 테이블들의 값이 같지 않아도 기준 테이블에 따라 select에서 지정한 모든 테이블의 데이터를 불러오는 것

‘데이터가 없어도 모두 불러와라’

SELECT
    e.first_name,
    e.last_name,
    e.department_id,
    d.department_name
FROM
    employees   e,
    departments d,
    locations loc
WHERE
    e.department_id = d.department_id (+) -- 외부조인
    AND d.location_id = loc.location_id; -- 내부조인 
--(+) : '+가 있는 테이블이 아닌 연결되는 테이블의 데이터가 같지 않아도 모두 가져와라' 라는 뜻
--외부조인은 select에 있는 모든 데이터를 다 가져올 수 있음 
--employees 테이블에는 존재하고, departments 테이블에는 존재하지 않아도 
--(+)가 붙지 않은 테이블을 기준으로 하여 departments 테이블이 조인에 참여하라는 의미를 부여하기 위해 사용
--내부조인과 외부조인을 같이 사용했을때 내부조인이 먼저 수행되므로 외부조인만 사용했을때의 데이터가 사라진 것을 확인

SELECT
    e.employee_id,
    e.first_name,
    e.department_id,
    j.start_date,
    j.end_date,
    j.job_id
FROM
    employees   e,
    job_history j
WHERE
    e.employee_id = j.employee_id(+)
    AND j.department_id(+) = 80;
/*
외부 조인 진행 시 모든 조건에 (+)를 붙여야 하며 
일반 조건에도 (+)를 붙이지 않으면 데이터가 누락되는 현상 발생
일반조건으로 부서 번호가 80번인 애들만 조회하라고 했는데 outer를 사용했으므로 
select에 정의된 모든 data를 불러오므로 outer에서 일반 조건은 의미가 없으
*/

LEFT OUTER JOIN

LEFT OUTER JOIN

왼쪽을 기준으로 왼쪽은 전부 조회하고 없는 경우 null 처리

SELECT
    *
FROM
    info i
    LEFT OUTER JOIN auth a ON i.auth_id = a.auth_id;
--왼쪽을 기준으로 왼쪽의 데이터는 모두 조회

RIGHT OUTER JOIN

RIGHT OUTER JOIN

오른쪽을 기준으로 오른쪽은 전부 조회하고 없는 경우 null처리

SELECT
    *
FROM
    info i
    RIGHT OUTER JOIN auth a ON i.auth_id = a.auth_id;
--오른쪽을 기준으로 왼쪽의 데이터는 모두 조회

FULL OUTER JOIN

FULL OUTER JOIN

양쪽을 기준으로 모든 행이 붙어 조회하고 없는 경우 null 처리

SELECT
    *
FROM
    info i
    FULL OUTER JOIN auth a ON i.auth_id = a.auth_id;
--전체 기준으로 모든 데이터 조회
--좌측 테이블과 우측 테이블 데이터를 모두 읽어 중복된 데이터는 삭제

SELF JOIN

SELF JOIN

하나의 테이블에서 데이터를 조회하고 싶은데 JOIN없이는 조회할 수 없을 때 사용

하나의 테이블에서 얻고 싶은 정보가 같은 테이블에 있을 때 사용

--자기 자신의 테이블을 중복으로 join하여 테이블의 정보를 조회
SELECT
    e1.employee_id,
    e1.first_name,
    e1.manager_id,
    e2.first_name,
    e2.employee_id
FROM
         employees e1
    JOIN employees e2 ON e1.manager_id = e2.employee_id;
--특정 직원의 매니저가 누군지 조회

Practice

Exercise

GROUP BY의 이해를 위해 연습

/* 문제 1
사원 테이블에서 JOB_ID 별 사원 수를 구하세요 
사원 테이블에서 JOB_ID 별 월급의 평균을 구하세요. 월급의 평균 순으로 내림차순 정렬 */

SELECT
    job_id,
    COUNT(job_id),
    AVG(salary)
FROM
    employees
GROUP BY
    job_id
ORDER BY
    AVG(salary);

/* 문제 2
사원 테이블에서 입사 년도 별 사원 수를 구하세요 */

SELECT
    to_char(hire_date, 'YY'),
    COUNT(hire_date)
FROM
    employees
GROUP BY
    to_char(hire_date, 'YY');

/* 문제 3 
급여가 8000이상인 사원들의 부서별 평균 급여를 출력하세요. 단 부서 평균 급여가 2000이상인 부서만 출력 */

SELECT
    department_id,
    trunc(AVG(salary))
FROM
    employees
WHERE
    salary >= 8000
GROUP BY
    department_id
HAVING
    AVG(salary) >= 2000;

/* 문제4
사원 테이블에서 commission_pct 컬럼이 null이 아닌 사람들의 부서별, 월급 평균 합계, count를 구함 
조건1 월급의 평균은 커미션을 적용시킨 월급 
조건2 평균은 소수 2째 자리에서 절삭 */

SELECT
    department_id,
    TRUNC((AVG(salary +(salary * commission_pct))), 2) AS 평균,
    SUM(salary +(salary * commission_pct)) AS 합,
    COUNT(*) AS 수
FROM
    employees
WHERE
    commission_pct IS NOT NULL
GROUP BY
    department_id;

Exercise2

JOIN의 이해를 위해 연습

/*
문제 1 
employee와 departments 테이블을 INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER 조인하세요
*/
SELECT
    *
FROM
         employees e
    INNER JOIN departments d ON e.department_id = d.department_id;
-- 106개의 행이 존재 

SELECT
    *
FROM
    employees   e
    LEFT OUTER JOIN departments d ON e.department_id = d.department_id;
-- 107개의 행이 존재

SELECT
    *
FROM
    employees   e
    RIGHT OUTER JOIN departments d ON e.department_id = d.department_id;
-- 122개의 행이 존재

SELECT
    *
FROM
    employees   e
    FULL OUTER JOIN departments d ON e.department_id = d.department_id;
--123개의 행이 존재

/*
문제2 
employee, department 테이블을 inner join하세요
조건1 employee_id가 200인 사람의 이름, department_id출력 
조건2 이름 컬럼은 first_name과 last_name을 합쳐서 출력 
*/
SELECT
    e.first_name || e.last_name,
    d.department_id
FROM
         employees e
    INNER JOIN departments d ON e.department_id = d.department_id
WHERE
    e.employee_id = 200;

/*
문제3 
employee, job 테이블을 inner join하세요 
모든 사원의 이름과 직무아이디, 직무 타이틀을 출력하고 이름 기준으로 오름차순 정렬 
*/
SELECT
    e.first_name,
    e.job_id,
    j.job_title
FROM
         employees e
    INNER JOIN jobs j ON e.job_id = j.job_id
ORDER BY
    e.first_name ASC;

/*
문제4 
job, job_history 테이블을 left outer join하세요
*/
SELECT
    *
FROM
    jobs        j
    LEFT OUTER JOIN job_history jh ON j.job_id = jh.job_id;

/*
문제5
Steven King의 부서명을 출력하세요
*/
SELECT
    e.first_name || e.last_name,
    d.department_name
FROM
         employees e
    INNER JOIN departments d ON e.department_id = d.department_id
WHERE
        e.first_name = 'Steven'
    AND e.last_name = 'King';

SELECT
    e.first_name || e.last_name,
    d.department_name
FROM
    employees   e
    LEFT OUTER JOIN departments d ON e.department_id = d.department_id
WHERE
        e.first_name = 'Steven'
    AND e.last_name = 'King';

/*
문제6 
employee, departments 테이블을 cartesian product(CROSS JOIN)처리하세요
*/
SELECT
    *
FROM
         employees e
    CROSS JOIN departments d;

/*
문제 7 
사원과 부서 테이블의 부서번호를 조인하고 SA_MAN 사원만의 사원번호, 이름, 급여, 부서명, 그눔지를 출력하세요
*/
SELECT
    e.employee_id AS 사원번호,
    e.first_name AS 이름,
    e.salary AS 급여,
    d.department_name AS 부서이름,
    d.location_id AS 지역번호,
    L.state_province AS 지역이름
FROM
    employees   e
    LEFT OUTER JOIN departments d ON e.department_id = d.department_id
    LEFT OUTER JOIN locations L ON d.location_id = l.location_id
WHERE
    e.job_id = 'SA_MAN';
    
/*
문제8 
employees, job 테이블을 조인 지정하고 job_title이 'Stock Manager', 'Stock Clerk'인 직원 정보만 출력하세요
*/
SELECT e.first_name, e.last_name, e.salary, j.job_title
FROM employees e LEFT OUTER JOIN jobs j ON e.job_id = j.job_id
WHERE j.job_title = 'Stock Manager' or j.job_title = 'Stock Clerk';

/*
문제9
부서 테이블에서 직원이 없는 부서를 찾아 출력하세요.
*/
--manager_id가 없으면 직원이 없으므로 manager_id로 조회
SELECT count(*)
FROM departments d LEFT OUTER JOIN employees e ON d.department_id = e.department_id
WHERE d.manager_id IS NULL;

/*
문제10
join을 이용해서 사원의 이름과 그 사원의 매니저 이름을 출력하세요
*/
SELECT e1.first_name, e2.first_name
FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;

/*
문제11
직원 테이블에서 left join하여 매니저 이름, 매니저 급여까지 출력하세요. 
*/
SELECT DISTINCT e2.first_name, e2.salary
FROM employees e1 INNER JOIN employees e2 ON e1.manager_id = e2.employee_id
ORDER BY e2.salary DESC;

Let's Just Go

안녕하세요! 공부한 내용을 기록하는 공간입니다.

이전 포스트

DATABASE Function

다음 포스트

DATABASE JOIN

DATABASE

DATABASE

DATABASE JOIN

INNER JOIN

OUTER JOIN

LEFT OUTER JOIN

RIGHT OUTER JOIN

FULL OUTER JOIN

SELF JOIN

Practice

DATABASE Function

DATABASE SUBQUERY

0개의 댓글