E0020 outer-column-in-subquery

IN subquery selects a column of the outer query.

What it reports

The select list of an IN / NOT IN subquery is an unqualified column that the subquery's own tables don't have, so it silently refers to the outer query's column. The subquery then returns the outer row's own value: IN is true for every row (when the subquery has rows) and NOT IN is never true. A typo or a wrong column name, and SQL accepts it.

Example

SELECT id, total FROM orders WHERE id IN (SELECT id FROM coupons);

warning[E0020]: Column 'id' in the subquery is the outer query's 'orders.id': coupons has no column 'id'
  --> q.sql:1:50
    |
  1 | SELECT id, total FROM orders WHERE id IN (SELECT id FROM coupons);
    |                                                  ^^
    = help: The subquery then returns the outer row's own value. Select a column of coupons, or qualify it (orders.id) if this is intended

Fixed:

SELECT id, total FROM orders WHERE id IN (SELECT order_id FROM coupons);

Notes

Only the select list of the subquery is checked: outer references in its WHERE (correlated subqueries) are normal. A qualified column (orders.id) is taken as intended. Nothing is reported when the columns of a relation in the subquery are unknown.

Configuring

# sqlsift.toml
[rules]
outer-column-in-subquery = "error"   # or "off"

Or for a single line: -- sqlsift:disable E0020. The examples use the schema on the Rules page.