CROSS JOIN超过两个表 [英] CROSS JOIN more than two tables

问题描述

我有三张表，t1，t2和t3。表结构和值：

  t1_id | t1_k1 | t1_val 
 ------ + ------- + -------- 
 1 | k1foo | t1foo 
 2 | k1bar | t1bar 
 3 | k1baz | t1baz 
 
 t2_id | t2_k1 | t2_k2 | t2_val 
 ------ + ------- + ------- + -------- 
 1 | k1foo | k2foo | t2foo 
 2 | k1bar | k2bar | t2bar 
 3 | k1baz | k2baz | t2baz 
 
 t3_id | t3_k2 | t3_val 
 ------ + ------- + -------- 
 1 | k2foo | t3foo 
 2 | k2bar | t3bar 
 3 | k2baz | t3baz 
  

所以对于普通的SQL，我可以在t1_k1 = t2_k1上连接t1和t2，在t2_k2上连接t2和t3 = t3_k2，即：

  SELECT t1_val，t2_val，t3_val 
从t1，t2，t3 
 WHERE t1_k1 = t2_k1 AND t2_k2 = t3_k2 
  

要获得

  t1foo | t2foo | t3foo 
 t1bar | t2bar | t3bar 
 t1baz | t2baz | t3baz 
  

现在我试图用bigQuery SQL获得相同的结果。根据我的理解，使用CROSS JOIN应该像普通SQL中的逗号一样工作，所以我认为以下方法可行：

  SELECT t1_val，t2_val，t3_val 
 FROM test.t1 CROSS JOIN test.t2 CROSS JOIN test.t3 
 WHERE t1_k1 = t2_k1 AND t2_k2 = t3_k2 
  

但是我收到一个错误消息：在'JOIN的任一侧没有找到'Field't3_k2'。
像这样添加括号：

pre code> SELECT SELECT t1_val，t2_val，t3_val
FROM（（test.t1 CROSS JOIN test.t2）CROSS JOIN test.t3）
WHERE t1_k1 = t2_k1 AND t2_k2 = t3_k2

在第0行第0列给出了一个（坦率地说相当神秘的）语法错误Encountered。
只有两个表CROSS JOIN可以工作，但它与正常的JOIN没有区别。
我没有在两个以上的表上找到使用CROSS JOIN的例子。可能吗？我做错了什么？

解决方案

  SELECT t1_val，t2_val，t3_val 
 FROM（
 SELECT t1_val，t2_val，t2_k2 
 FROM test.t1 AS 
 CROSS JOIN test.t2 AS b 
 WHERE t1_k1 = t2_k1 
）AS c 
 CROSS JOIN test.t3 AS d 
 WHERE t2_k2 = t3_k2 
  
  SELECT t1_val，t2_val，t3_val 
 FROM（
 SELECT t1_val，t2_val，t2_k2 
 FROM test.t1作为JOIN test.t2 AS b 
 ON t1_k1 = t2_k1 
）AS c 
 JOIN test.t3 AS d ON t2_k2 = t3_k2 
  
 
 
 
 
 
为第二个查询添加可运行的示例 - 证明它的工作原理：o ）
 
 
 
 
 
  S ELECT t1_val，t2_val，t3_val 
 FROM（
 SELECT t1_val，t2_val，t2_k2 
 FROM（
 SELECT * FROM 
（SELECT 1 AS t1_id，'k1foo'AS t1_k1 ，'t1foo'AS t1_val），
（SELECT 2 AS t1_id，'k1bar'AS t1_k1，'t1bar'AS t1_val），
（SELECT 3 AS t1_id，'k1baz'AS t1_k1，'t1baz' AS t1_val）
）AS a 
 JOIN（
 SELECT * FROM 
（SELECT 1 AS t2_id，'k1foo'AS t2_k1，'k2foo'AS t2_k2，'t2foo'AS t2_val ），
（SELECT 2 AS t2_id，'k1bar'AS t2_k1，'k2bar'AS t2_k2，'t2bar'AS t2_val），
（SELECT 3 AS t2_id，'k1baz'AS t2_k1，'k2baz' AS t2_k2，'t2baz'AS t2_val）
）AS b 
 ON t1_k1 = t2_k1 
）as c 
 JOIN（
 SELECT * FROM 
（SELECT 1 AS t3_id，'k2foo'AS t3_k2，'t3foo'AS t3_val），
（SELECT 2 AS t3_id，'k2bar'AS t3_k2，'t3bar'AS t3_val），
（SELECT 3 AS t3_id， 'k2baz'AS t3_k2，'t3baz'AS t3_val）
）AS d 
 ON t2_k2 = t3_k2 
  
 
