cockroachdb / cockroachdb/cockroach
opt: transform JOIN with equality on VALUES column to IN
- Dominant language
- Go
- Stars
- 32.5k
- Forks
- 4.1k
- PR merge metrics
- PR metrics pending
Description
We can transform equality joins where one side of the join is a Values expression into a Select expression. For example, this query:
```sql
SELECT * FROM t
JOIN (
SELECT i FROM (
VALUES (0), (10), (20), (30), (40), (50)
) AS v(i)
) AS v(i) ON t.i = v.i
```
Can be rewritten as:
```sql
SELECT * FROM t
WHERE i IN (
SELECT i FROM (
VALUES (0), (10), (20), (30), (40), (50)
) AS v(i)
)
```
The benefits of this rewrite include:
1. Simplifies the query plan.
2. May yield better row count estimates.
3. Avoids join reordering.
However, a hash-join may be more efficient than the binary search of `IN` evaluation as the number of items in the values expression increases. Therefore this transformation may not be an improvement in all cases.
Jira issue: CRDB-52078
Contributor guide
Assessment
This issue has not been assessed yet.