> ## 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.

# Combine datasets

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

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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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?

```sql theme={"theme":{"light":"github-light","dark":"github-dark"}}
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](https://docs.e6data.com/transformation/learn/query-steps.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.