Legacy Joins and Compatibility

The Old Codebase

I joined a team that had been running Oracle databases since 1998. Their queries looked like this:

SELECT * FROM orders, customers

WHERE orders.customer_id = customers.id (+);

That `(+)` symbol was Oracle's proprietary way of writing a LEFT JOIN.

The Quest: The Rosetta Stone

Different legacy databases had their own join syntax before ANSI SQL standardized it.

**Oracle `(+)`**: Placed on the "optional" table's column to indicate an outer join.

**SQL Server `*=` and `=*`**: `*=` was a LEFT JOIN, `=*` was a RIGHT JOIN.

The Modern Translation

| Legacy | Modern Equivalent |

|--------|-------------------|

| `WHERE a.id = b.id` | `FROM a INNER JOIN b ON a.id = b.id` |

| `WHERE a.id = b.id (+)` (Oracle) | `FROM a LEFT JOIN b ON a.id = b.id` |

| `WHERE a.id *= b.id` (SQL Server) | `FROM a LEFT JOIN b ON a.id = b.id` |

The "Oops" Moment

I tried to use `(+)` in PostgreSQL. It failed immediately—that syntax is Oracle-only.

**Pro Tip**: When migrating databases, join syntax is one of the first things that breaks. Always rewrite to ANSI-standard `JOIN ... ON`.

The Victory

I migrated a 200-query legacy codebase to modern syntax. The new code is portable across PostgreSQL, MySQL, and SQL Server. Investing in standard SQL pays dividends.

Your Task for Today

If you have access to a legacy system, find an old query with proprietary join syntax and translate it to modern ANSI SQL.

*Day 44: Combining Results—UNION and UNION ALL.*