쿼리가 오류 없이 실행되고 합리적으로 보이는 숫자를 반환하지만 행이 누락되고 평균이 잘못되었습니다. SQL NULL 값이 원인인 경우가 많습니다. NULL은 일반적인 값처럼 동작하지 않으므로 초보자와 숙련된 개발자 모두에게 적합합니다.

이 가이드는 NULL이 어떻게 작동하는지 정확하게 보여줍니다. 우리는 SQLite 3.50에서 모든 쿼리를 실행했으며 표시된 결과는 해당 실행에서 나왔습니다. 이 동작의 대부분은 표준 SQL이며 NULL 정렬 순서, Oracle의 빈 문자열 처리 등 데이터베이스가 다른 점을 확인합니다.

NULL이란 무엇입니까?

NULL은 값이 누락되었거나 알 수 없음을 의미하는 표시입니다. 이는 일반적인 의미의 값이 아니므로 모두 다릅니다.

특히 금액의 차이가 중요합니다. 보너스 0은 그 사람이 아무것도 받지 못했다는 것을 의미합니다. NULL 보너스는 아직 아무도 모른다는 의미입니다.

샘플 테이블

CREATE TABLE employees (
  id INT, name TEXT, phone TEXT, bonus INT, manager_id INT
);

INSERT INTO employees VALUES
 (1,'Meera','555-0101',500,NULL),
 (2,'Arjun',NULL,300,1),
 (3,'Sara','',NULL,1),
 (4,'Ravi',NULL,NULL,2);

Sara에는 전화에 대한 빈 문자열이 있는 반면 Arjun과 Ravi에는 NULL이 있습니다. 두 사람은 매우 다르게 취급됩니다.

NULL 테스트

거의 모든 사람이 처음에 저지르는 실수는 다음과 같습니다.

SELECT name FROM employees WHERE phone = NULL;
-- (no rows)

SELECT name FROM employees WHERE phone IS NULL;
-- Arjun
-- Ravi

SELECT name FROM employees WHERE phone IS NOT NULL;
-- Meera
-- Sara

첫 번째 쿼리는 유효한 SQL이며 오류가 없습니다. NULL과 비교하는 것은 결코 사실이 아니기 때문에 단순히 아무것도 반환하지 않습니다. 빈 문자열은 실제 값이므로 Sara는 IS NOT NULL 아래에 표시됩니다. 그러나 Oracle에서는 빈 문자열이 NULL로 처리되는데 이는 주목할 만한 예외입니다.

세 가지 값 논리

대부분의 프로그래밍 언어에서 조건은 true 또는 false입니다. SQL은 세 번째 결과인 unknown을 추가합니다. 값을 모르면 5인지 말할 수 없습니다. 그리고 알 수 없는 두 값이 같은지 알 수 없으므로 NULL = NULL가 참이 아닙니다. 테스트에서는 모두 NULL을 반환했습니다.

SELECT NULL = NULL, NULL <> 1, 1 + NULL, 'a' || NULL;

-- NULL | NULL | NULL | NULL

결합 조건은 신중한 규칙을 따릅니다. 다음은 실행 결과입니다. 여기서 1은 true이고 0은 false입니다.

WHERE에서 사라지는 행

WHERE은 조건이 true인 경우에만 행을 유지합니다. 결과를 알 수 없는 행은 거짓인 행과 함께 삭제됩니다. 이는 다음과 같은 놀라운 결과를 가져옵니다.

SELECT name FROM employees WHERE phone <> '555-0101';

-- Sara

Arjun과 Ravi의 전화번호가 확실히 그 번호가 아니기 때문에 나타날 것으로 예상할 수 있습니다. 하지만 그들의 휴대폰이 알려지지 않아 결과도 알 수 없고 그들은 사라진다. 이를 포함하려면 다음과 같이 명시적으로 말하세요: WHERE phone <> '555-0101' OR phone IS NULL. 부정은 다음 중 하나에도 도움이 되지 않습니다.

