sql - Django order_by 导致 LEFT JOIN

Question

谁能告诉我为什么当我添加 order_by() 时从 INNER JOIN 获取输出更改的查询到 LEFT OUTER JOIN ?

有什么办法可以保存INNER JOIN - 是吗？

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')

之前:

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

之后:

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

您在外部表 (date_dimension__calendar_year=year) 上放置了一个过滤器，因此无论您使用内部连接还是左外部连接，结果集之间都没有区别。

order-by 是在一个中间结果集上处理的——如果在内部连接的表上完成，那么它必须在表合并后完成——这意味着读取一个:合并记录；阅读二:对合并记录进行排序。

但是如果 order-by 只在外连接表上完成，在这种情况下就是你所要求的，那么你的查询优化器可能能够避免读取整个集合两次，而是只读取外 table 两次。您的优化器可能会将其视为处理能力方面的节省。

这只是一个猜测。无论哪种方式，您的结果集都应该是相同的。我想知道你是否可以两种方式都计时，看看哪一种需要更长的时间。