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'
Advertisement
Answer
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…