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

First the theory:

A join is a subset of the left join (all other things equal). Under some circumstances they are identical

The difference is that the left join will include all the tuples in the left hand side relation (even if they don't match the join predicate), while the join will only include the tuples of the left hand side that match the predicate.

For instance assume we have to relations R and S.

Say we have to do R JOIN S (and R LEFT JOIN S) on some predicate p

J = R JOIN S on (p)

Now, identify the tuples of R that are not in J.

Finally, add those tuples to J (padding any attribute in J not in R with null)

This result is the left join:

R LEFT JOIN S (p)

So when all the tuples of the left hand side of the relation are in the JOIN, this result will be identical to the Left Join.

back to you problem:

Your JOIN is very likely to include all the tuples from Users. So the query is the same if you use JOIN or LEFT JOIN.

Answer 3

The two are exactly equivalent, because the WHERE clause turns the LEFT JOIN into an INNER JOIN.

When filtering on all but the first table in a LEFT JOIN, the condition should usually be in the ON clause. Presumably, you also have a valid join condition, connecting the two tables:

SELEC id, name 
FROM u_users u LEFT JOIN 
     t_orders o
     ON o.user_id = u.user_id AND o.status = 'PAYMENT PENDING';

This version differs from the INNER JOIN version, because this version returns all users even those with no pending payments.

Answer 4

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'