sql - Problems with INNER JOIN and LEFT/RIGHT OUTER JOIN -

i have 3 tables:

• orders
  • orderid, int pk
  • customerid, int fk customer, null allowed

• customers
  • customerid, int pk
  • companyid, int fk company, null not allowed

• companies
  • companyid, int pk
  • name, nvarchar(50)

i want select orders, no matter if have customer or not, , if have customer customer's company name.

if use query...

select orders.orderid, customers.customerid, companies.name   orders        left outer join customers            on orders.customerid = customers.customerid        inner join companies            om customers.companyid = companies.companyid 

...it returns orders have customer. if replace inner join left outer join...

select orders.orderid, customers.customerid, companies.name   orders        left outer join customers            on orders.customerid = customers.customerid        left outer join companies            om customers.companyid = companies.companyid 

...it works don't understand why necessary because relationship between customers , companies required: customer must have company.

an alternative approach works seems be:

select orders.orderid, customers.customerid, companies.name   companies        inner join customers            on companies.companyid = customers.companyid        right outer join orders            om customers.customerid orders.customerid 

this query has number of inner , outer joins expect problem is hard read me because have query query of orders in mind order "root" of selection , not company. usage of right outer join rather unfamiliar me.

the last query small part of query generated designer sql server reporting services reports. trying write query manually without designer surface because overcrowded , i'm having problems maintain query after many changes , more changes expected in future. so, want give query readable structure somehow.

questions:

1. why doesn't query 1 work expected?
2. is query 2 correct solution although (or because?) uses 2 left other joins?
3. is query 3 correct solution?
4. is there better way write query?
5. are there general rules of thumb , practices how write query lot of outer , inner joins in readable manner?

semantically, joins processed in order appear in from clause. (they may not executed in order due sql optimizations, ordering important defining result set.)

so, when do:

from orders left outer join customers inner join companies 

(i'm leaving out on clauses distraction purpose.)

the sql interpreted as:

from (orders left outer join customers) inner join companies 

you doing inner join, values must appear on both sides. in case, undoes effect of left outer join.

you want:

from orders left outer join (customers inner join companies) 

here solutions.

my preferred solution use left outer join joins. in fact, readability , maintainability, every query write going left outer join or [inner] join connecting tables. having parse through query understand semantics of joins seems unnecessary effort, if can write queries in consistent form.

another solution use parentheses:

from orders left outer join (customers inner join companies) 

another solution subquery:

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