subquery란?

• select, insert, update, delete에 포함된 쿼리를 말합니다.

• subquery는 반드시 () 안에 기술됩니다.

outerquery란?

• subquery를 포함하는 query를 말합니다.

id가 14인 임직원보다 생일이 빠른 직원 출력

select id, name, birth_date
from employee
where birth_date < (
    select birth_date
    from employee
    where id = 14
);

id가 1인 임직원과 같은 부서, 성별인 임직원들의 id, 이름, 직군을 출력

select id, name, position
from employee
where (dept_id, sex) = (
    select dept_id, sex
    from employee
    where id = 1
);

• subquery의 결과가 하나 이상이고 하나 이상의 결과를 return하는 subquery를 비교할 때는 ()를 이용해서 비교할 attribute를 지정할 수 있습니다.

id가 5인 임직원이 참여한 프로젝트에 참여한 임직원 출력

select distinct empl_id
from works_on
where empl_id != 5 and proj_id in (
    select proj_id
    from works_on
    where empl_id = 5
);

• works_on 테이블에서 해당 임직원이 참여한 프로젝트 id를 찾는 subquery를 통해서 proj_id를 찾아내고 이와 동일환 proj_id를 가진 임직원의 id를 출력하였습니다.

• in은 여러개의 조건들중 하나라도 같다면 true를 return 합니다. 반대로 not in은 모든 값과 값이 다르면 true를 return 합니다.

subquery를 통한 가상의 테이블

select id, name
from employee as a,
    (
        select distinct empl_id
        from works_on
        where empl_id != 5 and proj_id in (
            select proj_id
            from works_on
            where empl_id = 5
        )
    ) as b
where a.id = b.empl_id;

• from 절에 subquery를 이용해서 임시 테이블을 만들어서 query문에 사용할 수 있습니다. 즉, subquery는 where절이 아닌 부분에서 사용될 수 있습니다.

exists

select p.id, p.name
from project as p
where exists (
    select *
    from works_on as w
    where w.proj_id = p.id and w.empl_id in (7, 12)
);

• id가 7 또는 12인 임직원이 참여한 프로젝트의 id와 name을 출력하는 쿼리입니다. 여기서 exists는 ()안에 subquery가 하나의 row라도 return한다면 true를 return 하는 조건절입니다. 그의 반대로는 not exists가 있습니다.

리더보다 높은 연봉을 받는 부서원을 가진 리더의 id, name, salary, 그리고 해당 부서의 가장 높은 연봉을 출력

select e.id, e.name, e.salary,
    (
        select max(salary)
        from employee
        where dept_id = e.dept_id
    ) as dept_max_salary
from department as d, employee as e
where d.leader_id = e.id and e.salary < any (
    select salary
    from employee
    where id != d.leader_id and dept_id = e.dept_id
);

• any는 subquery가 반환한 결과들 중에 단 하나라도 v와의 비교 연산이 true라면 true를 반환합니다. ( some을 사용할 수도 있는데 some과 any는 같은 역할을 합니다. )

• attribute 절에 subquery를 사용할 수 있습니다.

id가 13인 임직원과 한 번도 같은 프로젝트에 참여하지 못한 임직원들의 id, name, dept_id를 출력

select distinct e.id, e.name, e.dept_id
from employee as e, works_on as w
where e.id = w.empl_id
and w.proj_id != all(
    select proj_id
    from works_on
    where empl_id = 13
);

• all은 subquery가 반환환 결과들을 비교 연산하여 모두 true 라면 true를 반환합니다. ( all 대신 not in을 사용할 수도 있습니다. )

select 기본

select * from employee where id = 9;

select name, position from employee where id = 9;

• *을 사용하면 모듣 attribute를 출력하거나 출력하고 싶은 attribute를 select 절 다음에 지정할 수 있습니다.

두 개의 테이블을 연관지어 select

select employee.id, employee.name, position
from project, employee
where project.id = 2002 and project.leader_id = employee.id;

• 위 select 문은 project에서 id가 2002인 leader의 id, name, position을 출력할 때 사용하는 select 문입니다.

as문 사용법

select b.id as 'leader_id', b.name as 'leader_name', b.position
from project as 'a', employee as 'b'
where a.id = 2002 and a.leader_id = b.id;

• 테이블 명이 너무 길어서 selec문의 작성하거나 가시성이 떨어진다면 as문을 사용해서 위의 select문에서만 사용할 테이블명의 별칭을 정의할 수 있습니다.

• as를 이용해서 출력할 때 attribute 이름의 별칭을 지정할 수 있습니다.

distinct 사용법

-- employee 직원
-- project 프로젝트
-- works_on는 employee와 project를 연관짓는 테이블

select p.id, p.name
from employee as e, project as p, works_on as w
where e.position = 'DSGN' and e.id = w.empl_id and p.id = w.proj_id;

-- 중복 tuple을 생략하여 출력
select distinct p.id, p.name
from employee as e, project as p, works_on as w
where e.position = 'DSGN' and e.id = w.empl_id and p.id = w.proj_id;

• 위의 쿼리는 'DSGN' position을 가진 직원들이 어떤 프로젝트에 참여중인지를 확인하기 위한 쿼리입니다. 하지만, 이럴 경우 동일한 프로젝트에서 일하는 'DSGN'들이 있기 때문에 겹치는 프로젝트가 출력됩니다. 이를 위해서 distinct를 사용하여 중복된 tuple을 생략하여 출력할 수 있습니다.

like

select * from employee where position like 'DEV_%' or position like '%SG%';

-- 언더바 3개
select name from employee where name like 'J___';

• like는 찾고자 하는 키워드가 포함된 튜플을 출력할 때 사용합니다. like 와 찾고자하는 키워드 뒤에 %를 붙일경우 찾고자 하는 키워드로 시작하는 튜플을 찾을 수 있습니다.

• %를 사용하지 않고 _ 개수만큼 찾고자하는 키워드 뒤에 글자 수를 제한하여 조건을 지정할 수 있습니다.