SQL: JOIN vs LEFT OUTER JOIN?

Question

I have multiple SQL queries that look similar where one uses JOIN and another LEFT OUTER JOIN. I played around with SQL and found that it the same results are returned. The codebase uses JOIN and LEFT OUTER JOIN interchangeably. While LEFT JOIN seems to be interchangeable with LEFT OUTER JOIN, I cannot I cannot seem to find any information about only JOIN. Is this good practice?

Ex Query1 using JOIN

SQL
SELECT 
   id,
   name 
FROM 
   u_users customers
JOIN 
   t_orders orders
ON orders.status=='PAYMENT PENDING'

Ex. Query2 using LEFT OUTER JOIN

SQL
SELECT 
   id,
   name 
FROM 
   u_users customers
LEFT OUTER JOIN 
   t_orders orders
ON orders.status=='PAYMENT PENDING'

Answer 1

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/

Answer 2

Both are the same, there is no difference here.

You need to use the ON clause when using Join. It can match any data between two tables when you don't use the ON clause. This can cause performance issue as well as map unwanted data.

If you want to see the differences you can use "execution plans".

for example, I used the Microsoft AdventureWorks database for the example.

LEFT OUTER JOIN :

LEFT JOIN :

If you use the ON clause as you wrote, there is a possibility of looping. Example "execution plans" is below.

You can access the correct mapping and data using the ON clause complement.

select 
   id,
   name 
from 
   u_users customers
left outer join 
   t_orders orders on customers.id = orders.userid
where orders.status=='payment pending'