# How Exists In Checks Work

Source: https://userguide.qualytics.io/data-quality-checks/exists-in/deep-dive/how-it-works/

## Definition

*Asserts that every value in the selected field exists in a field on another container.*

## Overview

The Exists In rule is a referential-integrity check. It reads a **reference field** on another container and requires that each value in the target field exists there. The reference can live in the same datastore or in a different one, and it can be narrowed with its own filter clause, so the lookup covers exactly the set you consider valid.

Typical use cases:

- Enforce a foreign key between a fact table and its dimension.
- Restrict a code column to the values held in a reference table.
- Confirm that a migrated dataset only references entities that made it across.

## Field Scope

**Single:** The rule evaluates exactly one field per check, looked up against exactly one reference field. Every field type is accepted.

## Anomaly Types

| Type | Supported |
| --- | --- |
| Record Flag inconsistencies at the row level | Supported |
| Shape Flag inconsistencies in the overall patterns and distributions of a field | Supported |

## Evaluation Flow

Every Exists In check follows the same four-step evaluation flow:

1. **Apply both filter clauses.** The target filter scopes the rows that are evaluated; the reference filter scopes the rows that form the lookup set. Each side is narrowed before anything is compared.
2. **Build the lookup set.** The platform reads the reference field on the filtered reference container.
3. **Test each target value.** A row passes when its value exists the lookup set, and fails when it does not exist.
4. **Apply coverage.** At 100% coverage, any failing row causes the check to fail. Below 100% coverage, the check fails only when the passing fraction drops below the threshold (see [Coverage and Tolerance](https://userguide.qualytics.io/data-quality-checks/exists-in/deep-dive/how-it-works/#coverage-and-tolerance)).

## Two Filters, Two Purposes

The rule carries two independent filter clauses and mixing them up is the most common configuration mistake:

- The **Filter Clause** on the main form scopes the **target** rows: which rows get evaluated at all.
- The **Filter Clause** on the **Right Reference** panel scopes the **reference** rows: which values count as valid.

Narrowing the reference filter makes the check stricter, because fewer values are acceptable. Narrowing the target filter makes it quieter, because fewer rows are tested.

## NULL Handling

The check **passes NULL values** in the target field: a row with `NULL` has no value to look up, so it is not counted as a violation. Exists In only asserts something about *present* values.

If the field must also be populated, pair it with a [Not Null](https://userguide.qualytics.io/data-quality-checks/not-null/introduction/) check on the same field.

## Both Sides Must Be Comparable

The target value and the reference value are matched as values of the same kind. A numeric column looked up against a text column will not match, even when the printed values look identical. Keep the two columns on the same type, or normalize one of them upstream with a Computed Field.

## The Reference Is Read at Scan Time

The lookup set reflects the reference container as the scan reads it, not as it was when the check was created. A reference table that loses rows makes the check stricter without anyone editing it, which is usually what you want from referential integrity, and is worth remembering when a check suddenly starts firing.

## The Filter Clause

The filter clause is a SQL `WHERE` expression applied **before** the evaluation. Filtered-out rows are ignored entirely (they cannot trigger a violation and are not counted in the totals).

When a filter is set, the message names it at the end of its first sentence, as `(applied to records where <expression>)`. Record and Shape Anomaly messages both carry it.

## Coverage and Tolerance

Coverage is a fractional value between `0` and `1` that defines the minimum fraction of evaluated rows that must pass:

- **`1.0` (100%, default):** every row in the filtered set must pass. Any failing row causes the check to fail. This is the strictest setting.
- **`< 1.0`:** the check tolerates a fraction of rows failing. The check fails only when the fraction of passing rows drops below the threshold.

Lower coverage values are useful when a small, known fraction of failures is expected. Use coverage carefully: a 0.5% tolerance can mask a real regression that happens to fall just under the threshold.

## See Also

- **Anomaly Reporting**

  The anomaly messages the check produces, what the numbers mean, Source Records highlighting, and Custom Anomaly Description.

  [Anomaly Reporting](https://userguide.qualytics.io/data-quality-checks/exists-in/deep-dive/anomaly-reporting/)
- **Examples**

  Three production scenarios with sample data, anomaly messages, and the SQL equivalent of what the check evaluates.

  [Examples](https://userguide.qualytics.io/data-quality-checks/exists-in/deep-dive/examples/)
- **Best Practices**

  Guidelines for choosing the reference, scoping both sides, and keeping the signal clean.

  [Best Practices](https://userguide.qualytics.io/data-quality-checks/exists-in/deep-dive/best-practices/)
- **Permissions**

  The team permission each action needs: view, create, edit, archive, restore, and delete.

  [Permissions](https://userguide.qualytics.io/data-quality-checks/exists-in/deep-dive/permissions/)

*Last updated: September 22, 2026.*
