[ DataBase - Basic ] - three-valued logic, join, aggregating

SQL에서 NULL의 의미

SQL에서 NULL은 unknown, unavailable or withheld, not applicable이라는 3가지 의미를 가지게 됩니다. 예를 들어 진짜로 모를 수도, 민감한 정보여서 숨긴걸 수도, 호환이 되지 않는 정보일 수도 있는 것이라는 겁니다. 아래의 예시를 보겠습니다.

 

만약 14, 15번의 birth_date가 모두 NULL인데, 생일이 같다고 판단할 수는 없을 겁니다. 그래서 실제로 NULL과 비교할 때는 "="을 사용하면 안되고 "IS NULL" 이렇게 비교해 주어야 합니다.

 

NULL과 three-valued logic

기본적으로 위 그림림에서 birth_date = "1920-02-10" 이렇게 하게되면 NULL은 false일까요?. SQL에서 NULL과 비교연산을 하게 되면 그 결과는 UNKNOWN입니다. UNKNOWN은 'True'일 수도 있고 'False'일 수도 있다는 의미입니다. three-valued logic은 비교/논리 연산의 결과로 TRUE, FALSE, UNKNOWN을 가진다는 것입니다.

 

그리고 위는 AND, OR, NOT 연산을 UNKNOWN과 했을 떄의 연산 결과를 나타냅니다. 즉 WHERE 절에서의 condition(s)의 결과가 FALSE이거나 UNKNOWN이면 tuple은 선택되지 않음에 주의해야 합니다. 오직 TRUE인 tuple(s)만 선택되는 것이죠.

 

만약 위 지식을 바탕으로 2000년대생이 없는 부서의 ID와 이름을 알고 싶다고 해보겠습니다.

SELECT D.id, D.name
FROM department AS D
WHERE D.id NOT IN (
    SELECT E.dept_id
    FROM employee E
    WHERE E.birth_Date >= '2000-01-01'
);

만약 이렇게 작성되었다고 해 봅시다, employee의 department가 NULL일 수도 있는데, 그럼 해당 서브쿼리와 NOT IN이 연산된 결과는 FALSE or UNKNOWN이 됩니다. 그래서 TRUE가 절대 될 수 없는 것이죠. 이를 해결하려면 아래와 같이 조건을 변경해주어야 합니다.

 

SELECT D.id, D.name
FROM department AS D
WHERE D.id NOT IN (
    SELECT E.dept_id
    FROM employee E
    WHERE E.birth_Date >= '2000-01-01'
        AND E.dept_id IS NOT NULL
);

그래야 dept_id가 유효한 것만 들고와서 비교를 할 수 있게 되는 것이죠. 또한 이는 NOT EXISTS를 통해 해결 할 수도 있는데, 아래와 같이 작성할 수 있습니다.

 

SELECT D.id, D.name
FROM department AS D
WHERE NOT EXISTS (
    SELECT *
    FROM employee E
    WHERE E.dept_id = D.id AND E.birth_Date >= '2000-01-01'
);

이렇게 하면 D.id와 E.dept_id 를 join condition으로 주어서 만약 해당하는 로우가 한개도 없다면 출력할 수 있게 되는 것이지요.

 

Implicit Join, Explicit Join

SELECT D.name
FROM employee AS E, department AS D
WHERE E.id = 1 AND E.dept_id = D.id;

위와같은 결과는 Implicit Join의 결과입니다. 그 이유는 E.dept_id와 D.id를 암묵적으로 join해서 D.name을 출력했기 때문이죠. 즉 이는 from 절에는 table들만 나열하고 where절에 join condition을 명시하는 방식입니다. 이는 where 절에 selection condition과 join condition이 같이 있기 때문에 가독성이 매우 떨어지게 됩니다.

 

SELECT D.name
FROM employee AS E JOIN department AS D ON E.dept_id = D.id
WHERE E.id = 1;

이렇게 explicit join 즉 from절에 JOIN 키워드와 함께 joined table을 명시하게 되면 가독성이 좋아지고, 복잡한 join 쿼리 작성 중에도 실수할 가능성이 적어지게 됩니다.

 

 

