交叉连接的用途是什么？ [英] What are the uses for Cross Join?

问题描述

交叉连接对两组的元组执行笛卡尔乘积。

A cross join performs a cartesian product on the tuples of the two sets.

SELECT *
FROM Table1
CROSS JOIN Table2

哪种情况下使这样的SQL操作特别有用？

Which circumstances render such an SQL operation particularly useful?

推荐答案

如果你有一个网格，你想填充完整，像大小和颜色信息的特定衣服：

If you have a "grid" that you want to populate completely, like size and color information for a particular article of clothing:

select 
    size,
    color
from
    sizes CROSS JOIN colors

也许你想要一个表，其中包含一天的每一分钟，并且你想使用它来验证过程已每分钟执行一次，因此您可能会跨越三个表：

Maybe you want a table that contains a row for every minute in the day, and you want to use it to verify that a procedure has executed each minute, so you might cross three tables:

select
    hour,
    minute
from
    hours CROSS JOIN minutes

或者您有一套标准报告您希望应用于一年中每个月的规格：

Or you have a set of standard report specs that you want to apply to every month in the year:

select
    specId,
    month
from
    reports CROSS JOIN months

维护这些视图的问题是在大多数情况下，你不想要一个完整的产品，特别是对于衣服。您可以向查询中添加 MINUS 逻辑，以删除某些您不带的组合，但是您可能会发现以其他方式填充表格而不使用笛卡尔产品。

The problem with maintaining these as views is that in most cases, you don't want a complete product, particularly with respect to clothes. You can add MINUS logic to the query to remove certain combinations that you don't carry, but you might find it easier to populate a table some other way and not use a Cartesian product.

此外，你可能最终尝试交叉连接的表可能比你想象的更多的行，或者你的 WHERE 子句部分或完全丢失。在这种情况下，您的DBA将立即通知您遗漏。通常他或她不会幸福。

Also, you might end up trying the cross join on tables that have perhaps a few more rows than you thought, or perhaps your WHERE clause was partially or completely missing. In that case, your DBA will notify you promptly of the omission. Usually he or she will not be happy.

这篇关于交叉连接的用途是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！