Scenario

You are a materials engineer with two tables: one tracking lab experiments and another cataloging available materials.

Task

Write a Snowflake SQL query that:

1. Performs a full outer join between {{ ref("experiments") }} and {{ ref("materials") }} on material_id
2. Uses COALESCE to produce a single non-null material_id column from both sides
3. Returns experiment_id, experiment_date, experiment_results from the experiments side and material_name, material_type from the materials side

Schema

experiments

materials

Example

experiments:

materials:

Expected Output:

Note: Experiment 3 used material 203 which has no entry in the materials table (NULLs for material columns). Material 204 has no experiments (NULLs for experiment columns). The output always shows a non-null material_id by taking the available value from whichever side has it.

SELECT
    e.experiment_date,
    e.experiment_id,
    e.experiment_results,
    COALESCE(e.material_id, m.material_id) AS material_id,
    m.material_name,
    m.material_type
FROM {{ ref("experiments") }} AS e
FULL OUTER JOIN {{ ref("materials") }} AS m
    ON e.material_id = m.material_id

Explanation

Step 1: FULL OUTER JOIN preserves all rows

FROM {{ ref("experiments") }} AS e
FULL OUTER JOIN {{ ref("materials") }} AS m
    ON e.material_id = m.material_id

A FULL OUTER JOIN keeps every row from both tables. When a row in experiments has no matching material_id in materials, the materials columns are NULL. When a row in materials has no matching experiment, the experiment columns are NULL. This is the key difference from LEFT JOIN (keeps only the left side) or INNER JOIN (keeps only matches).

Step 2: COALESCE resolves the duplicate join key

COALESCE(e.material_id, m.material_id) AS material_id

After a full outer join, unmatched rows have NULL on one side of the join key. COALESCE returns the first non-null argument, so this produces a single material_id column that is never NULL. Without COALESCE, you would need to reference e.material_id or m.material_id separately, and one would be NULL for unmatched rows.

Step 3: Selecting columns from each side

SELECT
    e.experiment_date,
    e.experiment_id,
    e.experiment_results,
    ...
    m.material_name,
    m.material_type

Columns that are not the join key can be selected directly from their respective table alias. They will naturally be NULL for unmatched rows on that side, which is the expected behavior of a full outer join.