Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN

Question

I have three tables:

• Orders
  • OrderId, int PK
  • CustomerId, int FK to Customer, NULL allowed

• Customers
  • CustomerId, int PK
  • CompanyId, int FK to Company, NULL not allowed

• Companies
  • CompanyId, int PK
  • Name, nvarchar(50)

I want to select all orders, no matter if they have a customer or not, and if they have a customer then also the customer's company name.

If I use this query...

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

...it only returns the orders that have a customer. If I replace INNER JOIN by LEFT OUTER JOIN...

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

...it works but I don't understand why this is necessary because the relationship between Customers and Companies is required: A customer must have a company.

An alternative approach which works as well seems to be:

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

This query has the number of inner and outer joins that I expect but the problem is that it is hard to read for me because I have my query as a query of orders in mind where an order is the "root" of the selection and not the company. Also the usage of RIGHT OUTER JOIN is rather unfamiliar to me.

The last query is a small part of a query generated by the designer for SQL Server Reporting Services Reports. I am trying to write the query manually without the designer surface because it is very overcrowded and I'm having problems to maintain the query after many changes and more changes are expected in the future. So, I want to give the query a readable structure somehow.

Questions:

1. Why doesn't query 1 work as I expected?
2. Is query 2 the correct solution although (or because?) it uses two LEFT OTHER JOINS?
3. Is query 3 the correct solution?
4. Is there a better way to write the query?
5. Are there some general rules of thumb and practices how to write a query with a lot of outer and inner joins in a good readable manner?

Answer 1

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

Answer 2

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.