OUTER Clause for Joins - Informix to MySQL 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 MySQL.

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:

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):

Result (2nd query):

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

MySQL:

  -- 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:

Nested OUTER in ANSI SQL syntax:

MySQL:

  -- 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):

Result (2nd query):

For more information, see Informix to MySQL Migration.