Difference Between Right & Left Join VS Right & Left Outer Join in SQL

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

As per the documentation: FROM (Transact-SQL):

<join_type> ::= 
    [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } } [ <join_hint> ] ]
    JOIN

The keyword OUTER is marked as optional (enclosed in square brackets). In this specific case, whether you specify OUTER or not makes no difference. Note that while the other elements of the join clause is also marked as optional, leaving them out will make a difference.

For instance, the entire type-part of the JOIN clause is optional, in which case the default is INNER if you just specify JOIN. In other words, this is legal:

SELECT *
FROM A JOIN B ON A.X = B.Y

Here's a list of equivalent syntaxes:

A LEFT JOIN B            A LEFT OUTER JOIN B
A RIGHT JOIN B           A RIGHT OUTER JOIN B
A FULL JOIN B            A FULL OUTER JOIN B
A INNER JOIN B           A JOIN B

Also take a look at the answer I left on this other SO question: SQL left join vs multiple tables on FROM line?.

Are left outer joins and left joins the same?

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

Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL

There is no difference between RIGHT JOIN and RIGHT OUTER JOIN. Both are the same. That means that LEFT JOIN and LEFT OUTER JOIN are the same.

Visual Representation of SQL Joins

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Reading this original article on The Code Project will help you a lot: Visual Representation of SQL Joins.

Also check this post: SQL SERVER – Better Performance – LEFT JOIN or NOT IN?.

Find original one at: Difference between JOIN and OUTER JOIN in MySQL.

SQL: JOIN vs LEFT OUTER JOIN?

As previously noted above:

JOIN is synonym of INNER JOIN. It's definitively different from all
types of OUTER JOIN

So the question is "When should I use an outer join?"

Here's a good article, with several great diagrams:

https://www.sqlshack.com/sql-outer-join-overview-and-examples/

The short answer your your question is:

• Prefer JOIN (aka "INNER JOIN") to link two related tables. In practice, you'll use INNER JOIN most of the time.
• INNER JOIN is the intersection of the two tables. It's represented by the "green" section in the middle of the Venn diagram above.
• Use an "Outer Join" when you want the left, right or both outer regions.
• In your example, the result set happens to be the same: the two expressions happen to be equivalent.
• ALSO: be sure to familiarize yourself with "Show Plan" (or equivalent) for your RDBMS: https://www.sqlshack.com/execution-plans-in-sql-server/

'Hope that helps...

Are from Table1 left join Table2 and from Table2 right join Table1 interchangeable?

Select * from Table1 left join Table2 ...

and

Select * from Table2 right join Table1 ...

are indeed completely interchangeable. Try however Table2 left join Table1 (or its identical pair, Table1 right join Table2) to see a difference. This query should give you more rows, since Table2 contains a row with an id which is not present in Table1.

what is the difference between left join and left outer join?

The OUTER keyword is optional across most popular SQL distributions, which means there is absolutely no difference between a LEFT JOIN and a LEFT OUTER JOIN

---

Related Topics