Compare 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?

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?

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?

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?

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?

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?

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, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Before you start

1. Keep duplicates

2. Remove duplicates

3. Find common values

4. Find removed values

5. Find new values

6. Compare totals

Continue

​Before you start

​1. Keep duplicates

​2. Remove duplicates

​3. Find common values

​4. Find removed values

​5. Find new values

​6. Compare totals

​Continue

Before you start

1. Keep duplicates

2. Remove duplicates

3. Find common values

4. Find removed values

5. Find new values

6. Compare totals

Continue