---
title: "Order and key"
description: "Why both sets must be sorted, how to sort them, and the key that tells a different row from a missing one"
url: "https://docs.justcat.it/reference/tests/order-and-key/"
---
# Order and key


[Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") and [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") compare two sets the same way: CAT reads both from the top, row by row, and steps forward on the side that is behind. That only works when both sets arrive **in the same order**. This page covers the two ways of getting them there and the optional `Key`.

## Both sets must be sorted

The comparison never loads the sets whole — it walks them. A row that is out of order is a row CAT cannot find on the other side, and the test fails for the wrong reason. Two paths, pick the one that fits the test:

* **`ORDER BY` in the queries — the performance path.** The database sorts far better than CAT ever will, and nothing is read to RAM. Sort by every compared column, or by the key when you use one; ascending; both sides with the same collation.
* **`Sort data: true` on the test — the convenience path.** CAT reads both sets into memory, sorts them itself and only then compares. No `ORDER BY` needed, and both sides are ordered by one and the same rules, so a different collation on the two systems cannot bite you. Either set above a million rows gets a warning in the log recommending you sort in the source instead — CAT still sorts.

```yaml
Tests:
- Name: All customers are loaded
  #
  # abbreviated
  #
  Expectation: sets match
  Sort Data: true
```

`Sort data` sorts by the key when one is set, by all columns otherwise. It changes who sorts, not what is accepted: the rules below apply either way.

## Why a key

Without a key, when a row is not found on the other side CAT can only say so. It cannot tell whether the row is *missing* or whether a *similar* row exists with different values — there is nothing that says which rows belong together. So you only get:

* rows missing in the first set,
* rows missing in the second set.

With a key, CAT pairs the rows by it and you get:

* rows missing in the first set,
* rows missing in the second set,
* **different rows** — same key, at least one different value — with the differing values marked.

> **🐱:** **The key is optional.** It only gives you better failure messages (and, for [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains"), it is what makes `Maximum errors logged` work). If you are happy with the messages you get, you can skip this page.



## Where to set it

Put `Key` on the test definition — in the YAML file, in the database table, in the worksheet, wherever your tests live:


**Properties**



Name
: Compare staging table with fact table - Passengers

Suite
: Consistency checks

First Data Source
: DWH

First Query
: SELECT  * FROM STAGE.PASSENGERS ORDER BY ID

Second Data Source
: DWH

Second Query
: SELECT  * FROM FACT.PASSENGERS ORDER BY ID

Expectation
: sets match

Key
: ID

Maximum Errors Logged
: 20





**YAML**


```yaml
Tests:
- Name: Compare staging table with fact table - Passengers
  Suite: Consistency checks
  First Data Source: DWH
  First Query: |
     SELECT  * FROM STAGE.PASSENGERS ORDER BY ID
  Second Data Source: DWH
  Second Query: |
     SELECT  * FROM FACT.PASSENGERS ORDER BY ID
  Expectation:  sets match
  Key: ID
  Maximum Errors Logged: 20
```




What the value can be:

| `Key` | Meaning |
|---|---|
| `ID` | A column name, from either set; the match ignores case |
| `1` | An ordinal position — `1` is the first column |
| `First is key` (or `The first column is key`) | The first column |
| `TerminalID, GateID` · `1, 2` · `1, GateID` | A **composite key**: names and positions, separated by commas or semicolons, in any combination |
| `All except last` (or `All columns except the last one`) | Every column but the last is part of the key |

A key that resolves to no column is an error; the message lists the columns of both sets to choose from.

## Rules for key columns

* **Sorted by the key, ascending**, on both sides — with `ORDER BY`, or with `Sort data`. Same collation on both sides; for non-ASCII characters that means a binary collation, so we do not recommend such columns as keys. A key that goes down somewhere is reported as a warning inside the failure message (`Found decreasing key in the first set`), and the comparison is not reliable from that point on.
* **Unique.** A repeated key is reported as a warning inside the failure message (`Found duplicity in the key`).
* **No `NULL`.** A `NULL` in a key column ends the test with `Error`.
* **Compatible types on the two sides.** The values need not be the same type, but they must fall into one of these groups together: the same type; any integer or decimal number; any date or time; a unique identifier (a GUID, or 16 bytes); text (`char`, `varchar`, character arrays). A `bigint` against an `int` is fine; a date against a boolean is not — the test ends with `Error`.
* Names of the key columns can differ between the sets; use the name from either side.

## Related

* [Failure message](https://docs.justcat.it/reference/tests/failure-message/ "Failure message") — what the two kinds of report look like, with and without a key.
* [Sets match](https://docs.justcat.it/reference/tests/expectations/sets-match/ "Sets match") · [Contains](https://docs.justcat.it/reference/tests/expectations/contains/ "Contains") — the expectations that read `Key` and `Sort data`.