Outer Joins *= and =* - Sybase ASE to PostgreSQL Migration

Sybase Adaptive Server Enterprise (ASE) supports legacy Transact-SQL syntax *= and =* for outer joins. In PostgreSQL you have to use ANSI SQL standard LEFT OUTER and RIGHT OUTER join syntax.

Consider the following sample table:

  CREATE TABLE colors (name VARCHAR(30), category CHAR(1));
 
  INSERT INTO colors VALUES ('Green', 'G');
  INSERT INTO colors VALUES ('Red', 'R');
  INSERT INTO colors VALUES ('Blue', 'B');
 
  CREATE TABLE categories (name CHAR(1), note VARCHAR(30));
 
  INSERT INTO categories VALUES ('G', 'G category');
  INSERT INTO categories VALUES ('R', 'R category');

Let's use a left outer join between these two tables:

Sybase ASE:

  -- Left outer join
  SELECT colors.name, categories.note 
  FROM colors, categories
  WHERE colors.category *= categories.name;

PostgreSQL:

  -- Left outer join
  SELECT colors.name, categories.note 
  FROM colors LEFT OUTER JOIN categories
   ON colors.category = categories.name;

Both queries return the following result:

name note
Green G category
Red R category
Blue NULL

Now let's use a right outer join between these two tables:

Sybase ASE:

  -- Right outer join
  SELECT categories.note, colors.name 
  FROM categories, colors
  WHERE categories.name =* colors.category;

PostgreSQL:

  -- Right outer join
  SELECT categories.note, colors.name 
  FROM categories RIGHT OUTER JOIN colors
   ON categories.name = colors.category;

Both queries return the following result:

note name
G category Green
R category Red
NULL Blue

For more information, see Sybase ASE to PostgreSQL Migration.