SELECT name FROM employees WHERE NOT (bonus > 400);

-- Arjun

Sara와 Ravi는 보너스가 없으므로 bonus > 400에도 그 반대에도 나타나지 않습니다. 기억해야 할 핵심 사항은 조건과 그 부정이 항상 모든 행을 포함하지는 않는다는 것입니다.

COALESCE: 대체 제공

COALESCE은 목록에서 NULL이 아닌 첫 번째 값을 반환하며 모든 주요 데이터베이스에서 작동합니다.

SELECT COALESCE(NULL, NULL, 'third', 'fourth');
-- third

SELECT name, COALESCE(phone, 'no phone') FROM employees;
-- Meera | 555-0101
-- Arjun | no phone
-- Sara  |
-- Ravi  | no phone

빈 문자열은 NULL이 아니기 때문에 Sara는 여전히 공백으로 나타납니다. MySQL과 SQLite의 IFNULL, SQL Server의 ISNULL, Oracle의 NVL과 같은 유사한 기능이 존재합니다. COALESCE는 휴대용 선택입니다. 계산에 대한 이전 요점도 여기에 적용됩니다. bonus + 100는 보너스가 없는 사람에게 NULL을 제공하므로 누락된 보너스를 0으로 처리하는 것이 귀하의 비즈니스에 적합한 경우 COALESCE(bonus, 0) + 100을 작성하십시오.

NULLIF: 값을 NULL로 변환

NULLIF(a, b)은 두 값이 같으면 NULL을 반환하고, 그렇지 않으면 a를 반환합니다. 두 가지 용도가 특히 도움이 됩니다. 자리 표시자 값을 정리할 수 있습니다.

SELECT name, NULLIF(phone, '') FROM employees WHERE id = 3;
-- Sara | NULL

SELECT name FROM employees
WHERE COALESCE(NULLIF(phone, ''), '') = '';
-- Arjun
-- Sara
-- Ravi

두 번째 쿼리는 “누락되었거나 비어 있는” 값을 찾는 편리한 방법입니다. 또한 많은 데이터베이스에서 오류가 발생하는 0으로 나누는 것을 방지할 수도 있습니다.

SELECT total / NULLIF(quantity, 0) FROM sales;

quantity이 0이면 실패하지 않고 NULL이 됩니다. 테스트에서 SELECT 100 / NULLIF(0, 0)는 NULL을 반환했습니다.

집계 건너뛰기 NULL

NULL이 보고서에 가장 큰 피해를 주는 부분은 다음과 같습니다.

SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus),
       AVG(COALESCE(bonus, 0))
FROM employees;

-- 4 | 2 | 800 | 400.0 | 200.0

그렇다면 평균 보너스는 400인가요, 아니면 200인가요? 데이터에서 NULL이 무엇을 의미하는지에 따라 다릅니다. NULL이 “보너스가 지급되지 않음”을 의미하는 경우 200이 맞습니다. “아직 결정되지 않음”을 의미한다면 400이 맞을 수도 있습니다. 데이터베이스는 귀하를 대신하여 이러한 결정을 내릴 수 없으므로 해당 비즈니스를 아는 사람에게 질문해야 합니다.

관련 트랩: 값이 없는 SUM은 0이 아니라 NULL입니다. 보너스 없이 행만 합산하면 NULL이 되고 COUNT는 0을 반환합니다. 숫자를 기대하는 애플리케이션 코드는 이에 충돌할 수 있으므로 래핑합니다: COALESCE(SUM(bonus), 0).

GROUP BY, 정렬 및 DISTINCT

비교에서 NULL은 “같지 않음”이지만 이러한 작업은 이를 하나의 그룹으로 처리합니다.

SELECT bonus, COUNT(*) FROM employees GROUP BY bonus ORDER BY bonus;
-- NULL | 2
--  300 | 1
--  500 | 1

SELECT DISTINCT bonus FROM employees;
-- 500, 300, NULL