Inner Join

사실 우리가 JOIN 앞에 (INNER)를 생략했던 것입니다. 이는 간단히 말해서 두 table에서 join condition을 만족하는 tuple들로 result table을 만드는 join으로써, join condition에서 null값을 가지는 tuple은 result table에 포함되지 못한다는 특징이 있습니다. 위의 예씨에서 E.dept_id 즉 부정된 부서가 없다고 하면, 이와의 연산 결과는 Unknown이 될 것입니다(Unknown은 True가 아님). 그래서 포함되지 않는 것이죠.

 

Outer(=Left, Right, Full) Join

 

이는 Inner Join과는 다르게 join condition이 True가 아니더라도 result table에 포함합니다. 이에는 LEFT, RIGHT, FULL Join등이 있습니다. 

 

equi Join

이는 간단합니다. 그냥 condition에 "=" 비교 연산자를 사용하면 되는 것이죠. 우리가 앞에서 살펴본 것 모두가 equi join인 것입니다.

 

Using

이는 두 table이 equi join을 할 때 join하는 attribute의 이름이 같다면, USING으로 간단하게 작성할 수 있게 되는 것입니다. 만약 Department의 PK(id)를 dept_id로 변경하고 아래 SQL을 작성하면 맨 아래와 같은 표가 나오게 된다는 것이죠. 원래는 dept_id가 2번 표시가 되었을 겁니다.

SELECT *
    FROM employee E INNER JOIN department D USING (dept_id);

 

natrual join

이는 두 table에서 같은 이름을 가지는 모든 attribute pair에 대해서 equi join을 수행하는 것을 말합니다. 그래서 join condition을 따로 명시하지 않습니다. 기존의 스키마를 조금 바꿔서 Department의 name을 Employee의 id와 natural join되지 않게 dept_name으로 바꾸어 주었습니다.

 

SELECT *
    FROM employee E NATURAL INNER JOIN department D;

이렇게 사용하면 아래와 같은 결과가 나오게 됩니다. ( 이전의 USING 결과와 같음 )

 

cross join

cross join은 두 table의 tuple pair로 만들 수 있는 모든 조합 (=Cartesian product)를 result table로 변환합니다. 이 또한 join condition이 없습니다.

 

SELECT *
    FROM employee CROSS JOIN department;
    
SELECT *
    FROM employee, department;

 

이렇게 모든 조합을 구할 수 있게 됩니다.

 

self join

만약 ID가 2001인 프로젝트에 참여한 임직원들의 이름과 직군과 소속 부서 이름을 알고 싶다고 해 봅시다.

SELECT E.name AS empl_name, E.position AS empl_position, D.name AS dept_name
FROM works_on W JOIN employee E ON W.empl_id = E.id
LEFT JOIN department D ON E.dept_id = D.id
WHERE W.proj_id = 2001;

이렇게 해 줄 수 있겠습니다. 여기서의 핵심은 LEFT JOIN인데, dept_id가 null인 경우에도 표시해 주어야 하기 때문입니다. 

 

 

Count

먼저 각 row의 수를 카운팅하고 싶다면 아래와 같이 사용해주면 됩니다 (NULL 포함)

SELECT COUNT(*) FROM employee;

SELECT COUNT(position) FROM employee

아래와 같이 특정 컬럼의 개수를 샐 때에는 중복값 포함해서 값을 샙니다. 뒤에서도 볼꺼지만 여기서 DISTINCT를 통해서 개수를 새는 방법도 있습니다. 또한 position컬럼에서 NULL인 로우의 개수는 새지 않습니다. 그래서 특정 컬럼의 로우값이 NULL이라면 카운트 값이 다르게 나올 수 있다는 점 유의해야 합니다.

 

이제 다시 예시입니다. 프로젝트 2002에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다면 어떻게 해야할까요?

