Conditional

NULLIF

PostgreSQL

Mengembalikan NULL jika value1 sama dengan value2, otherwise mengembalikan value1. Berguna untuk menghindari division by zero atau mengkonversi sentinel values ke NULL.

Tipe hasil: same as value1

Syntax

SQL

NULLIF(value1, value2)

Parameter

value1anywajib

Nilai yang akan dikembalikan jika tidak sama dengan value2

value2anywajib

Nilai pembanding

Contoh Penggunaan

Avoid Division by Zero

SQL

1 SELECT 
2   total_revenue,
3   total_orders,
4   total_revenue / NULLIF(total_orders, 0) AS avg_order_value
5 FROM sales_summary;

Menghindari error division by zero dengan mengkonversi 0 ke NULL.

Hasil

total_revenue	total_orders	avg_order_value
100000	10	10000
0	0	(null)

Konversi Sentinel ke NULL

SQL

1 SELECT 
2   name,
3   NULLIF(phone, N/A) AS phone,
4   NULLIF(email, ) AS email
5 FROM contacts;

Mengkonversi placeholder values menjadi NULL.

Hasil

name	phone	email
John	08123456789	john@...
Jane	(null)	(null)

Percentage Calculation Safe

SQL

1 SELECT 
2   category,
3   100.0 * current_sales / NULLIF(previous_sales, 0) AS growth_pct
4 FROM category_sales;

Menghitung persentase dengan aman dari division by zero.

Fungsi Equivalen di Database Lain

NULLIF

MySQL

NULLIF

Snowflake

NULLIF

BigQuery

NULLIF

DuckDB

Fungsi Terkait

COALESCE

Kembali ke PostgreSQL Functions

1	SELECT
2	total_revenue,
3	total_orders,
4	total_revenue / NULLIF(total_orders, 0) AS avg_order_value
5	FROM sales_summary;

1	SELECT
2	name,
3	NULLIF(phone, N/A) AS phone,
4	NULLIF(email, ) AS email
5	FROM contacts;

1	SELECT
2	category,
3	100.0 * current_sales / NULLIF(previous_sales, 0) AS growth_pct
4	FROM category_sales;