← All recipes
Moderne licensed

Find NOT IN with a subquery

Recipe IDorg.openrewrite.sql.antipattern.FindNotInSubquery
Artifactorg.openrewrite.recipe:rewrite-sql

When the subquery can return a NULL, NOT IN evaluates to unknown for every row and the outer query silently returns nothing. Many planners also fail to rewrite NOT IN as an efficient anti-join. NOT EXISTS avoids both problems. NOT IN over a literal list is not flagged.

Single recipeProprietary

Usage

This recipe has no required configuration options. You’ll need the Moderne CLI configured before running the command below.

mod run . --recipe org.openrewrite.sql.antipattern.FindNotInSubquery

If the recipe isn’t available locally, install it with:

mod config recipes jar install org.openrewrite.recipe:rewrite-sql:2.14.1

Data tables

Structured output this recipe can produce.

  • SQL anti-patternsSQL statements matching performance anti-pattern rules.org.openrewrite.sql.table.SqlAntiPatterns
  • Source files that had resultsSource files that were modified by the recipe run.org.openrewrite.table.SourcesFileResults
  • Source files that had search resultsSearch results that were found during the recipe run.org.openrewrite.table.SearchResults
  • Source files that errored on a recipeThe details of all errors produced by a recipe run.org.openrewrite.table.SourcesFileErrors
  • Recipe performanceStatistics used in analyzing the performance of recipes.org.openrewrite.table.RecipeRunStats