Why do we have OUTER clause in LEFT JOIN?

Question

What the difference between the 2 queries below

UPDATE

This question is already answered in a similar question here LEFT JOIN vs. LEFT OUTER JOIN in SQL Server.

SELECT * FROM ca 
LEFT JOIN exam 
ON ca.subject = exam.subject

and

SELECT * FROM ca 
LEFT OUTER JOIN exam 
ON ca.subject = exam.subject

Tables

Table 'CA'
--------------------
subject|score
----------------
ENG    |25
MTH    |34
BIO    |18


Table 'Exam'
--------------------
subject|score
----------------
ENG    |25

Running the 2 queries produce the same result in mysql, sqlserver 2008. I didn't test in Oracle.

subject | score1 | subject | score1
--------------------------------------
ENG       14       ENG       44
MTH       28       NULL      NULL
BIO       22       NULL      NULL

What's the purpose of that OUTER clause

See this SO post: http://stackoverflow.com/questions/406294/left-join-and-left-outer-join-in-sql-server — ssis_ssiSucks, Jun 09 '12 at 18:50
I didn't see that one. This one (http://stackoverflow.com/questions/38549/sql-difference-between-inner-and-outer-join) that I saw was confusing — codingbiz, Jun 09 '12 at 19:09
Is this question on some new exam or brain bench or something? http://dba.stackexchange.com/questions/19005/difference-of-left-join-and-left-outer-join — Aaron Bertrand, Jun 09 '12 at 20:29

score 8 · Accepted Answer · answered Jun 09 '12 at 18:49

8

There is no difference. The OUTER keyword is optional.

answered Jun 09 '12 at 18:49

Smi

13,850
9
56
64

score 2 · Answer 2 · answered Jun 09 '12 at 18:54

2

They are equivalent in the sense that all LEFT JOIN queries are also LEFT OUTER JOIN.

See http://en.wikipedia.org/wiki/Join_(SQL)#Outer_joins for more information.

answered Jun 09 '12 at 18:54

HeatfanJohn

7,143
2
35
41

score 1 · Answer 3 · answered Jun 09 '12 at 18:49

1

There is no difference between LEFT and LEFT OUTER; the OUTER keyword may be omitted.

answered Jun 09 '12 at 18:49

Bojangles

99,427
50
170
208

score 1 · Answer 4 · answered Jun 09 '12 at 18:50

In some databases LEFT JOIN is called LEFT OUTER JOIN,so it's the same thing. The keyword OUTER is optional So, 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.

score 0 · Answer 5 · answered Jun 09 '12 at 18:51

0

There are four types of joins: inner, left outer, right outer and full outer. Left join, right join and full join are simply abbreviations for the types of outer joins.

answered Jun 09 '12 at 18:51

Barton Chittenden

4,238
2
27
46

And anti-joins, semi-joins, and then there is the distinction between equijoins and non-equijoins. – Gordon Linoff Jun 09 '12 at 21:17

Why do we have OUTER clause in LEFT JOIN?

5 Answers5