Django order_by calls LEFT JOIN

Question

Can someone tell me why when I add order_by() query that receives output changes from INNER JOIN to LEFT OUTER JOIN ?

Is there a way to keep INNER JOIN -ness?

 data = models.RetailSalesFact.objects.values('customer_key__customer_state', 'date_key__calendar_month_name') data = data.filter(date_key__calendar_year=year) data = data.annotate(sales=Sum('sales_quantity')) data = data.order_by('date_key__calendar_month_name') 

Before:

 SELECT Customer_Dimension.Customer_State, Date_Dimension.Calendar_Month_Name, SUM(Retail_Sales_Fact.Sales_Quantity) AS sales FROM Retail_Sales_Fact INNER JOIN Customer_Dimension ON (Retail_Sales_Fact.Customer_Key = Customer_Dimension.Customer_Key) INNER JOIN Date_Dimension ON (Retail_Sales_Fact.Date_Key = Date_Dimension.Date_Key) WHERE Date_Dimension.Calendar_Year = ? GROUP BY Customer_Dimension.Customer_State, Date_Dimension.Calendar_Month_Name ORDER BY Date_Dimension.Calendar_Month_Name ASC 

After:

 SELECT Customer_Dimension.Customer_State, Date_Dimension.Calendar_Month_Name, SUM(Retail_Sales_Fact.Sales_Quantity) AS sales FROM Retail_Sales_Fact INNER JOIN Customer_Dimension ON (Retail_Sales_Fact.Customer_Key = Customer_Dimension.Customer_Key) LEFT OUTER JOIN Date_Dimension ON (Retail_Sales_Fact.Date_Key = Date_Dimension.Date_Key) WHERE Date_Dimension.Calendar_Year = ? GROUP BY Customer_Dimension.Customer_State, Date_Dimension.Calendar_Month_Name ORDER BY Date_Dimension.Calendar_Month_Name ASC 

Answer 1

I would suggest that ORM does a LEFT JOIN because it cannot determine if the constraint is an INNER JOIN , where the constraint is more or less restrictive than the ordering clause. Because he believes that he needs to order every record, regardless of whether it matches or not.

You can force INNER JOIN use Raw SQL . Or maybe you can trick ORM by applying order_by to filter ?

Answer 2

You put the filter in an external table (date_dimension__calendar_year = year), so there will be no difference between the result set, whether you are using an inner join or a left outer join.

Order processing is processed on an intermediate result set, if this is done in internal tables, then this must be done after joining the tables, which means reading: joining records; read two: order combined notes.

But if ordering is done only in the external table, in this case everything you ask for, then your query optimizer may be able to avoid reading the entire set twice, and instead, read the external table twice. Your optimizer may recognize this as a savings in terms of processing power.

This is just a hunch. Your result set should be the same anyway. I wonder if you can find time in both directions and see which one takes longer.