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 Microsoft SQL Server.
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 SQL Server you have to convert the join clause to ANSI SQL join as follows:
SQL Server:
-- 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:
SQL Server:
-- 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 SQL Server Migration.