MariaDB - 데이터 검색 (JOIN)

JOIN

하나 이상의 테이블로부터 연관된 데이터를 검색해 오는 방법

Primary Key(PK) 와 Foreign Key(FK) 값의 연관에 의해 JOIN이 성립
( 아닌 경우도 있다. 논리적인 값들의 연관으로만 성립 가능)

• 조인의 기본 유형
  • equijoin : =(equal) 연산자를 사용하는 조인
  • inner join : 조인 조건을 만족하는 행에 대해서만 결과값이 나오는 조인
  • outter join : 조인 조건을 만족하지 않아도 출력이 가능해야하는 결과를 얻고자 할 때 사용

---

EQUIJOIN

• Equal 관계만으로 하나의 테이블로 걀합한 결과

• EQUIJOIN의 문형
• SELECT 테이블명.컬럼명, 테이블명.컬럼명. … FROM 테이블1, 테이블2 WHERE 테이블1.컬럼1 = 테이블2.컬럼2
• 테이블명.컬럼명 : 검색해올 데이터가 어디에서 오는지 테이블과 컬럼을 밝혀둔다.
• 테이블1.칼럼1 = 테이블2.컬럼2 : 두 테이블간에 논리적으로 값을 연결시키는 칼럼간의 조건을 기술한다.
• 컬럼에 있는 값들이 정확히 일치한는 경우에 = 연산자를 사용해서 조인
• 일반적으로 PK-FK 관계에 의하여 JOIN이 성립
• WHERE 절 혹은 ON절을 이용
• 액세스 효율을 향상시키고 좀더 명확히 하기 위해 칼럼이름앞에 테이블 이름을 밝힌다.
• 같은 이름의 칼럼이 조인대상 테이블에 존재하면 반드시 컬럼이름앞에 테이블이름을 밝혀주어야 한다.
• JOIN을 위한 테이블이 N개라고 하면 JOIN을 위한 최소한의 =조건은 N-1이다.

---

Alias 사용

• 테이블명.칼럽명 으로 기술할 때, 테이블명이 길어지는 경우는 많은 시간이 소요되므로 ALIAS를 지정하고 ALIAS가 지정되면 지정된 ALIAS만 사용해야 한다.
• ALIAS를 사용하면 칼럼헤딩에 대한 애매함을 피할 수 있다.

추가적인 조건 기술

• WHERE절에 JOIN조건 이외의 추가적인 조건을 가질 수 있다.
• 조인을 만족하는 데이터중 특정행만 선택하여 결과를 얻고 싶을 때 추가조건을
  AND로 연결한다.

Cartesian Join

• Join 에 대한 조건이 생략되거나 잘못 기술되어 한 테이블에 있는 모든 행들이 다르 테이블에 있는 모든 행들과 Join이 되어서 얻어진 경우을 Cartesian Produc한다
• Cartesian Product를 얻지 않기 위해서 반드시 WHERE 절을 써 준다.
• Natural Join
• 두 테이블에 공통 칼럽이 있는 경우 별다른 조인 조건없이 공통 칼럼처럼 묵시적으로 조인이 되는 유형
• ANSI / ISO SQL1999를 따르는 ANSI JOIN 문법.

JOIN~ USING

• Natural join의 문제점 : 조인하고자 하는 두 테이블에 같은 이름이 칼럼이 많을 때 위와 같을 시 특정한 칼럼으로만 조인하고 싶다면 USING절을 사용해서 기술한다.
• ANSI / ISO SQL1999를 따르는 ANSI JOIN 문법.

JOIN ~ ON

• 공통된 이름의 칼럼이 없는 경우 가장 보편적으로 사용할 수 있는 유형
• WHERE 절에 일반조건 만 쓸 수 있게하고 조인 조건은 ON에 두어 보다 의미를 명확히 하고 알아보기 도 쉽다.
• ANSI / ISO SQL1999를 따르는 ANSI JOIN 문법.

---

예제

