INNER JOIN与CROSS JOIN与CROSS APPLY的区别

Question

SQL的乐趣之一在于完成同样的事情通常有多种方法，那么哪种方法最好呢？在这种情况下，我正在将记录从'Import'表中插入，并给它们全部设置默认的安全级别为'Viewer'(在不同的数据库中可能有不同的关键ID)。我可以用至少三种不同的方法来实现这个目标(也许还有更多): CROSS JOIN、CROSS APPLY和INNER JOIN。任何关于性能或设计目的上的建议都可以吗？我倾向于使用Cross Apply。

可能已经回答了这个问题，但是我找不到答案，而且最近我在开发中一直遇到这个需求，所以我可能会学习最好的方法。

下面是3个示例语句，请问哪种JOIN SecRole表的方式最好？

INSERT INTO LocStaff (LocationID, StaffID, SecRoleID)
    SELECT i.LocationID, s.StaffID, sr.SecRoleID
    FROM IntStaff i
        JOIN Staff s ON i.EmployeeID = s.StaffNumber
        CROSS JOIN SecRole sr
    WHERE sr.Name = 'Viewer' 

INSERT INTO LocStaff (LocationID, StaffID, SecRoleID)
    SELECT i.LocationID, s.StaffID, sr.SecRoleID
    FROM IntStaff i
        JOIN Staff s ON i.EmployeeID = s.StaffNumber
        JOIN SecRole sr ON sr.Name = 'Viewer'

INSERT INTO LocStaff (LocationID, StaffID, SecRoleID)
    SELECT i.LocationID, s.StaffID, sr.SecRoleID
    FROM IntStaff i
        JOIN Staff s ON i.EmployeeID = s.StaffNumber
        CROSS APPLY (SELECT TOP 1 SecRoleID FROM SecRole WHERE Name = 'Viewer') sr

- Scott Duncan

---

可能是 When should I use Cross Apply over Inner Join? 的重复问题。 - SqlZim

我认为在大多数情况下，“cross join”不应被视为与其他两种方法相媲美。因此，你的问题变成了“inner join vs cross apply”，这里有相关讨论：https://dev59.com/bnM_5IYBdhLWcg3w-4nw - SqlZim

这并不完全是一个重复的问题，因为另一个问题关注的是一般情况下何时使用Cross Apply，但如果你的断言是正确的，即Cross Join应该被彻底拒绝，那么我认为另一个帖子建议CROSS APPLY比INNER JOIN更快。当只拉取一条记录时，是否是这种情况，还是真的没有区别，更多的是个人喜好？ - Scott Duncan

你的示例查询在尝试比较这些不同操作的上下文中没有意义。实际上，你是用三种不同的方式从查询中提取一个常量值。你也可以使用本地变量或在选择中使用子查询。SecRoleID与你的查询的其余部分没有关系。 - SqlZim

你说得对。我正在尝试为每个插入的记录添加适当的SecRoleID。这可以使用本地变量（使用SELECT在WHERE子句中设置它为'Viewer'），或者使用子查询来实现。您觉得这两种方法是否比使用Cross Apply更好？ - Scott Duncan

Answer 1

第一种和第二种方法是等效的。在这种情况下，使用内连接还是交叉连接实际上是个人喜好问题。我认为我通常会使用“交叉连接”，因为表之间没有真正的连接条件。

注意：当意图是具有表之间匹配条件的“真正”内连接时，绝不能使用“交叉连接”。

“交叉应用”不是在做同样的事情。它只选择一行。如果您的意图是获取最多一行匹配行，则使用“交叉应用”。如果意图是获取恰好一行匹配行，则使用“外部应用”。

- Gordon Linoff

---

我不知道OUTER APPLY；听起来像是第四种做法。你可能不知道这一点（因为我没有说明），但由于目标表中的SecRoleID不为空，我确实正在寻找恰好一个匹配的行。你是在说CROSS APPLY（TOP 1）可能会返回NULL，因此它不是一个合适的解决方案吗？ - Scott Duncan

@ScottDuncan . . . 不，CROSS APPLY (TOP 1) 才是你想要的。其他的似乎不行。如果表之间没有匹配，OUTER APPLY 将返回 NULL 值。 - Gordon Linoff

你提到前两个是等价的。这是真的吗，还是Cross Join在过滤之前连接所有行，而Inner Join仅在所需的一行上连接？无论哪种方式，我都倾向于使用Cross Apply，因为它似乎最清楚地表明了意图。我担心性能问题，但SqlZim提供的链接似乎表明Cross Apply应该具有更好的性能。 - Scott Duncan

@ScottDuncan……SQL语句“描述”结果集。优化器选择最佳执行计划。我熟悉的每个优化器都会将这两者视为相同。 - Gordon Linoff

顺便说一句，因为有人提到优化器在INNER JOIN和CROSS JOIN之间选择最佳执行计划时似乎并不总是表现出最佳性能，所以我想在这里留个言。在我的情况下，两个表中有一列对于所有行都是相同的常量值。因此，在该列上进行的CROSS JOIN和INNER JOIN是相同的。然而，如果该列在后续的连接中被使用，似乎优化器没有保留它是固定的这一知识，因此进行了许多不必要的比较，而使用INNER JOIN时，它“记住”了它们匹配的事实。 - Shirik

Answer 2

我们只使用Cross join（或者只使用关键字Join）当我们的表格之间没有任何共同的列时（这不是最佳的结构设计！）或者我们需要所有可能的情况。我不知道你的表格结构，但是我假设表格SecRole与Staff和InsStaff没有外键或公共键。在这种情况下，我会在这里使用Right join（outer join），以便获得来自Staff和InsStaff之间第一个内部连接的所有结果，然后将它们放置在SecRole所需的记录旁边。

下面是Right join的概念 http://www.dofactory.com/sql/right-outer-join

- Mehrad Eslami

---

问题是，当SecRole和其他表之间没有公共键时，最好的方法是如何附加来自SecRole的一个值，那么“将它们放在”（仅一个）所需记录旁边的意思是什么？我的示例显示了三种可能的方法，我想知道哪种方法最好，或者是否有第四种更好的方法？您是否建议对SecRole进行Right Outer Join？由于没有公共键，ON子句将是什么，以及它比带有WHERE过滤器的CROSS JOIN或带有ON子句中过滤器的LEFT JOIN更好在哪里？ - Scott Duncan

抱歉，我的意思是INNER JOIN，而不是LEFT JOIN（显然你只有5分钟来捕捉并在原地修复拼写错误）。 - Scott Duncan