> ## Documentation Index
> Fetch the complete documentation index at: https://docs.prophecy.ai/llms.txt
> Use this file to discover all available pages before exploring further.

# UnionByName

> Combine datasets by aligning columns with the same name

export const gemName_0 = "UnionByName"

export const execution_engine_0 = "the SQL warehouse"

<Info>This gem runs in {execution_engine_0}.</Info>

## Overview

Use the UnionByName gem to combine rows from multiple datasets by matching column names. This can help when working with data from different sources where schemas might vary slightly in order or structure, but the column names are consistent.

<Tip>
  The {gemName_0} gem has a corresponding interactive gem example. See [Interactive gem
  examples](/data-analysis/gems/gems#interactive-gem-examples) to learn how to run sample pipelines
  for this and other gems.
</Tip>

## Prerequisites

* Add `prophecy_basics` package version 1.0.0 or higher to your project.

## Input and Output

| Port    | Description                                                                |
| ------- | -------------------------------------------------------------------------- |
| **in0** | The first input table.                                                     |
| **in1** | The second input table.                                                    |
| **inN** | Optional: Additional input tables to include in the union.                 |
| **out** | A single table containing the combined rows, with columns matched by name. |

To add additional input ports, click `+` next to **Ports**.

## Parameters

| Parameter      | Description                                                                                                                                                                                                                           |
| -------------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| Operation Type | Choose between two different operations.<ul><li>Union By Name (No Missing Columns): All columns must be present in both tables.</li><li>Union By Name (Allow Missing Columns): Columns do not have to match between tables.</li></ul> |

## Example

Let's say you're working with two tables: **Table A** and **Table B**.

* Both tables contain order-related data.
* Table B is missing the `amount` column that exists in Table A.
* The columns in **Table B** that match **Table A** are ordered differently.

### Table A

<div class="table-example">
  | `order_id` | `customer_id` | `order_date` | `amount` |
  | ---------- | ------------- | ------------ | -------- |
  | 101        | 1             | 2024-12-01   | 250.00   |
  | 102        | 2             | 2024-12-03   | 150.00   |
  | 103        | 1             | 2025-01-15   | 300.00   |
  | 104        | 3             | 2025-02-10   | 200.00   |
</div>

### Table B

<div class="table-example">
  | `order_id` | `order_date` | `customer_id` |
  | ---------- | ------------ | ------------- |
  | 103        | 2025-01-15   | 1             |
  | 104        | 2025-02-10   | 3             |
  | 105        | 2025-03-05   | 4             |
  | 106        | 2025-03-07   | 2             |
</div>

### Result

The table below shows the output after running a UnionByName gem with **Allow Missing Columns** enabled.

Since **Table B** doesn't have the `amount` column, Prophecy fills in null values for rows coming from Table B.

<div class="table-example">
  | `order_id` | `customer_id` | `order_date` | `amount` |
  | ---------- | ------------- | ------------ | -------- |
  | 101        | 1             | 2024-12-01   | 250.00   |
  | 102        | 2             | 2024-12-03   | 150.00   |
  | 103        | 1             | 2025-01-15   | 300.00   |
  | 104        | 3             | 2025-02-10   | 200.00   |
  | 103        | 1             | 2025-01-15   | NULL     |
  | 104        | 3             | 2025-02-10   | NULL     |
  | 105        | 4             | 2025-03-05   | NULL     |
  | 106        | 2             | 2025-03-07   | NULL     |
</div>

<Warning>
  This example will fail if you select **No Missing Columns** in the gem settings, since **Table B**
  is missing a column in **Table A**.
</Warning>