Combine datasets

TutorialApplies to: Unified engine deployments

Match customers and orders, and find records with no match.

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. Match rows

Who placed each known order?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT s.order_id, c.name FROM sales s INNER JOIN customers c ON s.customer_id = c.id ORDER BY s.order_id;

Expected result

order_id	name
10	Ada
11	Ada
12	Ben

INNER JOIN keeps matching pairs. Change one thing: Add WHERE c.name = ‘Ada’. Expect orders 10 and 11.

2. Keep every customer

Which customers have orders?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT c.name, s.order_id FROM customers c LEFT JOIN sales s ON c.id = s.customer_id ORDER BY c.id, s.order_id;

Expected result

name	order_id
Ada	10
Ada	11
Ben	12
Cy	NULL

LEFT JOIN keeps a left row even when no right row matches. Change one thing: Filter WHERE s.order_id IS NULL. Only Cy remains.

3. Find unknown customers

Which order has no known customer?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT s.order_id FROM sales s LEFT JOIN customers c ON s.customer_id = c.id WHERE c.id IS NULL ORDER BY s.order_id;

Expected result

order_id
13

A null in the matched key marks an unmatched row. Change one thing: Use WHERE c.id IS NOT NULL. Expect orders 10, 11, and 12.

4. Keep customers with orders

Which customers have at least one order?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT c.name FROM customers c LEFT SEMI JOIN sales s ON c.id = s.customer_id ORDER BY c.name;

Expected result

name
Ada
Ben

A semi join returns matching left rows once. Change one thing: Add WHERE c.id = 1. Only Ada remains.

5. Keep customers without orders

Which customers have no orders?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT c.name FROM customers c LEFT ANTI JOIN sales s ON c.id = s.customer_id ORDER BY c.name;

Expected result

name
Cy

An anti join keeps left rows with no match. Change one thing: Use LEFT SEMI JOIN instead. Expect Ada and Ben.

6. Build all combinations

How many customer and channel pairs exist?

WITH customers(id, name) AS (VALUES (1, 'Ada'), (2, 'Ben'), (3, 'Cy')),
sales(order_id, customer_id, amount) AS (VALUES (10, 1, 20), (11, 1, 15), (12, 2, 30), (13, 9, 40))
SELECT c.name, ch.channel FROM customers c CROSS JOIN (VALUES ('Email'), ('SMS')) AS ch(channel) ORDER BY c.name, ch.channel;

Expected result

name	channel
Ada	Email
Ada	SMS
Ben	Email
Ben	SMS
Cy	Email
Cy	SMS

A cross join produces every pair. Repeated join keys also produce multiple pairs. Change one thing: Keep only Email in the channel input. Expect three rows.

Continue

Continue to Build queries in steps, or return to Learn Laminar.

Transformation Engine

Learn Laminar

Workloads

SQL reference

HTTP API

Recipes

Operations

Before you start

1. Match rows

2. Keep every customer

3. Find unknown customers

4. Keep customers with orders

5. Keep customers without orders

6. Build all combinations

Continue

​Before you start

​1. Match rows

​2. Keep every customer

​3. Find unknown customers

​4. Keep customers with orders

​5. Keep customers without orders

​6. Build all combinations

​Continue

Before you start

1. Match rows

2. Keep every customer

3. Find unknown customers

4. Keep customers with orders

5. Keep customers without orders

6. Build all combinations

Continue