Are left outer joins and left joins the same?

Question

I have seen joins called LEFT OUTER JOIN or RIGHT OUTER JOIN. In some places I have seen LEFT JOIN or RIGHT JOIN. I am confused by this.

I posted a question 2 days ago, but I am unable to understand the links the solutions provide.

Are these types of joins both the same, or is there some difference between the two?

`LEFT JOIN` is the same as `LEFT OUTER JOIN`. `OUTER` keyword is *optional*. — John Woo, Mar 15 '13 at 05:57
@JW. i saw the link whatever you suggested .It is very useful.But it still did not fulfill all my doubts. http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html — PSR, Mar 15 '13 at 05:58
@jw. i want to learn writing some complex queries using joins and cases.Can u refer some links for that.I saw your answers.They attracted me very well.Can u give some reference sites for that. — PSR, Mar 15 '13 at 06:02

score 92 · Accepted Answer · answered Mar 15 '13 at 06:00

92

There are no difference between both. Refer visual represenation of joins

answered Mar 15 '13 at 06:00

divyabharathi

2,187
17
12

1

This article is just awesome! Thanks! – Frank Fang Aug 04 '17 at 18:56

score 37 · Answer 2 · edited Aug 27 '16 at 15:51

The first link they quoted gives you:

INNER JOIN: returns rows when there is a match in both tables.

LEFT JOIN / LEFT OUTER JOIN: returns all rows from the left table, even if there are no matches in the right table.

RIGHT JOIN / RIGHT OUTER JOIN: returns all rows from the right table, even if there are no matches in the left table.

FULL JOIN / FULL OUTER JOIN / OUTER JOIN: returns rows when there is a match in one of the tables.

SELF JOIN: is used to join a table to itself, as if the table were two tables, temporarily renaming at least one table in the SQL statement.

CARTESIAN JOIN: returns the cartesian product of the sets of records from the two or more joined tables.

The self join is actually not a special join. It just reflects the fact that you can join a table with itself. Doing so you must alias it in order to address the fact that it appears more than once in the same statement.

The cartesian join can be considered as an inner join without a restricting condition. Or you may view an inner join as a cartesian join with an added restriction (the join condition).

Ed Heal · Answer 3 · 2013-03-15T06:24:15.610

4

If you look at the manual page for JOIN you will see the following lines:

join_table:

   table_reference [INNER | CROSS] JOIN table_factor [join_condition]

   | table_reference STRAIGHT_JOIN table_factor

   | table_reference STRAIGHT_JOIN table_factor ON conditional_expr

| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition

   | table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

The bold line clearly shows that the keyword OUTER is optional.

edited Mar 15 '13 at 06:24

answered Mar 15 '13 at 06:09

Ed Heal

59,252
17
87
127

@Luv - Now the final line does not make sense with your formatting! – Ed Heal Mar 15 '13 at 06:23
If it's optional it does not means it doing nothing. This **optional** word in SQL descriptions confusing me a lot. For example standard `git log` CLI command syntax: `git log [] [] [[--] …]` a plenty of optional arguments. But that have significant influence on the behavior. – Dmitry Dmitriev Oct 24 '19 at 16:48

score 3 · Answer 4 · answered Mar 15 '13 at 06:00

In MySQL syntax, LEFT OUTER JOIN and LEFT JOIN are identical: (from http://dev.mysql.com/doc/refman/5.0/en/join.html)

| table_reference {LEFT|RIGHT} [OUTER] JOIN table_reference join_condition
| table_reference NATURAL [{LEFT|RIGHT} [OUTER]] JOIN table_factor

Note that the OUTER keyword is optional for LEFT/RIGHT JOIN.

LEFT and RIGHT joins are both outer joins. I believe there are some flavors of SQL that may require the OUTER keyword, but MySQL does not. That is to say that at times LEFT JOIN may not be valid.

score 2 · Answer 5 · edited Aug 18 '13 at 07:49

2

I am working in SQL Server and as per my usage and experience there is absolutely no difference between LEFT JOIN and LEFT OUTER JOIN. The same is true for RIGHT JOIN and RIGHT OUTER JOIN. When you use LEFT JOIN keyword in SQL Server, it means LEFT OUTER JOIN only. So as per my opinion its the generic rule which is same for all database engines.

see here and here

edited Aug 18 '13 at 07:49

laalto

150,114
66
286
303

answered Mar 15 '13 at 07:13

Sadia Aziz

46
3

Are left outer joins and left joins the same?

5 Answers5

Linked