Geek Answers Handbook

Problems with INNER JOIN and LEFT / RIGHT OUTER JOIN

I have three tables:

  • Orders
    • OrderId, int PK
    • CustomerId, int FK for the client, NULL allowed


  • Customers
    • CustomerId, int PK
    • CompanyId, int FK for company, NULL not allowed


  • Companies
    • CompanyId, int PK
    • Name, nvarchar (50)

I want to select all orders, regardless of whether they have a client or not, and if they have a client, then also the name of the client's company.

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

... he only returns orders with the customer. If I replaced INNER JOIN with 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 it is necessary, because the connection between Customers and Companies is required: the client must have a company.

An alternative approach that works also looks like this:

 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 request has the number of internal and external associations that I expect, but the problem is that it is difficult to read for me, because I have my request as a request for orders in mind where the order is the "root" and not the company. Also, using RIGHT OUTER JOIN is pretty unfamiliar to me.

The final query is a small part of the query created by the designer for SQL Server Services reporting reports. I am trying to write a query manually without a constructor surface because it is very crowded and I am having problems saving the query after many changes, and more changes are expected in the future. So, I want to somehow provide a request for a readable structure.

Questions:

  • Why not request 1 job, as I expected?
  • Is query 2 the right solution, though (or because?) Does it use two LEFT OTHER JOINS?
  • Is query 3 the right solution?
  • Is there a better way to write a query?
  • Are there any general rules and practices on how to write a query with a large number of external and internal connections in good readability?
+7
source share
5 answers

Semantically, joins are processed in the order in which they appear in the from clause. (They cannot actually be executed in this order due to SQL optimization, but ordering is important to determine the result set.)

So when you do this:

 from orders left outer join customers inner join companies

(I am leaving on sentences that are distractions for this purpose.)

SQL is interpreted as:

 from (orders left outer join customers) inner join companies

You do an inner join , so the values ​​should be displayed on both sides. In your case, this cancels the effect of left outer join .

Do you want to:

 from orders left outer join (customers inner join companies)

Here are some solutions.

My preferred solution is to use left outer join for all joins. In fact, for ease of reading and maintenance, almost every query that I write will consist only of a left outer join or [inner] join joining these tables. The need to parse a query to understand the semantics of joins seems unnecessary if you can write queries in a consistent manner.

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
+11
source
  • Request 1: since you have an INNER JOIN for clients, LEFT JOIN is an INNER JOIN .
  • Request 2 is correct because you want to see all Orders regardless of the quality / condition of the data.
  • I like to avoid RIGHT JOIN in general, as it confuses some developers and is therefore less readable. You can write your request in such a way as to do the same with efficient use of LEFT JOIN s.
  • Query 2 is my recommendation for something simple.
  • One general rule ... After entering an OUTER JOIN into your query, the JOIN that follows it should also be OUTER JOIN s. Otherwise, you CAN exclude rows that you did not plan.
+4
source

You can enter your investments in such a way that the left connection is performed by the combined result of customers and companies instead of the internal connection performed by the combined result of orders and customers. I just moved your inner join to the ON clause for the left outer join. Someone else suggested brackets to get this result, both syntaxes will lead to the same execution if memory will be used.

 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
+3
source

Request 1 has an INNER JOIN on the company, which means that the order must have a client Vaild (CompanyID). If you want to use an INNER JOIN, it could be like

 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
+2
source

1) This does not work because when you INNER JOIN before Companies , you force it to exist in the entirety of the connection, but since Customer does not exist for the order, there is no way to link a Companies written back in order and therefore is not returned.

2) Suppose you could use the second query if you normally receive Customer records without the appropriate company, but if the ratio between these tables is 1 to 1, this should be good.

3) The third request is good, but ugly. You join the tables of companies and customers, and then say that no matter what is in this result set, I want everything to be from Orders .

4) I would probably join clients and companies in the subquery and leave them to return 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) It is much easier to get through a search on Google. I am sure that there is more complete information that I could write in a couple of minutes.

+2
source

More articles:


All Articles