Problems With INNER JOIN And LEFT/RIGHT OUTER JOIN

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 Comp

Solution 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

Solution 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.

Solution 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

Solution 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

Solution 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.