Oninit
Talk to an expert →

Informix Error -393: A condition in the where clause results in a two-sided outer join.

Cause and resolution

A condition in the where clause results in a two-sided outer join.

This query requests an outer join, but one or more conditions in the WHERE clause interfere with the dominant-subordinate relationships between the joined tables in the FROM clause.

Review the query, and verify that every condition that relates the joined tables is actually necessary and semantically correct.

If an expression in the WHERE clause relates two subordinate tables, you must use parentheses around the joined tables in the FROM clause to enforce dominant-subordinate relationships. (Note: You cannot put a parenthesis directly after the FROM keyword.) The following example successfully returns a result:

SELECT c.company, o.order_date, i.total_price, m.manu_name FROM customer c, OUTER (orders o, OUTER (items i, OUTER manufact m)) WHERE c.customer_num = o.customer_num AND o.order_num = i.order_num AND i.manu_code = m.manu_code;

If you omit parentheses around the subordinate tables in the FROM clause, you must establish join conditions, or relationships, between the dominant table and each subordinate table in the WHERE clause. If a join condition is between two subordinate tables, the query will fail. The following example successfully returns a result:

SELECT c.company, o.order_date, c2.call_descr FROM customer c, OUTER orders o, OUTER cust_calls c2 WHERE c.customer_num = o.customer_num AND c.customer_num = c2.customer_num;

Consider using the ANSI-SQL standard syntax for outer joins. For more information, refer to the IBM Informix Guide to SQL: Syntax.

Oninit® Troubleshooting Guidance

Reasons / Common Causes

-393 is specific to Informix's older, non-ANSI outer-join syntax (OUTER in the FROM clause): that syntax depends on a clear dominant/subordinate relationship between joined tables, and a WHERE clause condition that links two subordinate tables directly to each other (instead of each subordinate linking back to the dominant table) breaks that relationship, effectively demanding an outer join on both sides at once — something the server can't resolve.

  1. A WHERE clause condition joining two subordinate (outer-joined) tables directly to each other, rather than each relating back to the dominant table.
  2. A query with more than one OUTER table where the join graph isn't a clean dominant-to-each-subordinate star shape.
  3. Old-style outer join syntax carried over from a much older codebase, where the ANSI LEFT OUTER JOIN/RIGHT OUTER JOIN syntax would have avoided this ambiguity entirely.
Solutions / Resolution
  1. Use parentheses around joined tables to enforce the correct dominant/subordinate relationships, per the official guidance, if staying with the older OUTER syntax.
  2. Ensure every join condition relates the dominant table to each subordinate table individually, rather than linking subordinate tables to each other directly.
  3. Consider migrating to ANSI-SQL standard outer join syntax (LEFT OUTER JOIN/RIGHT OUTER JOIN with explicit ON clauses), per the official guidance — this syntax doesn't have the same dominant/subordinate ambiguity and is generally clearer to maintain.
Examples
The disallowed subordinate-to-subordinate condition (old syntax)
SELECT c.name, o.order_id, s.tracking_number
FROM customers c, OUTER orders o, OUTER shipments s
WHERE c.customer_id = o.customer_id
  AND o.order_id = s.order_id;
-- -393: shipments (subordinate) is linked to orders (subordinate),
-- not directly to the dominant table

Fix — migrate to ANSI outer join syntax instead:

SELECT c.name, o.order_id, s.tracking_number
FROM customers c
    LEFT OUTER JOIN orders o ON c.customer_id = o.customer_id
    LEFT OUTER JOIN shipments s ON o.order_id = s.order_id;
Diagnostic Checks
  1. Identify every OUTER table in the FROM clause and trace its join condition back to the dominant table.
  2. Check for any join condition linking two subordinate tables directly rather than each to the dominant table.
  3. Consider whether migrating to ANSI outer join syntax resolves the ambiguity more cleanly than restructuring the old-style syntax.
Related Errors / Related Topics
  • -201 — "A syntax error has occurred." The general SQL-parsing-error family this fits into.

Migrating to ANSI LEFT OUTER JOIN/RIGHT OUTER JOIN syntax with explicit ON clauses avoids this dominant/subordinate ambiguity entirely, rather than working around it with parentheses in the older syntax.