MariaDB - 데이터 검색 (SUBQUERY)

SUBQUERY

-- 맛보기) 현재 Fai Bale이 근무하는 부서에서 근무하는 직원의 사번, 전체 이름을 출력해보세요.

SELECT 
    *
FROM
    employees a,
    dept_emp b
WHERE
    a.emp_no = b.emp_no
        AND b.to_date = '9999-01-01'
        AND CONCAT(a.first_name, ' ', a.last_name) = 'Fai Bale';

SELECT 
    a.emp_no, a.first_name
FROM
    employees a,
    dept_emp b
WHERE
    a.emp_no = b.emp_no
        AND b.to_date = '9999-01-01'
        AND b.dept_no = 'd004';      

-- 위 두 쿼리를 합쳐보자.

SELECT 
    a.emp_no, a.first_name
FROM
    employees a,
    dept_emp b
WHERE
    a.emp_no = b.emp_no
        AND b.to_date = '9999-01-01'
        AND b.dept_no = (SELECT 
                             b.dept_no
                         FROM
                             employees a,
                             dept_emp b
                         WHERE
                             a.emp_no = b.emp_no
                                 AND b.to_date = '9999-01-01'
                                 AND CONCAT(a.first_name, ' ', a.last_name) = 'Fai Bale');        

SUBQUERY

• 하나의 SELECT(SQL)문 안에 포함되어 있는 SELECT 문장
• 여러 절에서 사용할 수 있으나 SELECT문안에 포함되어 있는 것이 일반적

---

Subquery의 문형

SELECT 검색할 컬럼들
   FROM 테이블명
 WHERE 형식 연산자   ( SELECT 검색할 칼럼들
                                    FROM 테이블 명
                                    …                             )

• 서브쿼리는 괄호로 묶여 있어야 한다.
• 서브쿼리 내에서 ORDER BY 절을 포함할 수 없음
• 서브쿼리는 거의 모든 구문에서 사용이 가능(GROUP BY 절 제외)
• 형식 연산자
  • 단일행 연산자 ( =, >, >=, <, <=, <> )
  • 복수행 연산자 ( IN, ANY, ALL, NOT IN )

---

단일행 서브쿼리

• 메인 쿼리로 전달되는 행이 단 하나인 경우
• 단일행 연산자를 사용한다.

다중행 서브쿼리

• 메인 쿼리로 전달되는 행이 여러 개인 경우
• 다중 행 연산자 IN, ANY, ALL를 사용한다.

---

예제

-- 1) from 절

select now() as n, sysdate() s, 3+1 c;

select s.n, s.s, s.c
from (select now() as n, sysdate() s, 3+1 c) as s;

-- 2-1) where : 단일행

-- 단일행 연산자 : = > < <= >= !=

-- ex ) print emp_no, full name of each employee who works in the department which "Fai Bale" currently works

select e.emp_no, concat(first_name, ' ',last_name) as name
from dept_emp de
join employees e on e.emp_no = de.emp_no
join departments d on d.dept_no = de.dept_no
where d.dept_name = (select d.dept_name
                    from dept_emp de
                    join employees e on e.emp_no = de.emp_no
                    join departments d on d.dept_no = de.dept_no
                    where de.to_date >= current_date
                    and e.first_name = "Fai" and e.last_name = "Bale")
;

-- ex ) show name, salary of each employee who's current salary is less than average employees' salary

select first_name, salary
from employees e
join salaries s on e.emp_no = s.emp_no
where salary < (select avg(salary)
                from salaries
                where to_date >= current_date)
and s.to_date >= current_date
order by salary desc
;

-- ex ) show current lowest average salary of the title

-- method 1) min(avg(salary))

select title, min(s.title_salary)
from (select title, avg(salary) as title_salary
        from titles t
        join salaries s on s.emp_no = t.emp_no
        where t.to_date >= current_date and s.to_date >= current_date
        group by title) as s
;

-- method 2) top-k : mysql에서만 제공하는 것

select title, avg(salary) as title_salary
from titles t
join salaries s on s.emp_no = t.emp_no
where t.to_date >= current_date and s.to_date >= current_date
group by title
order by title_salary
limit 0,1 -- 0,3
;

-- method 3) having

SELECT 
    title, AVG(salary) AS title_salary
FROM
    titles t
        JOIN
    salaries s ON s.emp_no = t.emp_no
WHERE
    t.to_date >= CURRENT_DATE
        AND s.to_date >= CURRENT_DATE
GROUP BY title
HAVING title_salary = (SELECT 
        MIN(title_salary)
    FROM
        (SELECT 
            AVG(salary) AS title_salary
        FROM
            titles t
        JOIN salaries s ON s.emp_no = t.emp_no
        WHERE
            t.to_date >= CURRENT_DATE
                AND s.to_date >= CURRENT_DATE
        GROUP BY title) AS a)
;

-- 2-2) where : 복수행
-- 복수행 연산자 : in, not in, any, all

-- any 사용법
-- 1. = any : equals with "in"
-- 2. > any : minimum
-- 3. > any : maximum
-- 4. <> any : equals with "not in"

-- ex ) show employee's name who currently got paid more than 50000

select first_name, salary
from employees e
join salaries s on e.emp_no = s.emp_no
where to_date >= current_date
and salary in (select salary
                from salaries
                where to_date >= current_date and salary > 50000)
order by salary
;