Are left outer joins and left joins the same?
MysqlSqlMysql Problem Overview
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?
Mysql Solutions
Solution 1 - Mysql
There are no difference between both. Refer visual represenation of joins
Solution 2 - Mysql
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).
Solution 3 - Mysql
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.
Solution 4 - Mysql
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.
Solution 5 - Mysql
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.