Except gem
Use the Except gem to extract rows that are present in the first table but absent from all subsequent tables. This is useful for identifying gaps, such as missing orders, unprocessed records, or customers who haven’t returned.
The Except gem has a corresponding interactive gem example. See Interactive gem examples to learn how to run sample pipelines for this and other gems.
Input and Output
| Port | Description |
|---|---|
| in0 | The primary input table. |
| in1 | The second input table. Any matching rows from in0 will be excluded from the output. |
| inN | Optional: Additional input tables. Matching rows from in0 will be excluded from the output. |
| out | A table containing rows from in0 that do not appear in any other input. |
To add additional input ports, click + next to Ports.
All input tables must have identical schemas (matching column names and data types).
Parameters
| Parameter | Description |
|---|---|
| Operation Type | Shows that the set operation type is Except |
| Preserve duplicate rows | Checkbox to keep duplicates in the output table |
Example
Let’s say you’re working with two tables: Table A and Table B.
- Both tables contain order-related data.
- Table A contains order information from customer
1,2, and3. - Table B contains order information from customer
1,2,3, and4. - These tables contain some identical records (duplicates).
Table A
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-12-01 | 250.00 |
| 102 | 2 | 2024-12-03 | 150.00 |
| 101 | 1 | 2024-12-01 | 250.00 |
| 104 | 3 | 2025-02-10 | 200.00 |
Table B
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 103 | 1 | 2025-01-15 | 300.00 |
| 104 | 3 | 2025-02-10 | 200.00 |
| 105 | 4 | 2025-03-05 | 400.00 |
| 106 | 2 | 2025-03-07 | 180.00 |
Result
Default
The table that results from the Except gem only includes records in Table A that are not in Table B.
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 101 | 1 | 2024-12-01 | 250.00 |
| 102 | 2 | 2024-12-03 | 150.00 |
The output indicates that order 101 and 102 appear in Table A, but not in Table B.
Preserve duplicates
If you select the preserve duplicates option, the gem preserves the duplicates from Table A.
| order_id | customer_id | order_date | amount |
|---|---|---|---|
| 102 | 2 | 2024-12-03 | 150.00 |
| 101 | 1 | 2024-12-01 | 250.00 |
| 101 | 1 | 2024-12-01 | 250.00 |
The output is the same as above, except the gem also retains the duplicate order 101.