SQL의 COALESCE() 함수 이해
기술 발전에 발맞추어 개발자에게 최신 트렌드를 따라가는 것은 필수적입니다. 문자열 조작에 대한 깊이 있는 이해는 초보자든 숙련된 개발자든 데이터 준비(예: 비즈니스 요구에 맞춰 기존 형식과 다른 형식을 생성), SQL 서버 내장 기능을 활용한 데이터 관리에 큰 도움이 됩니다.
데이터 조작 외에도 데이터 세트 검사, 데이터 값 평가, 인코딩 및 디코딩을 통해 보다 유의미한 데이터를 만들 수 있습니다. 이러한 과정을 통해 데이터 세트에서 누락된 값을 식별하고, 계산에 미치는 영향을 파악하며, 작업 결과를 왜곡할 수 있는 Null 값을 방지하여 데이터 처리 작업을 효율적으로 관리할 수 있습니다.
이 가이드는 복잡한 프로그램을 구축하는 데 유용한 SQL 통합 기능을 안내합니다. 이 글에서는 SQL 사용 경험이 있고, 특정 기능에 대한 이해를 넓히고자 하는 독자를 대상으로 합니다. SQL 학습을 시작하는 데 도움이 필요하다면, SQL 가이드 시리즈를 참고해 보세요.
SQL에서 COALESCE() 함수의 역할과 활용법
SQL의 COALESCE() 함수는 주어진 순서대로 매개변수(인수)를 평가하여 Null이 아닌 첫 번째 값을 반환합니다. 간단히 말해, 이 함수는 목록을 순차적으로 확인하며, 처음으로 Null이 아닌 값이 나오면 그 값을 반환하고 종료합니다. 만약 목록의 모든 인수가 Null이라면 함수는 NULL을 반환합니다.
이 함수는 MYSQL, Azure SQL Database, Oracle, PostgreSQL 등 다양한 데이터베이스 시스템에서 광범위하게 지원됩니다.
COALESCE() 함수는 다음과 같은 상황에서 유용하게 사용됩니다:
- Null 값 처리
- 여러 쿼리를 단일 쿼리로 통합 실행
- 복잡하고 시간이 많이 걸리는 CASE 문 대체
CASE 문(또는 ISNULL 함수) 대신 COALESCE()를 사용하면, CASE 문이 두 개의 매개변수만 처리하는 것과 달리 여러 개의 매개변수를 처리할 수 있습니다. 이는 코드를 간결하게 만들고 작성 과정을 용이하게 합니다.
기본 구문은 다음과 같습니다:
COALESCE(valueOne, valueTwo, valueThree, …, valueX);
SQL 서버에서 COALESCE() 함수는 여러 매개변수를 허용하며, 동일한 데이터 유형의 인수들을 사용합니다. 정수형 인수를 yield 함수를 통해 연결하여 최종적으로 정수형 값을 출력할 수 있습니다.
참고: SQL 치트 시트 모음 (나중에 참조)
COALESCE() 함수 사용법을 자세히 살펴보기 전에, 먼저 SQL에서 NULL 값이 무엇인지 이해하는 것이 중요합니다.
SQL에서 NULL 값의 의미
SQL에서 NULL은 데이터베이스에 값이 존재하지 않음을 나타내는 고유한 마커입니다. 이는 정의되지 않았거나 알 수 없는 값으로 이해할 수 있습니다. 빈 문자열이나 0과는 엄연히 다르며, 값이 '없다'는 것을 의미합니다. 테이블 열에서 NULL 값은 정보가 누락되었음을 나타냅니다.
실제 예시로, 전자상거래 웹사이트 데이터베이스의 고객 ID 열은 고객이 ID를 제공하지 않으면 NULL 값으로 채워질 수 있습니다. SQL에서의 NULL은 다른 프로그래밍 언어에서 특정 객체를 가리키지 않는 것과는 달리 '상태'를 의미합니다.
SQL의 NULL 값은 관계형 데이터베이스에 큰 영향을 미칩니다. 첫째, 다른 내부 함수와 함께 사용될 때 특정 값을 제외할 수 있습니다. 예를 들어, 생산 환경에서 총 주문 목록을 생성하는 동안 일부 주문은 아직 완료되지 않았을 수 있습니다. 이 경우 NULL 값을 자리 표시자로 사용하여 내부 SUM 함수가 총계를 정확하게 계산할 수 있도록 합니다.
또한, AVG 함수를 사용하여 평균을 계산해야 하는 경우를 생각해 봅시다. 만약 0 값을 포함하여 계산하면 결과가 왜곡될 수 있습니다. 대신, 데이터베이스는 이러한 필드를 무시하고 NULL 값을 활용하여 보다 정확한 평균값을 얻을 수 있습니다.
NULL 값에는 단점도 있습니다. 가변 길이 값으로 취급되어, 바이트 단위로 저장됩니다. 따라서, 일반 값보다 더 많은 저장 공간을 차지할 수 있으며, 데이터베이스는 이러한 바이트를 저장하기 위해 여분의 공간을 확보해야 합니다. 또한, 일부 함수에서 NULL 값을 제거하기 위해 사용자 정의를 해야 하는 경우가 있어 SQL 프로시저가 길어질 수 있습니다.
COALESCE() 함수를 이용한 NULL 값 처리
NULL 값은 값의 존재는 알지만 그 값이 무엇인지는 모르는 상태를 의미합니다. 실제 값을 수집하여 필드를 채울 때까지 NULL 값은 자리 표시자 역할을 합니다.
NULL 값은 십진수, 문자열, BLOB, 정수 등 데이터베이스의 다양한 데이터 유형에 사용될 수 있지만, 숫자 데이터를 처리할 때는 주의해야 합니다. 숫자 데이터와 함께 사용할 경우 코드 개발 시 추가 설명이 필요할 수 있습니다.
COALESCE() 함수를 사용하여 NULL 값을 처리하는 다양한 방법:
COALESCE() 함수를 활용하여 NULL 값을 특정 값으로 대체
COALESCE() 함수를 사용하면 모든 NULL 값을 특정 값으로 대체할 수 있습니다. 예를 들어, '직원' 테이블에 '급여' 열이 있고, 일부 직원의 급여 정보가 없어 NULL 값이 있을 수 있습니다. 이 경우, 계산을 수행할 때 모든 NULL 항목을 특정 값(예: 0)으로 처리할 수 있습니다. 아래는 그 방법입니다:
SELECT COALESCE(salary, 0) AS adjusted_salary FROM employees;
COALESCE() 함수로 여러 옵션 중 첫 번째 NULL이 아닌 값 선택
때로는 여러 표현식 목록에서 첫 번째 NULL이 아닌 값을 선택해야 할 경우가 있습니다. 이는 관련 데이터가 여러 열에 분산되어 있고, NULL이 아닌 값에 우선순위를 부여해야 할 때 유용합니다. 구문은 다음과 같습니다:
COALESCE (expression1, expression2, …)
실제 예시로, 'preferred_name' 및 'full_name' 열을 가진 연락처 테이블이 있다고 가정해 봅시다. 이 경우, 선호하는 이름이 있으면 선호하는 이름을 표시하고, 그렇지 않으면 전체 이름을 표시하여 연락처 목록을 생성하고자 할 수 있습니다. 해결책은 다음과 같습니다:
SELECT COALESCE(preferred_name, full_name) AS display_name FROM contacts.
위의 예시에서, 'preferred_name'이 NULL이 아니면 해당 값이 반환되고, 그렇지 않으면 'full_name'이 표시 이름으로 반환됩니다.
SQL COALESCE() 함수를 이용한 문자열 연결
NULL 값이 포함된 문자열을 연결할 때 SQL에서 문제가 발생할 수 있습니다. 이러한 경우, NULL 값이 반환될 수 있는데, 이는 일반적으로 원하는 결과가 아닙니다. COALESCE() 함수를 사용하여 이 문제를 해결할 수 있습니다. 아래 예시를 확인해 보겠습니다.
간단한 문자열 연결은 다음과 같이 수행할 수 있습니다:
SELECT 'Hello, where are you, '|| 'John '||'?' AS example
위 코드는 다음을 반환합니다:
예: Hello, where are you, John?
하지만, 아래와 같이 NULL 값을 사용하는 경우에는 다음과 같습니다:
SELECT 'Hello, where are you, ' || null || '?' AS example
결과는 다음과 같습니다:
NULL 값과 관련된 모든 텍스트 문자열 연결은 NULL을 반환하므로 위 결과는 NULL입니다. 하지만 COALESCE() 함수를 사용하면 이 문제를 해결할 수 있습니다. 이 함수는 NULL 대신 빈 문자열(또는 공백)을 반환합니다. 예를 들어 자동차 이름을 제조업체와 함께 나열한다고 가정해 봅시다. 쿼리는 다음과 같습니다:
SELECT car || ', manufacturer: ' || COALESCE(manufacturer, '—') AS car_brand FROM stock
제조업체 정보가 NULL인 경우, '—'가 NULL 값 대신 표시됩니다. 예상 결과는 다음과 같습니다:
car_brand outlander, manufacturer: — flying spur, manufacturer: Bentley royal athlete, manufacturer: — royal saloon, manufacturer: Crown
보시다시피, NULL 결과를 대체 문자열 값으로 대체하여 문제를 해결할 수 있습니다.
SQL COALESCE() 함수와 피벗 연산
SQL 피벗은 행을 열로 변환하는 기술입니다. '정규화된' 형태(행이 많고 열이 적음)의 데이터를 '비정규화된' 형태(행이 적고 열이 많음)로 변환하는 데 사용할 수 있습니다. COALESCE() 함수는 SQL 피벗과 함께 사용하여 피벗된 결과에서 NULL 값을 처리하는 데 유용합니다.
SQL에서 PIVOT 연산을 수행하면 행이 열로 변환되고, 결과 열은 일부 데이터의 집계 함수로 계산됩니다. 특정 셀에 대한 집계 결과가 NULL인 경우, 'COALESCE' 함수를 사용하여 NULL 값을 기본값 또는 의미 있는 표현으로 대체할 수 있습니다. 아래 예시를 확인해 봅시다.
연도, 분기, 수익 열을 가진 '판매' 테이블이 있고, 이 데이터를 피벗하려고 한다고 가정해 봅시다. 열은 연도로, 값은 각 분기의 총 수익으로 표시됩니다. 하지만, 일부 분기에는 수익 데이터가 없을 수 있으며, 피벗된 결과에 NULL 값이 나타날 수 있습니다. 이 경우, COALESCE() 함수를 사용하여 피벗된 결과의 NULL 값을 0으로 대체할 수 있습니다.
SELECT
year,
COALESCE(SUM(CASE WHEN quarter="Q1" THEN revenue END), 0) AS Q1_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q2" THEN revenue END), 0) AS Q2_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q3" THEN revenue END), 0) AS Q3_Revenue,
COALESCE(SUM(CASE WHEN quarter="Q4" THEN revenue END), 0) AS Q4_Revenue
FROM sales
GROUP BY year;
스칼라 사용자 정의 함수와 SQL COALESCE() 함수
스칼라 사용자 정의 함수(UDF)와 COALESCE() 함수를 함께 사용하면 NULL 값을 처리하는 복잡한 논리를 구현할 수 있습니다. 이 두 기능을 결합하면 SQL 쿼리에서 더욱 정교한 데이터 변환 및 계산을 수행할 수 있습니다. 다음 구조를 가진 'Employees' 테이블을 고려해 봅시다:
CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Salary INT,
Bonus INT
);
각 직원의 총 소득(급여와 보너스 합계)을 계산해야 한다고 가정해 봅시다. 하지만 일부 값은 누락되어 있습니다. 이 경우 스칼라 UDF는 급여와 보너스 합계를 계산하고, COALESCE() 함수는 NULL 값을 처리합니다. 다음은 총 수입을 계산하는 스칼라 UDF입니다:
CREATE FUNCTION dbo.CalculateTotalEarnings (@salary INT, @bonus INT)
RETURNS INT
AS
BEGIN
DECLARE @totalEarnings INT;
SET @totalEarnings = @salary + COALESCE(@bonus, 0);
RETURN @totalEarnings;
END;
You can then use the scalar UDF with coalesce in a query:
SELECT EmployeeID, FirstName, LastName,
Salary, Bonus, dbo.CalculateTotalEarnings(Salary, Bonus) AS TotalEarnings
FROM Employees;
SQL COALESCE() 함수를 이용한 데이터 유효성 검사
데이터베이스 작업을 할 때, 숫자 값의 유효성을 검증해야 할 수 있습니다. 예를 들어, 'products'라는 테이블에 'product_name', 'price', 'discount' 열이 있다고 가정해 봅시다. 각 항목의 제품 이름, 가격 및 할인을 검색하려고 합니다. 하지만 모든 NULL 할인 값을 0으로 처리하고 싶습니다. COALESCE() 함수가 이럴 때 유용합니다. 사용 방법은 다음과 같습니다:
SELECT product_name, price, COALESCE(discount, 0) AS discount FROM products
SQL COALESCE() 함수와 계산 열
계산 열은 테이블 내의 다른 열이나 식을 기반으로 계산되는 가상 열입니다. 계산 열은 데이터베이스에 물리적으로 저장되지 않으므로 복잡한 시나리오 및 데이터 변환 시 COALESCE() 함수와 함께 활용할 수 있습니다. 실제 사용 사례 예시를 확인해 보겠습니다.
'price', 'discount', 'tax_rate' 열을 가진 'product' 테이블을 고려해 봅시다. 할인과 세금을 적용한 최종 제품 가격을 나타내는 'total_price' 계산 열을 만들고 싶습니다. 할인 또는 세금이 지정되지 않은 경우(NULL), 0을 사용하여 계산을 진행하고 싶습니다. COALESCE() 함수를 활용하는 방법은 다음과 같습니다:
CREATE TABLE products( price DECIMAL(10, 2), discount DECIMAL(10, 2), tax_rate DECIMAL(5, 2), total_price AS (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) );
위 코드에서 일어나는 일은 다음과 같습니다:
- 'total_price' 계산 열은 (COALESCE(price, 0) – COALESCE(price*discount, 0))* COALESCE(1+tax_rate, 1) 로 정의됩니다.
- 가격이 NULL인 경우, COALESCE(price, 0)은 가격이 0으로 처리되도록 합니다.
- 할인이 NULL인 경우, COALESCE(price*discount, 0)는 할인이 0으로 처리되어 곱셈 연산에 영향을 주지 않도록 합니다.
- 세율이 NULL인 경우, COALESCE(1+tax_rate, 1)는 세금이 적용되지 않음을 의미하는 1로 처리되어 곱셈 연산에 영향을 주지 않도록 합니다.
위의 설정을 통해 NULL 값이 있거나 값이 누락된 경우에도 정확한 최종 가격을 계산하는 계산 열 'total_price'를 생성할 수 있습니다.
SQL COALESCE() 함수와 CASE 표현식
COALESCE() 함수는 CASE 표현식과 함께 사용할 수 있습니다. 다음 예시를 살펴봅시다:
SELECT Productname + ' '+ deliverydate productdetails, dealer, CASE WHEN cellphone is NOT NULL Then cellphone WHEN workphone is NOT NULL Then workphone ELSE 'NA' END EmergencyContactNumber FROM dbo.tb_EmergencyContact
위의 설정에서, CASE 표현식은 COALESCE() 함수와 유사한 역할을 수행합니다.
또한, 동일한 쿼리에서 COALESCE() 함수와 CASE 표현식을 함께 사용할 수도 있습니다. 두 기술을 모두 사용하여 NULL 값을 처리하고 조건부 논리를 동시에 적용할 수 있습니다. 예를 들어 설명하겠습니다.
'product_id', 'product_name', 'price', 'discount' 열을 가진 'products' 테이블을 고려해 봅시다. 일부 제품에는 특정 할인이 적용되지만 다른 제품에는 할인이 적용되지 않습니다. 제품에 할인이 있는 경우 할인된 가격을 표시하고, 할인이 없는 경우 정상 가격을 표시해야 합니다.
SELECT
product_id,
product_name,
price,
COALESCE(
CASE
WHEN discount > 0 THEN price - (price * discount / 100)
ELSE NULL
END,
price
) AS discounted_price
FROM products;
위 코드에서, `CASE` 표현식은 `discount` 값이 0보다 큰지 확인하고, 할인된 가격을 계산하며, 그렇지 않으면 NULL 값을 반환합니다. `COALESCE` 함수는 `CASE` 표현식과 `price` 결과를 인수로 받습니다. NULL이 아닌 첫 번째 값을 반환하여 사용 가능한 경우 할인된 가격을 표시하고, 그렇지 않은 경우 정상 가격을 표시합니다.
마지막으로
이 글에서는 데이터베이스 쿼리에서 `COALESCE` 함수를 다양하게 활용하는 방법을 알아보았습니다. 지정된 순서대로 매개변수를 평가하고, NULL이 아닌 첫 번째 값을 반환하는 COALESCE() 함수는 쿼리를 간소화하고 효율성을 높여줍니다.
COALESCE() 함수는 NULL 값 처리, 문자열 연결, 데이터 피벗, 데이터 유효성 검사, 계산 열 작업 등 다양한 상황에서 활용 가능한 다재다능한 기능입니다. COALESCE() 함수를 능숙하게 활용하면 누락된 데이터를 효율적으로 처리하고 오류 없는 데이터베이스 설계를 만들 수 있습니다. 이러한 기술을 마스터하려면 꾸준한 연습이 필요하다는 것을 기억해야 합니다.
이제 SQL에서 외래 키 제약 조건을 설정하는 방법을 알아보세요.