E0030 foreign-key-join-mismatch

A JOIN condition that doesn't follow the foreign key between the two tables.

What it reports

A JOIN ... ON between two tables linked by a single foreign key, whose condition uses one column of the foreign key but pairs it with another key column of the other table: orders.id instead of orders.user_id, or the referenced users.id swapped for another unique column. Both columns exist and have compatible types, so the database runs the query, and it returns plausible-looking wrong rows.

Example

SELECT u.name, o.total FROM orders o JOIN users u ON u.id = o.id;

warning[E0030]: Join condition compares o.id with u.id, but orders references users through orders.user_id = users.id
  --> q.sql:1:54
    |
  1 | SELECT u.name, o.total FROM orders o JOIN users u ON u.id = o.id;
    |                                                      ^^^^^^^^^^^
    = help: Did you mean `u.id = o.user_id`?

Fixed:

SELECT u.name, o.total FROM orders o JOIN users u ON u.id = o.user_id;

Notes

To stay quiet on joins the schema doesn't decide, nothing is reported when:

• the condition is anything but a single equality of two qualified columns (a.x = b.y), or the join is a self-join,
• there is no foreign key between the tables, or more than one (created_by and updated_by both referencing users), or it has several columns,
• the column paired with the foreign key's other side isn't a primary or unique key of its table (u.id = o.created_by may follow a relation the schema doesn't declare),
• the tables share their primary key (profiles.id REFERENCES users(id) joined on p.id = u.id is the foreign key itself),
• the columns' types are incompatible (that is E0007),
• the schema has an ALTER TABLE that could not be parsed and may add a foreign key.

Configuring

# sqlsift.toml
[rules]
foreign-key-join-mismatch = "error"   # or "off"

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