그룹화에 대한 자세한 내용은 SQL GROUP BY 및 HAVING을 참조하세요. 정렬 순서는 데이터베이스가 실제로 다른 부분입니다. SQLite와 MySQL은 NULL을 가장 작은 값으로 처리하는 반면 PostgreSQL과 Oracle은 이를 가장 큰 값으로 처리합니다. 따라서 동일한 ORDER BY bonus DESC는 NULL을 하나의 마지막에 넣고 다른 것의 첫 번째를 넣을 수 있습니다. PostgreSQL, Oracle 및 최신 SQLite를 사용하면 NULLS FIRST 또는 NULLS LAST를 사용하여 이를 직접 명시할 수 있습니다.

조인

=을 사용하는 조인 조건은 NULL과 일치하지 않으므로 NULL 키가 있는 행은 내부 조인에서 제외됩니다.

SELECT e.name FROM employees e
JOIN employees m ON m.id = e.manager_id;
-- Arjun, Sara, Ravi    (Meera is gone)

SELECT e.name, m.name FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;
-- Meera | NULL
-- Arjun | Meera
-- Sara  | Meera
-- Ravi  | Arjun

Meera에는 관리자가 없으므로 내부 조인은 해당 행을 자동으로 삭제합니다. A LEFT JOIN은 이를 유지하고 누락된 면에 대해 자체 NULL을 생성합니다. SQL 조인 설명에서 자세한 내용을 읽어보세요.

NOT IN 트랩

NOT IN 목록의 단일 NULL은 모든 결과를 지웁니다.

SELECT name FROM employees WHERE manager_id NOT IN (1);
-- Ravi

SELECT name FROM employees WHERE manager_id NOT IN (1, NULL);
-- (no rows)

두 번째 형식은 manager_id <> 1 AND manager_id <> NULL을 의미하며 마지막 부분은 결코 참이 될 수 없습니다. 이는 NULL이 눈에 띄지 않게 도착할 수 있는 하위 쿼리의 경우 특히 위험합니다. 대신 SQL 하위 쿼리 튜토리얼에 설명된 대로 NOT EXISTS를 사용하세요. 해당 행의 manager_id가 NULL이므로 첫 번째 결과에서도 Meera가 누락되었습니다.

NULL을 사용한 디자인

• 기본적으로 NOT NULL를 사용합니다. 값이 실제로 누락될 수 있는 경우에만 NULL을 허용합니다.
• 합리적인 기본값을 추가합니다. 예를 들면 quantity INT NOT NULL DEFAULT 0입니다.
• 마법 값을 사용하지 마십시오. 1900-01-01의 날짜 또는 “알 수 없음”을 나타내는 -1의 날짜는 계산을 손상시킵니다.
• UNIQUE 동작을 알아보세요. 대부분의 데이터베이스는 UNIQUE 열에 여러 개의 NULL을 허용하며 테스트에서는 오류 없이 두 개를 삽입했습니다. SQL Server는 일반적으로 허용하므로 예외입니다.
• 기본 키는 NULL일 수 없다는 점을 기억하세요. 기본 키와 외래 키 비교를 참조하세요.
• NULL로 테스트합니다. NULL이 없는 테스트 데이터는 이러한 버그를 숨깁니다. 다른 특정 사례와 마찬가지로 의도적으로 포함합니다.

마무리

NULL을 “알 수 없음”으로 처리하면 대부분의 동작이 이해되기 시작합니다. 알 수 없음에 1을 더한 것은 알 수 없는 것이며 알 수 없음은 알 수 없음과 같다는 사실이 알려지지 않았습니다. 이번 주에 귀하의 보고서 중 하나를 감사하십시오. COUNT(column)에 대해 COUNT(*)을 사용하여 Null 허용 열을 확인하고 NOT IN을 찾은 다음 각 평균에서 NULL이 무엇을 의미해야 하는지 결정합니다.

자주 묻는 질문

계속 학습하세요