All rules

Performance

postgresql/consistent-exists-over-in-subquery

Enforce a consistent stance on `EXISTS (subquery)` vs `column IN (subquery)`.

  • Type suggestion
  • Recommended warn
  • Fixable yes

Why this matters

`column IN (subquery)` returns NULL whenever the subquery yields a NULL row, which silently filters every row from the outer query. `EXISTS (subquery)` returns a clean boolean and is typically planned more cheaply too. Literal `IN` lists (`x IN (1, 2, 3)`) are out of scope. Flip with the `style` option for projects that prefer `IN`; the rewrite is limited to subqueries where the two forms keep the same rows.

Examples

Incorrect

Incorrect
SELECT * FROM t WHERE x IN (SELECT id FROM other);
Incorrect
SELECT * FROM t WHERE x = ANY (SELECT id FROM other);

Correct

Correct
SELECT * FROM t WHERE EXISTS (SELECT 1 FROM other WHERE other.id = t.x);
Correct
SELECT * FROM t WHERE x IN (1, 2, 3); -- literal list is out of scope

Configure it

// eslint.config.js
import postgresql from "eslint-plugin-postgresql";

export default [
  {
    files: ["**/*.sql"],
    languageOptions: {
      parser: postgresql.configs.recommended.languageOptions.parser,
    },
    plugins: { postgresql },
    rules: {
      "postgresql/consistent-exists-over-in-subquery": [
        "warn",
        {
          style: "always",
        },
      ],
    },
  },
];

Options

style "always" | "never" default: "always"
`always` (default) reports `IN (subquery)` and `= ANY (subquery)`. `never` rewrites a positive `EXISTS` in a `WHERE`, `ON` or `HAVING` condition whose sub-`SELECT` is tied to the outer query by one `inner.col = outer.col` comparison into `outer.col IN (SELECT inner.col ...)`. It leaves `NOT EXISTS` alone, and sub-`SELECT`s with `DISTINCT`, `GROUP BY`, `HAVING`, `LIMIT` / `OFFSET`, a locking clause, or anything but constants and columns in the select list.
Try this rule

Edit the SQL — only consistent-exists-over-in-subquery is enabled.

Pre-filled with the first incorrect example. Toggle off in the rule shelf to see how the diagnostic disappears.

0 errors 0 warnings parse 0ms · rules 0ms
Diagnostics

No issues found.

2 rules enabled.

Rule under test consistent-exists-over-in-subquery — plus no-syntax-error as a safety net.
eslint-plugin-postgresql

An ESLint plugin that lints .sql files with real PostgreSQL grammar and a curated set of best-practice rules.

© 2026 eslint-plugin-postgresql contributors Built on libpg-query · PostgreSQL 17