CROSS JOIN：只有两张桌子？ [英] CROSS JOIN: only two tables?

问题描述

似乎没有关于bigQuery SQL中的CROSS JOIN是否可用于两个以上表的文档。

There does not seem to be documentation on whether CROSS JOIN in bigQuery SQL can be used on more than two tables.

如果可能的话，我希望看到正确的语法。如果这是不可能的，那么很高兴知道什么是CROSS JOIN有用，因为使用两个表可以使用常规的JOIN。

If it's possible, I'd like to see the right syntax. If it's not possible, then it would be nice to know what is CROSS JOIN useful for, since with two tables regular JOINs can be used already.

另请参阅此问题： CROSS JOIN超过两张表

See also this question: CROSS JOIN more than two tables

推荐答案

3个表格之间的交叉连接似乎对我有用。请参阅下面的示例。

Cross join between 3 tables seem to work for me. See example below.

在某些特殊情况下，我故意要复制行时使用了交叉连接。或者当你想知道2个表之间的所有可能的组合时，也许是因为另一个连接会消除行，而你不想这样做。

I use cross joins in some special situations where I deliberately want to duplicate rows. Or when you want to know all possible combinations between 2 tables, perhaps because another join would eliminate rows while you don't want that.

SELECT
  *
FROM (
  SELECT
    *
  FROM (
    SELECT
      'a' ),
    (
    SELECT
      'b' ),
    (
    SELECT
      'c' ) ) AS X1
CROSS JOIN (
  SELECT
    *
  FROM (
    SELECT
      '1' ),
    (
    SELECT
      '2' ),
    (
    SELECT
      '3' ) ) AS X2
CROSS JOIN (
  SELECT
    *
  FROM (
    SELECT
      'x' ),
    (
    SELECT
      'y' ),
    (
    SELECT
      'z' ) ) AS X3

这篇关于CROSS JOIN：只有两张桌子？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！