Informix Error -395: The where clause contains an outer cartesian product.

Reasons / Common Causes

-395 is another guardrail on Informix's older OUTER-clause join syntax, closely related to -393: this one fires when a dominant/subordinate table pair in an outer join has no WHERE clause condition relating them at all, which would produce every row of the subordinate table matched against every row of the dominant table — an unintended and typically enormous cartesian product.

1. A missing join condition between the dominant table and an OUTER subordinate table — the direct cause, usually from an accidentally omitted or deleted WHERE condition.
2. A WHERE clause condition mistakenly relating the wrong pair of tables, leaving the actual dominant-subordinate pair with no connecting condition.
3. A query built up incrementally, where a table was added to the FROM clause but its corresponding join condition was never added to WHERE.

Solutions / Resolution

1. Review the query to ensure every dominant-subordinate table pair has at least one connecting condition in the WHERE clause, per the official guidance.
2. When adding a new OUTER table to a query, add its join condition to WHERE in the same change, rather than as an afterthought.
3. Consider migrating to ANSI-SQL outer join syntax (LEFT OUTER JOIN ... ON ...), which makes the connection between each pair of tables explicit and much harder to accidentally omit.

Examples

A missing join condition

SELECT c.name, o.order_id
FROM customers c, OUTER orders o
WHERE c.region = 'West';
-- -395: no condition relates customers to orders at all

Fix — add the missing join condition:

SELECT c.name, o.order_id
FROM customers c, OUTER orders o
WHERE c.region = 'West' AND c.customer_id = o.customer_id;

Using ANSI syntax to make the connection explicit

SELECT c.name, o.order_id
FROM customers c LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
WHERE c.region = 'West';

Diagnostic Checks

1. Identify every dominant-subordinate table pair in the FROM clause.
2. Confirm each pair has at least one connecting condition in the WHERE clause.
3. Consider migrating to ANSI outer join syntax to make missing connections far more visible at a glance.

Related Errors / Related Topics

• -393 — "A condition in the where clause results in a two-sided outer join." The closely related sibling restriction on the same older OUTER-clause syntax — both concern how WHERE clause conditions must correctly relate dominant and subordinate tables.

An outer join always needs an explicit connecting condition for every dominant-subordinate pair — migrating to ANSI LEFT OUTER JOIN ... ON ... syntax makes this much harder to accidentally omit.