---
title: "Contains"
description: "One set contains every row of the other set"
url: "https://docs.justcat.it/reference/tests/expectations/contains/"
---
# Contains


## Example

In this example we check that all data from a MySQL source system are present in a staging table in SQL Server. The staging table is persistent and is expected to contain more rows than the source (those that were deleted from the source already, but are kept in staging). The goal is to check that every single row from the source is present in the staging and we are not missing any rows.


**Properties**



Suite
: Incremental load tests

Name
: We have all customers from CRM

First Data Source
: DWH

First Query
: ```sql
  SELECT  id, name
  FROM    StagingCrm.Customer
  WHERE   created_on < CAST(GETDATE() AS DATE)
  ORDER BY id
  ```

Second Data Source
: CRM

Second Query
: ```sql
  SELECT  id, name
  FROM    contacts.customer
  WHERE   created_on < curdate()
  ORDER BY id
  ```

Expectation
: contains

Key
: id





**YAML**


```yaml
Tests:
- Suite: Incremental load tests
  Name: We have all customers from CRM
  First Data Source: DWH # this data source is defined with SqlServer@2 provider
  First Query: |
    SELECT  id, name
    FROM    StagingCrm.Customer
    WHERE   created_on < CAST(GETDATE() AS DATE)
    ORDER BY id
  Second Data Source: CRM # this data source is defined with MySql@1 provider
  Second Query: |
    SELECT  id, name
    FROM    contacts.customer
    WHERE   created_on < curdate()
    ORDER BY id
  Expectation: contains
  Key: id
```




> **🐱:** **Both sets need to be ordered** — by `ORDER BY` in the queries, or by `Sort data: true` on the test, which works for both directions of the expectation. See [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key").



## Queries

Two, exactly as for [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match"): the same number of columns, compared by position. By default the **first** set is the superset and the **second** the subset: every row of the second must be found in the first.

### The opposite direction

Expecting the superset in the first query may sometimes be awkward and unintuitive. Change the expectation name instead of swapping the queries:

```yaml
Expectation: second contains first
```

You can also be explicit about the default direction. These two are technically the same:

```yaml
Expectation: first contains second
Expectation: contains
```

## Result

The test result is `Passed` when:

* the superset contains **all** rows of the subset (the superset may contain additional rows, missing in the subset);
* the subset is empty (and there are no errors in the queries).

The test result is `Failed` when the subset contains at least one row missing in the superset — or, with a key, a row whose key exists on both sides but whose values differ.

If the underlying provider returns an exception for any of the sets, the result is `Error`.

## Properties it reads

The same as [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match"): `Key`, `Sort data`, `Tolerance` and `Tolerance mode`, `Ignore case`, `Culture`, `Maximum errors logged`, `Maximum sample column length`. The rules for the key — sorted by it, unique, compatible types, composite keys, ordinal positions — are on [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key").

## Remarks

**Set the `Key`.** Tests with the `contains` expectation perform better and give much better failure messages when a key is set. Without a key CAT still works, but it can only report the *first* row of the subset that is missing, and `Maximum errors logged` has no effect — the scan stops at that row. With a key, CAT tells a missing row from a different one and collects up to `Maximum errors logged` of them:

```yaml
Tests:
- Suite: Incremental load tests
  Name: We have all customers from CRM
  #
  # abbreviated
  #
  Expectation: contains
  Key: id # optional, but highly recommended — unique and sorted
  Maximum Errors Logged: 20 # has an effect only when a key is set
```

When the subset is exhausted and every row was found, CAT stops — it does not read the rest of the superset. The failure message then says the scan was complete for the subset side.

## Related

* [Order and key](https://docs.justcat.it/reference/tests/order-and-key/ "Order and key") — ordering, `Sort data`, and the key rules.
* [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message") — reading the table of differences.
* [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") — when both sets must be the same.