OUTER Clause for Joins - Informix to Oracle Migration

Informix supports the specific OUTER clause in the FROM clause of the SELECT statement to define the outer joins. It has to be converted to ANSI SQL outer join syntax in Oracle.

Consider the following tables with sample data:

  CREATE TABLE cities (name VARCHAR(30), country VARCHAR(30));
  CREATE TABLE countries (name VARCHAR(30), capital VARCHAR(30), region VARCHAR(30));
  CREATE TABLE regions (name VARCHAR(30), continent VARCHAR(30));
 
  -- Cities
  INSERT INTO cities VALUES ('Malaga', 'Spain');
  INSERT INTO cities VALUES ('Warsaw', 'Poland');
  INSERT INTO cities VALUES ('Kansas City', 'United States');
 
  -- Countries
  INSERT INTO countries VALUES ('Spain', 'Madrid', 'Western Europe');
  INSERT INTO countries VALUES ('United States', 'Washington', 'North America');
 
  -- Region
  INSERT INTO regions VALUES ('North America', 'America');

Informix allows you to use the following OUTER join syntax:

Informix:

  -- OUTER with the join condition in WHERE
  SELECT c.name, ct.capital
  FROM cities c, OUTER countries ct
  WHERE c.country = ct.name;
 
  -- Another form of OUTER (the same result as the previous query)
  SELECT c.name, ct.capital
  FROM cities c, OUTER (countries ct)
  WHERE c.country = ct.name;

Result:

name capital
Malaga Madrid
Warsaw NULL
Kansas City Washington

Nested OUTER:

Informix:

  -- Nested join of countries and regions tables is simple INNER join
  SELECT c.name, ct.capital, r.continent
  FROM cities c, OUTER (countries ct, regions r)
  WHERE c.country = ct.name and ct.region = r.name;
 
  -- Nested join of countries and regions tables is also OUTER join
  SELECT c.name, ct.capital, r.continent
  FROM cities c, OUTER (countries ct, OUTER (regions r))
  WHERE c.country = ct.name and ct.region = r.name;

Result (1st query):

name capital continent
Malaga NULL NULL
Warsaw NULL NULL
Kansas City Washington America

Result (2nd query):

name capital continent
Malaga Madrid NULL
Warsaw NULL NULL
Kansas City Washington America

When migrating to Oracle you have to convert the join clause to ANSI SQL join as follows:

Oracle:

  -- OUTER join condition is defined in ON clause, not WHERE
  SELECT c.name, ct.capital
  FROM cities c LEFT OUTER JOIN countries ct
  ON c.country = ct.name;

Result:

name capital
Malaga Madrid
Warsaw NULL
Kansas City Washington

Nested OUTER in ANSI SQL syntax:

Oracle:

  -- Nested join of countries and regions tables is simple INNER join
  SELECT c.name, ct.capital, r.continent
  FROM cities c LEFT OUTER JOIN (countries ct INNER JOIN regions r ON ct.region = r.name)
  ON c.country = ct.name;
 
  -- Nested join of countries and regions tables is also OUTER join
  SELECT c.name, ct.capital, r.continent
  FROM cities c LEFT OUTER JOIN (countries ct LEFT OUTER JOIN regions r  ON ct.region = r.name)
  ON c.country = ct.name;

Result (1st query):

name capital continent
Malaga NULL NULL
Warsaw NULL NULL
Kansas City Washington America

Result (2nd query):

name capital continent
Malaga Madrid NULL
Warsaw NULL NULL
Kansas City Washington America

For more information, see Informix to Oracle Migration.