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.

Answer 3

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

Answer 4

Query 1 have INNER JOIN on Company , which means a Order need to have vaild Customer(CompanyID) If you want to use INNER JOIN, it can be like this

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

Answer 5

1) It doesn't work because when you INNER JOIN to Companies you make it required to exist in the entirety of the join, but since Customer does not exist for the order there is no way to associate a Companies record back to the order and thus it is not returned.

2) I suppose you could use the second query if you're ok getting Customer records with no related company, but if the relation between those tables is 1 to 1 it should be fine.

3) The third query is fine, but ugly. You join the company and customer tables and then say that regardless of what is in that resultset I want everything from Orders.

4) I would probably join customers and companies in a subquery and left join that back to orders.

Query:

SELECT  Orders.OrderId, 
        Subquery.CustomerId, 
        Subquery.Name
FROM    Orders
LEFT    OUTER JOIN 
       (Select  Customers.CustomerID,
                Companies.Name
        From    Customers
        INNER   JOIN Companies
                ON Customers.CompanyId = Companies.CompanyId) Subquery
        On Orders.CustomerID = Subquery.CustomerID

5) This is much more easily answered with a google search. I'm sure there is more comprehensive info that I could write in a couple minutes.