[ DataBase - Basic ] - SQL, Subquery로 데이터 조회하기

INSERT

이전에 구현한 DB Schema를 토대로 간단히 알고있는 지식을 바탕으로 실습을 진행해보도록 하겠습니다. 참고로 제가 생각하기에 제가 부족한 것들만 간단히 정리한 것입니다.

 

INSERT INTO employee 
	VALUES (1, 'MESSI', '1987-02-01', 'M', 'DEV_BACK', 10000000, null)
    
# Query OK, 1 row affected (0.05 sec)

INSERT INTO employee (name, birth_date, sex, position, id)
	VALUES ('MESSI', '2000-10-12', 'F', 'DEV_BACK', 3)
    
# Query OK, 1 row affected (0.01 sec)

위와같이 진행할 수 있습니다. 아래와 같이 컬럼을 명시해서 넣고자 하는 값만 넣고 순서에 제약을 받지 않게 할 수도 있습니다.

 

 

INSERT INTO department VALUES
    (1001, 'headquarter', 4)
    (1002, 'HR', 6)
    (1003, 'development', 1)

위와같은 쿼리문을 작성할 수도 있습니다. 이제, 수정을 해보겠습니다.

 

UPDATE employee SET dpept_id = 1003 WHERE id = 1;

이렇게 바꿀 수 있습니다. department에 1003번의 PK가 존재하기 때문에 외래키로 지정해 줄 수 있습니다.

 

DISTINCT

이제 좀 더 나아가서 이전의 스키마를 전 부 다 추가했다고 가정하고, 디자이너들이 참여하고 있는 프로젝트들의 ID와 이름을 알고 싶다고 해보겠습니다.

SELECT DISTINCT P.id, P.name
    FROM employee As E, works_on AS W, project AS P
    WHERE E.position = 'DSGN' and
    	E.id = W.empl_id and W.proj_id = P.id

이렇게 employee에서 여러개의 프로젝트를 중복으로 참여하면, 이를 다 뿌려주면 안되니까 DISTINCT를 통해 select 결과에서 중복되는 tuples은 제외하고 싶을 때 이를 사용합니다.

 

SELECT로 조회할 떄의 주의점

SELECT를 통해 조회할 때 조건들을 포함해서 조회를 한다면 이 조건들과 관련된 attributes에 index가 걸려 있어야 합니다. 그렇지 않다면 데이터가 많아질 수록 조회 속도가 느려지게 됩니다.

 

그리고 다음 서브 쿼리를 보기 전에 우리는 RDBMS로 MySQL(InnoDB)를 사용한 예시입니다. 만약 다른 RDBMS를 사용한다면 문법이 많이 다를 수도 있음을 인지해야 합니다.

 

Subquery

만약 ID가 14인 임직원보다 생일이 빠른 임지원의 ID, 이름, 생일을 알고 싶다고 해 보겠습니다.

SELECT birth_date FROM employee WHERE birth date < (
		SELECT birth_date FROM employee WHERE id = 14
        );

위와같이 subquery(nested query or inner query)를 작성할 수 있습니다. 이전에 많이 작성해 봤기에 예제만 보고 넘어가겠습니다. 그리고 ID가 1인 임직원과 같은 부서 같은 성별인 임직원의 ID와 이름의 직군을 알고 싶다고 하면 아래와 같이 작성할 수 있겠습니다.

 

SELECT id, name, position
    FROM employee
    WHERE (dept_id, sex) = (
        SELECT dept_id, sec
        FROM employee
        WHERE id = 1
);

 

SELECT with subquery

만약 ID가 5인 임직원과 같은 프로젝트에 참여한 임직원의 ID를 알고 싶다고 해 보겠습니다.

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)
    );

이렇게 조건절에 select subquery를 톨해 간단히 조회할 수 있겠죠..?

 

unqualified attribute가 참조하는 table은 뭘까??

해당 attribute가 사용된 query를 포함하여 그 query의 바깥쪽으로 존재하는 모든 queries 중에 해당 attribute 이름을 가지는 가장 가까이에 있는 table을 참조합니다. -> 아마 많이 subquery를 작성해보면 쉽게 이해할 듯합니다.

 

그리고 위 쿼리문에 대해서 empl_id를 works_on 테이블에서 알아냈습니다. 이어서 employee테이블에서 id와 name을 뽑아오고 싶으면 위 쿼리를 그대로 또 subquery로 넣어주면 되겠죠??

SELECT id, name
FROM employee
WHERE id IN (
    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)
        )
);

이렇게 작성하면 정상 작동 할 것입니다. 그리고 FROM 절에도 subquery를 작성할 수 있는데 아래와 같이도 가능합니다.

 

 

SELECT id, name
FROM employee, 
    ( 
        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 DISTINCT_E
WHERE id = DISTINCT_E.empl_id;

이렇게 우리가 subquery로 조회해 온 아이대를 별도의 가상 테이블로써 employee 테이블에서 조회할 수도 있는 것이죠. 즉 맨 아래의 where절은 두개의 table을 join condition으로 묶어준 것이 됩니다.

 

EXISTS with subquery

SELECT P.id, P.name
FROM project P
WHERE EXISTS (
        SELECT *
        FROM works_on W
        WHERE W.proj_id = P.id AND W.empl_id In (7, 12)
);


SELECT P.id, P.name
FROM project P
WHERE id IN (
        SELECT W.proj_id
        FROM works_on W
        WHERE W.empl_id In (7, 12)
);

위와같이 ID가 7 혹은 12인 임직원이 참여한 프로젝트의 ID와 이름을 알고 싶다고 하면 EXISTS 쿼리를 위와같이 작성할 수 있습니다. EXISTS는 그냥 해당 로우가 존재하면 TRUE를 리턴하는 역할을 합니다. 그리고 이는 IN subquery와 상호 운용이 가능해서 저는 별로 쓰지 않는 것 같습니다.

 

 

ANY, ALL with subquery

그 다음으로는 리더보다 높은 연봉을 받는 부서원을 가진 리더의 ID와 이름과 연봉을 알고 싶다고 해 보겠습니다.

SELECT E.id, E.name, E.salary
FROM department D, employee 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
);

 위와같이 E.salary즉 그 앞에 join condition에 의해 해당 E는 leader일 것인데, 이 salary와, subquery를 통해 리더를 제외한 같은 부서의 salary 로우들을 가져와서 그 중에 리더의 salary보다 큰것이 ANY(하나라도 있다면) 해당 리더의 id, name, salary를 조회하는 것입니다.

 

그 다음으로 ID가 13인 임직원과 한번도 같은 프로젝트에 참여하지 못한 임직원들의 ID, 이름, 직군을 알고 싶다고 하면 ALL을 사용하면 간단히 짤 수 있습니다.

 

SELECT DISTINCT E.id, E.name, E.position
FROM employee E, works_on W
WHERE E.id = W.empl_id AND W.proj_id <> ALL (
    SELECT proj_id
    FROM works_on
    WHERE empl_id = 13
);

이렇게 짜면 되겠죠? 여기서  v comparision_operator ALL (subquery) 이건 subquery가 반환한 결과들과 v와의 비교 연산이 모두 TRUE라면 TRUE를 반환합니다.