Handling NULLs in Joins

Context

In marketing analytics, especially when combining campaign activity from Veeva CRM and reference data from Veeva Vault, NULL values can quietly change join results and lead to missing or misleading rows.

Question

Explain how you handle NULL values when performing a join in PostgreSQL. You should describe how NULL behaves in join conditions, how INNER JOIN and LEFT JOIN differ when join keys or joined columns contain NULLs, and when you would use COALESCE, IS NULL, or filtering logic after the join.

Scope guidance

The interviewer is looking for a practical explanation, not just a definition. You should be able to explain common pitfalls, show how NULLs affect row retention, and give a few short SQL examples that demonstrate the right way to preserve unmatched rows or substitute default values in the result.

NULL does not equal NULL

In SQL, NULL represents an unknown value, so NULL = NULL does not evaluate to true. That means rows with NULL join keys usually do not match each other in a standard equality join.

SELECT *
FROM campaign_members cm
JOIN hcp_dimension h
  ON cm.hcp_id = h.hcp_id;

INNER JOIN vs LEFT JOIN with NULLs

An INNER JOIN keeps only rows where the join condition matches, so rows with NULL join keys are typically dropped. A LEFT JOIN keeps all rows from the left table and fills unmatched right-side columns with NULLs.

SELECT cm.member_id, h.hcp_name
FROM campaign_members cm
LEFT JOIN hcp_dimension h
  ON cm.hcp_id = h.hcp_id;

Using COALESCE for output values

COALESCE is useful when the join result contains NULLs in selected columns and you want a fallback display value. It does not make NULL join keys match by itself unless you explicitly use it inside the join condition.

SELECT cm.member_id,
       COALESCE(h.hcp_name, 'Unknown HCP') AS hcp_name
FROM campaign_members cm
LEFT JOIN hcp_dimension h
  ON cm.hcp_id = h.hcp_id;

Finding unmatched rows with IS NULL

After a LEFT JOIN, checking a right-side key with IS NULL is the standard way to find left-table rows that had no match. This is common for data quality checks and reconciliation.

SELECT cm.member_id
FROM campaign_members cm
LEFT JOIN hcp_dimension h
  ON cm.hcp_id = h.hcp_id
WHERE h.hcp_id IS NULL;

Be careful using COALESCE in join conditions

Using COALESCE inside the ON clause can force NULLs to compare as a default value, but that may create false matches if the fallback value is not truly equivalent. It should only be used when the business rule explicitly says missing values should be treated the same.

SELECT *
FROM campaign_targets t
LEFT JOIN territory_map m
  ON COALESCE(t.territory_code, 'UNASSIGNED') = COALESCE(m.territory_code, 'UNASSIGNED');