any.md 5.5 KB
title: "ANY() equi-join condition"
description: "Use idiomatic Materialize SQL for equi-join whose ON expression includes the ANY() operator."
menu:
main:
parent: idiomatic-materialize-sql
identifier: idiomatic-materialize-any
weight: 5
Overview
The "field = ANY(...)" equality condition returns true if the equality
comparison is true for any of the values in the ANY() expression.
For equi-join whose ON expression includes an ANY operator
expression,
Materialize provides an idiomatic SQL as an alternative to the ANY()
expression.
{{< callout >}}
Materialize and equi-join ON fieldX = ANY(<array|list|map>)
When evaluating an equi-join whose ON expression includes the ANY operator
expression
(i.e., ON fieldX = ANY(<array|list|map>)), Materialize performs a cross join,
which can lead to a significant increase in memory usage. If possible, rewrite
the query to perform an equi-join on the unnested values.
{{</ callout >}}
Idiomatic Materialize SQL
Idiomatic Materialize SQL: For equi-join whose ON expression includes
the ANY operator expression (ON
fieldX = ANY(<array|list|map>)), use UNNEST() in a
Common Table Expression (CTE) to
unnest the values and perform the equi-join on the unnested values. If the
array/list/map contains duplicates, include DISTINCT to remove duplicates.
| Materialize SQL |
**If no duplicates exist in the unnested field:** Use a Common Table
Expression (CTE) to [`UNNEST()`](/sql/functions/#unnest) the array of values and
perform the equi-join on the unnested values.
```mzsql
-- array_field contains no duplicates.--
WITH my_expanded_values AS
(SELECT UNNEST(array_field) AS fieldZ FROM tableB)
SELECT a.fieldA, ...
FROM tableA a
JOIN my_expanded_values t ON a.fieldZ = t.fieldZ
;
```
|
| Materialize SQL |
**Duplicates may exist in the unnested field:** Use a Common Table
Expression (CTE) to [`DISTINCT`](/sql/select/#select-distinct)
[`UNNEST()`](/sql/functions/#unnest) the array of values and perform the
equi-join on the unnested values.
```mzsql
-- array_field may contain duplicates.--
WITH my_expanded_values AS
(SELECT DISTINCT UNNEST(array_field) AS fieldZ FROM tableB)
SELECT a.fieldA, ...
FROM tableA a
JOIN my_expanded_values t ON a.fieldZ = t.fieldZ
;
```
|
| Anti-pattern ❌ |
Avoid the use of [`ANY(...)` function](/sql/functions/#expression-bool_op-any) for equi-join
conditions.
```nofmt
-- Anti-pattern. Avoid. --
SELECT a.fieldA, ...
FROM tableA a, tableB b
WHERE a.fieldZ = ANY(b.array_field) -- Anti-pattern. Avoid.
;
```
|
| Materialize SQL ✅ | ***If no duplicates in the unnested field*** ```mzsql -- sales_items.items contains no duplicates. -- WITH individual_sales_items AS (SELECT unnest(items) as item, week_of FROM sales_items) SELECT s.week_of, o.order_id, o.item, o.quantity FROM orders o JOIN individual_sales_items s ON o.item = s.item WHERE date_trunc('week', o.order_date) = s.week_of ORDER BY s.week_of, o.order_id, o.item, o.quantity ; ``` ***To omit duplicates that may exist in the unnested field*** ```mzsql -- sales_items.items may contains duplicates -- WITH individual_sales_items AS (SELECT DISTINCT unnest(items) as item, week_of FROM sales_items) SELECT s.week_of, o.order_id, o.item, o.quantity FROM orders o JOIN individual_sales_items s ON o.item = s.item WHERE date_trunc('week', o.order_date) = s.week_of ORDER BY s.week_of, o.order_id, o.item, o.quantity ; ``` |
| Anti-pattern ❌ |
Avoid the use of [`ANY()`](/sql/functions/#expression-bool_op-any) for the equi-join condition.
```nofmt
-- Anti-pattern. Avoid. --
SELECT s.week_of, o.order_id, o.item, o.quantity
FROM orders o
JOIN sales_items s ON o.item = ANY(s.items)
WHERE date_trunc('week', o.order_date) = s.week_of
ORDER BY s.week_of, o.order_id, o.item, o.quantity
;
```
|