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?

Quadrat

I couldn't agree more. The OUTER keyword is just noise. Less is more. – Pyretotherapy

Can we use it when we are not sure which of the table actually has the data..and we need data from any one ? – Onto

Sounds like you need FULL [OUTER] JOIN, although it's not quite clear from your description. Maybe you need a UNION instead. Write a new question and describe your data and structures in detail. You'll get an answer quickly then. – Quadrat

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!

Dragnet

I asked some colleagues who use the INNER/OUTER selectors, and for them it's more an image reference; when they put OUTER, they imagine the outer part of the venn diagram. for me it was always a noise. – Cortisone

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.

Hausner

+1 for "especially for someone else trying to read and understand your code later on" – Diann

Answer 3

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.

Ptyalism

Answer 4

No. I use

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

There is no ambiguity for me.

Blaine

why not use full join instead of full outer join then? – Paley

For some reason, I ended up using the word OUTER for FULL OUTER joins only. I can't explain, it sounds better. My personal preference, I guess. – Bristling

Answer 5

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.

Manganese

Answer 6

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 :)

Mathildemathis

Answer 7

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).

Pray

Answer 8

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.

Lotti