Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN

Semantically, joins are processed in the order they appear in the from clause. (They may not be actually executed in this order due to SQL optimizations, but the ordering is important for defining the result set.)

So, when you do:

from orders left outer join customers inner join companies

(I'm leaving out the on clauses which are a distraction for this purpose.)

The SQL is interpreted as:

from (orders left outer join customers) inner join companies

You are doing an inner join, so the values must appear on both sides. In your case, this undoes the effect of the left outer join.

You want:

from orders left outer join (customers inner join companies)

Here are some solutions.

My preferred solution is to use left outer join for all the joins. In fact, for readability and maintainability, almost every query I write is going to be only left outer join or [inner] join connecting the tables. Having to parse through the query to understand the semantics of the joins seems to be an unnecessary effort, if you can write the queries in a consistent form.

Another solution is to use parentheses:

from orders left outer join (customers inner join companies)

Another solution is a subquery:

from orders left outer join (select . . . from customers inner join companies) cc

1. Query 1: Because you have an INNER JOIN on Customers, the LEFT JOIN is effectively an INNER JOIN.
2. Query 2 is correct because you want to see all Orders regardless of the data quality / condition.
3. I like to avoid RIGHT JOINs in general as it is confusing to some developers and is therefore less readable. You can generally write your query in such a way to do the same thing with effective use of LEFT JOINs.
4. Query 2 is my recommendation for something simple like this.
5. One general rule... Once you introduce an OUTER JOIN into your query, the JOINs that follow should also be OUTER JOINs. Otherwise, you MAY exclude rows you did not intend.

You can write your joins nested like this so that the left join is performed on the combined result of customers and companies instead of an inner join being performed on the combined result of orders and customers. I basically just moved your inner join to before the ON clause for the left outer join. Someone else suggested parenthesis to get this result, both syntaxes will result in the same execution if memory serves.

SELECT Orders.OrderId, Customers.CustomerId, Companies.Name
FROM   Orders
LEFT OUTER JOIN Customers
    INNER JOIN Companies
        ON Customers.CompanyId = Companies.CompanyId
    ON Orders.CustomerId = Customers.CustomerId