Do you use the OUTER keyword when writing left/rig

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

It is simply a matter of taste, I guess that people use it because they find that it leads to more readable code. For example, I prefer to use the also optional AS keyword since SELECT ... FROM table AS t looks more readable than SELECT ... FROM table t for me.

Answer 2

I'm using 'inner join', 'left join', 'right join', and 'full outer join'. 'join' without 'inner' makes it somewhat ambigious to me; 'left' and 'right' are self-descriptive and 'full' is such kind of a beast that it deserves special syntax :)

Answer 3

I think there is no such thing as portable SQL in the year 2009 anyway... At some point, you need to write DBMS-specific statements (like retrieving top N rows).

I personally find the JOIN syntax redundant and instead I comma-separate table names.

Answer 4

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 5

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.

Answer 6

I use the OUTER keyword myself. I agree it is merely a matter of taste but omitting it strikes me as being a little sloppy but not as bad a omitting the INNER keyword (sloppy) or writing SQL keywords in lower case (very sloppy).