Informix Error -396: Illegal join between a nested outer table and a preserved table.

Reasons / Common Causes

-396 is a third member of the same family as -393 and -395: with nested outer joins (a subordinate table that is itself the dominant table for a further-nested subordinate), a condition relating a nested subordinate table to a preserved (dominant) table that isn't its immediate parent in the join hierarchy is unsupported.

1. A multi-level outer join where a deeply-nested subordinate table's WHERE condition skips past its immediate parent and relates directly to a higher-level preserved table instead.
2. A query with three or more OUTER tables chained together, where the join hierarchy isn't a clean parent-to-immediate-child chain throughout.
3. Old-style outer join syntax used for a genuinely complex multi-table outer join, where the dominant/subordinate hierarchy becomes hard to reason about correctly.

Solutions / Resolution

1. Review the query structure and ensure every condition linking two tables connects a preserved table only to its immediately subordinate table, per the official guidance — never skipping a level in the hierarchy.
2. Restructure a deeply nested outer join so each level's condition is scoped correctly to its immediate parent.
3. Migrate to ANSI-SQL outer join syntax, per the general guidance for this whole error family (-393/-395) — nested LEFT OUTER JOIN ... ON ... clauses make the hierarchy explicit and much less error-prone for multi-level outer joins.

Examples

A condition skipping a level in the hierarchy (old syntax)

SELECT c.name, o.order_id, i.item_id
FROM customers c, OUTER orders o, OUTER order_items i
WHERE c.customer_id = o.customer_id
  AND c.customer_id = i.customer_id;
-- -396: order_items (nested subordinate) is related to customers
-- (not its immediate parent, orders) instead of to orders

Fix — relate each nested table only to its immediate parent, or migrate to ANSI syntax:

SELECT c.name, o.order_id, i.item_id
FROM customers c
    LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
    LEFT OUTER JOIN order_items i ON o.order_id = i.order_id;

Diagnostic Checks

1. Map out the dominant/subordinate hierarchy across every OUTER table in the query.
2. Check each WHERE condition against that hierarchy for one that skips past an immediate parent.
3. Consider migrating to ANSI outer join syntax to make the hierarchy explicit.

Related Errors / Related Topics

• -393 — "A condition in the where clause results in a two-sided outer join." A related restriction in the same older OUTER-syntax family.
• -395 — "The where clause contains an outer cartesian product." Another related restriction in the same family, about a missing rather than misplaced connecting condition.

For multi-level outer joins, migrate to ANSI LEFT OUTER JOIN ... ON ... syntax — it makes the parent-to-immediate-child hierarchy explicit and avoids this class of error entirely.