14

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?

Vilx-
  • 104,512
  • 87
  • 279
  • 422
  • 1
    I couldn't agree more. The OUTER keyword is just noise. Less is more. – gerryster Sep 28 '16 at 14:31
  • Can we use it when we are not sure which of the table actually has the data..and we need data from any one ? – T.Adak Jul 19 '17 at 14:42
  • 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. – Vilx- Jul 19 '17 at 17:37

8 Answers8

15

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!

Arthur Reutenauer
  • 2,622
  • 1
  • 17
  • 15
  • 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. – Hadi Jul 13 '19 at 22:30
7

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 1
    +1 for "especially for someone else trying to read and understand your code later on" – Seb Nov 16 '09 at 12:45
3

No. I use

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

There is no ambiguity for me.

gbn
  • 422,506
  • 82
  • 585
  • 676
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.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
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.

Konamiman
  • 49,681
  • 17
  • 108
  • 138
1

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

Arvo
  • 10,349
  • 1
  • 31
  • 34
0

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

onedaywhen
  • 55,269
  • 12
  • 100
  • 138
0

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.

ercan
  • 1,639
  • 1
  • 20
  • 34