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.