How does this odd BigQuery SQL syntax work?-CodePudding

I'm relatively new to BigQuery but I've been writing SQL for years; so you can imagine my surprise when I found a query written by a colleague like this: (simplified)

SELECT
    t1.user_id,
    t2.value,
    MAX(t1.order_id) 
FROM orders t1, 
    (SELECT 
         value
     FROM products) t2
WHERE t1.order_id > t2.value
GROUP BY 1, 2

He is selecting from two tables but without joining them - each table is just separated by a comma! Is this something normal in SQL or a join shorthand unique to BigQuery? How does it work?

CodePudding user response：

It's standard SQL, works everywhere (at least the table list part. GROUP BY 1, 2 doesn't work everywhere), and is the same as this:

SELECT
    t1.user_id,
    t2.value,
    MAX(t1.order_id) 
FROM orders t1
CROSS JOIN (
  SELECT value
  FROM products
) t2
WHERE t1.order_id > t2.value
GROUP BY 1, 2

If you list more than one table in the FROM clause, you're just going to get a cross product between all the listed tables.