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…