2

Possible Duplicate:
Left join and Left outer join in SQL Server

Based on my studies, the following statements would be correct

A) Join is the same as inner join.
B) Left outer join is the same as left join
C) Right outer join is the same as right join
D) There is no such thing as left inner join and right inner join
Community
  • 1
  • 1
dido
  • 3,347
  • 11
  • 34
  • 42

1 Answers1

2

The keyword OUTER is optional, but in my opinion it should be mandatory. Until such time as it is (and of course thereafter, too), my advice is always to write it, to remind yourself that it's an outer join.

The keyword LEFT, RIGHT, or FULL is mandatory. Left, right, and full outer joins are the only types of outer join. And of course the keyword JOIN is mandatory.

You mentioned INNER JOIN. This is not the same as FULL [OUTER] JOIN. INNER means all result rows are rows that were produced by match some condition between the two tables. An outer join has result rows where sometimes there isn't a match, yet rows from one table, or the other, or both, are returned without a match.

There is one other type of join besides inner and outer, and that's the CROSS JOIN, but that's a different question for another day.

SmartestVEGA
  • 8,415
  • 26
  • 86
  • 139
  • 5
    Why do you think `OUTER` should be mandatory? There's no way to confuse a `LEFT JOIN` with an `INNER JOIN`, as you yourself state in the answer. – JNK Mar 27 '12 at 13:20
  • "it should be mandatory... to write it, to remind yourself that it's an outer join" -- doesn't strike me as being a strong argument. How about, forcing users to writing things out longhand as a punishment for using a non-relational feature that is expressly designed to generate nulls? I'd vote for that :) But in the knowledge that once something gets into the SQL standard it never gets removed. For example, the Committee may consider `SELECT *` in a cursor to be 'deprecated' but they will never be able to expunge it from the standard. – onedaywhen Mar 27 '12 at 13:49
  • 1
    "There is one other type of join besides inner and outer" -- don't forget the union join. – onedaywhen Mar 27 '12 at 13:51