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

# Compare lists

> Combine, deduplicate, and compare two small lists.

TutorialApplies to: Unified engine deployments

Combine, deduplicate, and compare two small lists.

## Before you start

You need Workbench and a running SQL engine. Every example includes its own input rows. No catalog, files, credentials, or external data is needed. These queries create no stored tables.

Copy the SQL into Workbench and run the statements in order. Compare the result with the table below the query. `WITH` names input data for one statement only. Each example can run on its own.

## 1. Keep duplicates

What happens when we append both lists?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH first_list(name) AS (VALUES ('Ada'), ('Ben')),
second_list(name) AS (VALUES ('Ben'), ('Cy'))
SELECT name FROM first_list UNION ALL SELECT name FROM second_list ORDER BY name;
```

**Expected result**

| name |
| - |
| Ada |
| Ben |
| Ben |
| Cy |

UNION ALL keeps every input row.

**Change one thing:** Add Ada to the second list. Expect five rows, including two Adas.

## 2. Remove duplicates

Who appears in either list?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH first_list(name) AS (VALUES ('Ada'), ('Ben')),
second_list(name) AS (VALUES ('Ben'), ('Cy'))
SELECT name FROM first_list UNION SELECT name FROM second_list ORDER BY name;
```

**Expected result**

| name |
| - |
| Ada |
| Ben |
| Cy |

UNION removes repeated result rows.

**Change one thing:** Replace UNION with UNION ALL. Ben appears twice.

## 3. Find common values

Who appears in both lists?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH first_list(name) AS (VALUES ('Ada'), ('Ben')),
second_list(name) AS (VALUES ('Ben'), ('Cy'))
SELECT name FROM first_list INTERSECT SELECT name FROM second_list ORDER BY name;
```

**Expected result**

| name |
| - |
| Ben |

INTERSECT keeps shared rows.

**Change one thing:** Add Ada to the second list. Expect Ada and Ben.

## 4. Find removed values

Who appears only in the first list?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH first_list(name) AS (VALUES ('Ada'), ('Ben')),
second_list(name) AS (VALUES ('Ben'), ('Cy'))
SELECT name FROM first_list EXCEPT SELECT name FROM second_list ORDER BY name;
```

**Expected result**

| name |
| - |
| Ada |

EXCEPT subtracts the right result from the left result.

**Change one thing:** Reverse the inputs. Only Cy remains.

## 5. Find new values

Who was added in the second list?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH first_list(name) AS (VALUES ('Ada'), ('Ben')),
second_list(name) AS (VALUES ('Ben'), ('Cy'))
SELECT name FROM second_list EXCEPT SELECT name FROM first_list ORDER BY name;
```

**Expected result**

| name |
| - |
| Cy |

Compare snapshots in the direction of the question.

**Change one thing:** Add Dia to the second list. Expect Cy and Dia.

## 6. Compare totals

How many distinct people appear overall?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
WITH first_list(name) AS (VALUES ('Ada'), ('Ben')),
second_list(name) AS (VALUES ('Ben'), ('Cy'))
SELECT COUNT(*) AS people FROM (SELECT name FROM first_list UNION SELECT name FROM second_list) combined;
```

**Expected result**

| people |
| - |
| 3 |

A set result can be the input to another query.

**Change one thing:** Use UNION ALL. The count becomes 4.

## Continue

Continue to [Clean text and convert types](https://docs.e6data.com/transformation/learn/clean-text.md), or return to [Learn Laminar](https://docs.e6data.com/transformation/learn.md).


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.