I have three tables, t1, t2 and t3. Table structure and values:
t1_id | t1_k1 | t1_val
------+-------+--------
1     | k1foo | t1foo
2     | k1bar | t1bar
3     | k1baz | t1baz

t2_id | t2_k1 | t2_k2 | t2_val
------+-------+-------+--------
1     | k1foo | k2foo | t2foo
2     | k1bar | k2bar | t2bar
3     | k1baz | k2baz | t2baz

t3_id | t3_k2 | t3_val
------+-------+--------
1     | k2foo | t3foo
2     | k2bar | t3bar
3     | k2baz | t3baz
So with normal SQL I can join t1 and t2 on t1_k1 = t2_k1 and t2 and t3 on t2_k2 = t3_k2, that is:
SELECT t1_val, t2_val, t3_val
FROM t1, t2, t3
WHERE t1_k1 = t2_k1 AND t2_k2 = t3_k2
To get
t1foo | t2foo | t3foo
t1bar | t2bar | t3bar
t1baz | t2baz | t3baz
Now I'm trying to get the same result with bigQuery SQL. From my understanding, using CROSS JOIN should work just like the comma in regular SQL, so I thought the following would work:
SELECT t1_val, t2_val, t3_val
FROM test.t1 CROSS JOIN test.t2 CROSS JOIN test.t3
WHERE t1_k1 = t2_k1 AND t2_k2 = t3_k2
But I get an error that "Field 't3_k2' not found on either side of the JOIN".
Adding parentheses like this:
SELECT SELECT t1_val, t2_val, t3_val
FROM ((test.t1 CROSS JOIN test.t2) CROSS JOIN test.t3)
WHERE t1_k1 = t2_k1 AND t2_k2 = t3_k2
gives a (frankly quite cryptic) syntax error 'Encountered "" at line 0, column 0.'
With just two tables CROSS JOIN works, but then it's no different from a normal JOIN.
I haven't found examples using CROSS JOIN on more than two tables. Is it possible? Am I doing something wrong?
解决方案
Try below  
SELECT t1_val, t2_val, t3_val 
FROM (
  SELECT t1_val, t2_val, t2_k2
  FROM test.t1 AS a 
  CROSS JOIN test.t2 AS b
  WHERE t1_k1 = t2_k1 
) AS c
CROSS JOIN test.t3 AS d
WHERE t2_k2 = t3_k2
Note: I think your example is just tailored to address your CROSS JOIN issue - but wanted to mention that if your example really represents your issue  - you do not need CROSS JOIN - rather you need [INNER] JOIN, as in below  
SELECT t1_val, t2_val, t3_val
FROM (
  SELECT t1_val, t2_val, t2_k2
  FROM test.t1 AS a JOIN test.t2 AS b
  ON t1_k1 = t2_k1 
) AS c
JOIN test.t3 AS d ON t2_k2 = t3_k2



  
Added runnable example for second query - to prove it works :o)


SELECT t1_val, t2_val, t3_val
FROM (
  SELECT t1_val, t2_val, t2_k2
  FROM (
    SELECT * FROM 
    (SELECT 1 AS t1_id, 'k1foo' AS t1_k1, 't1foo' AS t1_val),
    (SELECT 2 AS t1_id, 'k1bar' AS t1_k1, 't1bar' AS t1_val),
    (SELECT 3 AS t1_id, 'k1baz' AS t1_k1, 't1baz' AS t1_val)
  ) AS a 
  JOIN (
    SELECT * FROM 
    (SELECT 1 AS t2_id, 'k1foo' AS t2_k1, 'k2foo' AS t2_k2, 't2foo' AS t2_val),
    (SELECT 2 AS t2_id, 'k1bar' AS t2_k1, 'k2bar' AS t2_k2, 't2bar' AS t2_val),
    (SELECT 3 AS t2_id, 'k1baz' AS t2_k1, 'k2baz' AS t2_k2, 't2baz' AS t2_val)
  ) AS b
  ON t1_k1 = t2_k1 
) as c
JOIN (
  SELECT * FROM 
  (SELECT 1 AS t3_id, 'k2foo' AS t3_k2, 't3foo' AS t3_val),
  (SELECT 2 AS t3_id, 'k2bar' AS t3_k2, 't3bar' AS t3_val),
  (SELECT 3 AS t3_id, 'k2baz' AS t3_k2, 't3baz' AS t3_val)
) AS d
ON t2_k2 = t3_k2


                        
这篇关于CROSS JOIN超过两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！