-- 예제1
-- employees 테이블과 titles 테이블을 join하여 직원의 이름과 직책을 모두 출력 하세요.

SELECT 
    CONCAT(a.first_name, ' ', a.last_name) AS name, b.title
FROM
    employees a,
    titles b
WHERE
    a.emp_no = b.emp_no;

-- employees 테이블과 titles 테이블을 join하여 직원의 이름과 직책을 출력하되 여성엔지니어만 출력하세요.

SELECT 
    CONCAT(a.first_name, ' ', a.last_name) AS name, b.title
FROM
    employees a,
    titles b
WHERE
    a.emp_no = b.emp_no AND a.gender = 'f'
        AND b.title = 'engineer';

-- natural join
-- 두 테이블에 공통 칼럼이 있으면 별다른 조인 조건없이 묵시적으로 조인됨
-- 쓸일이 없음

SELECT 
    CONCAT(a.first_name, ' ', a.last_name) AS name, b.title
FROM
    employees a
        JOIN
    titles b;

-- on a. emp_no = b.emp_no 생략

-- join ~ using

SELECT 
    CONCAT(a.first_name, ' ', a.last_name) AS name, b.title
FROM
    employees a
        JOIN
    titles b USING (emp_no);

-- 예제 3: 직원의 이름과 직책을 모두 출력 하되 여성 엔지니어만 출력 하세요(join~on)

SELECT 
    CONCAT(a.first_name, ' ', a.last_name) AS name, b.title
FROM
    employees a
        JOIN
    titles b ON a.emp_no = b.emp_no
        JOIN
    salaries c ON b.emp_no = c.emp_no
WHERE
    a.gender = 'f' AND b.title = 'engineer';

-- 실습문제 1:
-- 현재 직원별 근무부서를 사번, 직원전체이름, 근무부서 형태로 출력해 보세요.

select count(*)
from
    employees a
    join
        dept_emp b on a.emp_no = b.emp_no
        join 
    departments c on b.dept_no = c.dept_no
        where b.to_date = '9999-01-01';

-- 실습문제 2: 현재 회사에서 지급되고 있는 급여체계를 반영한 결과를 출력하세요.사번, 전체이름, 연봉 이런 형태로 출력하세요.

SELECT 
    a.emp_no,
    CONCAT(b.first_name, ' ', b.last_name) AS name,
    a.salary
FROM
    salaries a,
    employees b
WHERE
    a.emp_no = b.emp_no
        AND a.to_date = '9999-01-01';

-- 예제5 : 현재 직책별로 평균 연봉과 인원수를 구하되 직책별로 인원이 100명 이상인 직책만 출력하세요.

  select a.title, avg(salary), count(*)
    from titles a, salaries b
   where a.emp_no = b.emp_no
     and a.to_date = '9999-01-01'
     and b.to_date = '9999-01-01'
group by a.title
  having count(*) >= 100
order by avg(salary) desc; 

-- 예제6: 현재 부서별로 현재 직책이 Engineer인 직원들에 대해서만 평균급여를 구하세요.

   select d.dept_name, avg(b.salary)
     from dept_emp a, salaries b, titles c, departments d
    where a.emp_no = b.emp_no
      and b.emp_no = c.emp_no
      and a.dept_no = d.dept_no
      and a.to_date = '9999-01-01'
      and b.to_date = '9999-01-01'
      and c.to_date = '9999-01-01'
      and c.title = 'Engineer'
 group by d.dept_name;     

-- 예제7: 현재 직책별로 급여의 총합을 구하되 Engineer직책은 제외하세요.
-- 단, 총합이 2,000,000,000이상인 직책만 나타내며 급여총합에 대해서 내림차순(DESC)로 정렬하세요.

  select a.title, sum(b.salary) as sum_salary
    from titles a, salaries b
   where a.emp_no = b.emp_no
     and a.to_date = '9999-01-01'
     and b.to_date = '9999-01-01'
     and a.title != 'Engineer'
group by a.title
  having  sum_salary > 2000000000
order by sum_salary desc;