OUTER JOIN

Question

Hi all,
if the keyword OUTER can be obmitted, why does it exist?
What is it the difference between using it and not using it?

Thanks in advance
Mario

Answer 1

It looks like a safety blanket (i.e., makes you feel better until you know the world is safe). Here's my reasoning:

Going back far, IBM has this to say:

http://www.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids_sqs_1026.htm

"In an ANSI-compliant join that specifies the LEFT, RIGHT, or FULL keywords in the FROM clause, the OUTER keyword is optional."

That admits an alternative; so what's a non-ANSI join? These old, deprecated, horrifying-to-admins transact-SQL objects are SUPPOSED TO do an outer join:

*= and =*

but can unexpectedly do CROSS joins instead, which appears to be an interaction between the entire query and at what keyword conditions were specified (ON or HAVING). Inconsistent selection is obviously a terrible thing for a query to do so T-SQL was replaced by the 92 standard.

I've seen that syntax myself...and I remember some weird things about when query language was in transition...and lots of experience with people who dig into safe positions (even after the war's over)...I suspect admins burned by it would want the extra assurance that they are doing an OUTER join, period.

...but ANSI's mature. We don't need the blanket anymore.

Links for reference:
http://stackoverflow.com/a/983955
http://stackoverflow.com/a/940650
http://stackoverflow.com/a/19478161

There's some Oracle crossover too (they had OUTER joins before ANSI)...so Smoking gun (cartesian join, trillions of rows if you omitted join predicate...better go ANSI):
http://www.orafaq.com/comment/6687#comment-6687

Answer 2

It's a good question, personally I think it's merely legacy but also easy to read/understand what's being asked upon when reading the code.