You may be familiar with SQL and how to write a query using the LEFT, RIGHT, and FULL OUTER keywords. Did you know that there’s an alternative way to write these queries without the use of these ANSI keywords? Oracle has a unique method also used in some other databases that enable usage of the (+) operand instead. Let’s look at an example of how we can convert a normal ANSI standard LEFT JOIN to this other method.
Let’s start with a standard query that uses a left join.
Here’s how we can convert the above SQL to the Oracle outer join:
- Move all JOIN tables into the FROM clause separated by commas
- Put all matching conditions into a WHERE clause
- Add (+) to all tables being joined to
Note: Don’t confuse the aliases as we could easily switch I.CustomerId and C.CustomerId. Without the JOIN condition, it’s easier to mistype queries like this.
And the resulting output:
No more LEFT JOIN condition! These queries are great for short-hand and easier to generally write, but they don’t replace ANSI standard. Make sure not to transfer this skill to an unsupported database otherwise you’ll see errors like this:
The WHERE condition doesn’t care which sides we have the comparison fields on or even what order we put the tables in the FROM clause. What’s important is that the plus operand is located on the appropriate table. In our example, Customer is our driving table and it’s being joined to invoice, but we could’ve just as easily flipped the script by writing the query with the plus operand on the Customer table which means the Invoice is the driving table.
We can see how easy it can be to make a small mistake that can dramatically effect our data results. I’ve seen this happen far too many times to not at least give a fair warning on writing an Oracle outer join. Yes it’s shorter, easier, and faster, but it can come at a cost later on when you have large queries producing strange results so be careful!