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.
- Why doesn't query 1 work as I expected?
- Is query 2 the correct solution although (or because?) it uses two LEFT OTHER JOINS?
- Is query 3 the correct solution?
- Is there a better way to write the query?
- 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?
5条答案
按热度按时间o4tp2gmn1#
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:
(I'm leaving out the
on
clauses which are a distraction for this purpose.)The SQL is interpreted as:
You are doing an
inner join
, so the values must appear on both sides. In your case, this undoes the effect of theleft outer join
.You want:
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 onlyleft 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:
Another solution is a subquery:
mqxuamgl2#
INNER JOIN
on Customers, theLEFT JOIN
is effectively anINNER JOIN
.RIGHT JOIN
s 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 ofLEFT JOIN
s.OUTER JOIN
into your query, theJOIN
s that follow should also beOUTER JOIN
s. Otherwise, you MAY exclude rows you did not intend.eqoofvh93#
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.
0s0u357o4#
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
kdfy810k5#
It doesn't work because when you
INNER JOIN
toCompanies
you make it required to exist in the entirety of the join, but sinceCustomer
does not exist for the order there is no way to associate aCompanies
record back to the order and thus it is not returned.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.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
.I would probably join customers and companies in a subquery and left join that back to orders.
Query: