title: Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN
body: <p>I have three tables:</p>
  
  <ul>
  <li><strong>Orders</strong>
  <ul>
  <li>OrderId, int PK</li>
  <li>CustomerId, int FK to Customer, NULL allowed</li>
  </ul></li>
  </ul>
  
  <p><br/></p>
  
  <ul>
  <li><strong>Customers</strong>
  <ul>
  <li>CustomerId, int PK</li>
  <li>CompanyId, int FK to Company, NULL not allowed</li>
  </ul></li>
  </ul>
  
  <p><br/></p>
  
  <ul>
  <li><strong>Companies</strong>
  <ul>
  <li>CompanyId, int PK</li>
  <li>Name, nvarchar(50)</li>
  </ul></li>
  </ul>
  
  <p>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.</p>
  
  <p>If I use this query...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>...it only returns the orders that have a customer. If I replace <code>INNER JOIN</code> by <code>LEFT OUTER JOIN</code>...</p>
  
  <pre><code>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
  </code></pre>
  
  <p>...it works but I don't understand why this is necessary because the relationship between <code>Customers</code> and <code>Companies</code> is required: A customer <em>must</em> have a company.</p>
  
  <p>An alternative approach which works as well seems to be:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>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 <em>orders</em> in mind where an order is the "root" of the selection and not the company. Also the usage of <code>RIGHT OUTER JOIN</code> is rather unfamiliar to me.</p>
  
  <p>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.</p>
  
  <p>Questions:</p>
  
  <ol>
  <li>Why doesn't query 1 work as I expected?</li>
  <li>Is query 2 the correct solution although (or because?) it uses two LEFT OTHER JOINS?</li>
  <li>Is query 3 the correct solution?</li>
  <li>Is there a better way to write the query?</li>
  <li>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?</li>
  </ol>