SELECT COUNT(*). MAX(salary), MIN(salary), AVG(Salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
WHERE W.proj_id = 2002;

위와같이 작성될 수 있습니다.

 

Group by

이제 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 알고 싶다고 해 보겠습니다.

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
GROUP BY W.proj_id;

이는 말 그대로 관심있는 attribute(s) 기준으로 그룹을 나누어서 그룹별로 aggregate function을 적용하고 싶을 때 사용합니다. 여기서 W.porj_id가 grouping attribute(s)가 됩니다. 그리고 만약 grouping attribute(s)에 NULL값이 있을 때에는 NULL값을 가지는 tuple끼리 따로 묶습니다.

 

Having

이는 GROUP BY를 통해 묶었을 때의 결과에 대한 condition을 작성하기 위해 사용됩니다. 만약 위의 예시에서 프로젝트 참여 인원이 7명 이상인 프로젝트들에 대해서 각 프로젝트에 참여한 임직원 수와 최대 연봉과 최소 연봉과 평균 연봉을 구하고 싶다고 해 보겠습니다.

SELECT W.proj_id, COUNT(*), MAX(salary), MIN(salary), AVG(salary)
FROM works_on W JOIN employee E ON W.empl_id = E.id
GROUP BY W.proj_id
HAVING COUNT(*) >= 7;

이렇게 해서 쿼리문을 작성할 수 있게 됩니다. 즉 aggregate function의 결괏값을 바탕으로 그룹을 필터링하고 싶을 때 사용합니다.

 

Examples

각 부서별 인원수를 인원수가 많은 순서대로 정렬해서 알고싶다고 해 보겠습니다.

SELECT dept_id, COUNT(*) AS empl_count FROM employee
GROUP BY dept_id
ORDER BY empl_count DESC;

이렇게 하면 dept_id가 NULL인 것도 표시가 되겠죠??

 

그 다음음 예시로는 회사 전체 평균 연봉보다 평균 연봉이 적은 부서들의 평균 연봉을 알고 싶다고 해 봅시다.

SELECT dept_id, AVG(salary)
FROM employee
GROUP BY dept_id
HAVING AVG(salary) < (
    SELECT AVG(salary) FROM employee
);

이렇게 서브 쿼리로 모든 employee 평균 연봉을 구한다음에 HAVING으로 작은 경우에만 출력하게 해주면 되겠죠??

 

그 다음으로는 각 프로젝트별로 프로젝트에 참여한 90년대생들의 수와 이들의 평균 연봉을 알고 싶다고 해 보겠습니다.

SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ONW.empl_id = E.id
WHERE E.birth_date BETWEEN '1900-01-01' AND '1999-12-31'
GROUP BY W.proj_id;

이렇게 해주면 되겠습니다. 그리고 더 넘어서 위 예시에서 프로젝트 참여 인원이 7명 이상이고 프로젝트 아이디를 기준으로 오름차순 정렬하고 싶다면 어떻게 해야할까요??

SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ONW.empl_id = E.id
WHERE E.birth_date BETWEEN '1900-01-01' AND '1999-12-31'
GROUP BY W.proj_id
HAVING COUNT(*) >= 7
ORDER BY W.proj_id;

이렇게 하는게 맞을까요,,? 아닙니다! COUNT(*) 로 묶은 것은 프로젝트에서 90년대 생의 명수가 7명 이상인 것을 출력하게 됩니다. 그럼 프로젝트 참여 인원이 7명 이상인 프로젝트에 한정해서 출력하려면 어떻게 해야할까요??

 

SELECT proj_id, COUNT(*), ROUND(AVG(salary), 0)
FROM works_on W JOIN employee E ONW.empl_id = E.id
WHERE E.birth_date BETWEEN '1900-01-01' AND '1999-12-31'
    AND W.proj_id IN ( 
        SELECT proj_id FROM works_on
        GROUP BY proj_id HAVING COUNT(*) >= 7)
GROUP BY W.proj_id
ORDER BY W.proj_id;

바로 이렇게 서브쿼리를 이용해서 AND로 조건문을 추가해줘야 하는 것이죠!