CODEWITHSUNDEEP
mysqlsqlsql-serverjoin
codingbiz
codingbiz

Reputation: 26386

Why do we have OUTER clause in LEFT JOIN?

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

Upvotes: 2

Views: 1358

Answers (5)

HeatfanJohn
HeatfanJohn

Reputation: 7333

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.

Upvotes: 2

Barton Chittenden
Barton Chittenden

Reputation: 4416

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.

Upvotes: 0

James-Jesse Drinkard
James-Jesse Drinkard

Reputation: 15703

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.

Upvotes: 1

Bojangles
Bojangles

Reputation: 101483

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

Upvotes: 1

Smi
Smi

Reputation: 14326

There is no difference. The OUTER keyword is optional.

Upvotes: 8

Related Questions