Search code examples
sqljoinleft-join

SQL: JOIN vs LEFT OUTER JOIN?

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'
Solution

  • 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/

    enter image description here

    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/