Do you use the OUTER keyword when writing left/right JOINs in SQL?

Question

I often see people who write SQL like this:

SELECT * from TableA LEFT OUTER JOIN TableB ON (ID1=I2)

I myself write simply:

SELECT * from TableA LEFT JOIN TableB ON (ID1=I2)

To me the "OUTER" keyword is like line noise - it adds no additional information, just clutters the SQL. It's even optional in most RDBMS that I know. So... why do people still write it? Is it a habit? Portability? (Are your SQL's really portable anyway?) Something else that I'm not aware of?

Answer 1

OUTER really is superfluous, as you write, since all OUTER joins are either LEFT or RIGHT, and reciprocally all LEFT or RIGHT joins are OUTER. So syntactically it's mostly noise, as you put it. It is optional even in ISO SQL. As for why people use it, I suppose some feel the need the insist on the join being OUTER, even if the left-or-right keyword already says so. For that matter, INNER also is superfluous!

Answer 2

YES

It just make things clearer in my opinion - the clearer and more obvious you state your intent, the better (especially for someone else trying to read and understand your code later on).

But that's just my opinion - it's not technically needed, so you can use it - or leave it.

Answer 3

No. I use

• JOIN
• LEFT JOIN
• RIGHT JOIN
• FULL OUTER JOIN
• CROSS JOIN

There is no ambiguity for me.

Answer 4

One thing that several months on Stackoverflow has shown me is how much SQL is written and / or maintained by people with no previous exposure to SQL or relational databases at all.

For that reason, I think that the more explicit you can be the better off the next programmer is going to be when looking at your code.