Emulating VALUES in GoogleSQL?

Question

Is the following the closest, least verbose way to emulate a VALUES clause in BigQuery?

WITH Roster AS
 (SELECT * FROM UNNEST([
    STRUCT('Adams' as LastName, 50 as SchoolID),
   ('Buchanan', 52),
   ('Coolidge', 52),
   ('Davis', 51),
   ('Eisenhower', 77)
])) SELECT * FROM Roster

The old way I was doing and what Google uses in their docs is something like:

WITH Roster AS
 (SELECT 'Adams' as LastName, 50 as SchoolID UNION ALL
  SELECT 'Buchanan', 52 UNION ALL
  SELECT 'Coolidge', 52 UNION ALL
  SELECT 'Davis', 51 UNION ALL
  SELECT 'Eisenhower', 77)
SELECT * FROM Roster

In Postgres or something I'd do:

WITH Roster(LastName, SchoolID) AS (
    VALUES('Adams', 50),
   ('Buchanan', 52),
   ('Coolidge', 52),
   ('Davis', 51),
   ('Eisenhower', 77)
) SELECT * FROM Roster

Which is quite similar to the first approach.

Yes and see stackoverflow.com/questions/69715211/select-values-in-bigquery — SQLmojoe
– SQLmojoe, Commented Apr 3, 2024 at 23:18

Mel · Accepted Answer · 2024-04-11 18:58:43Z

1

That's exactly right! Using UNNEST is a great approach for creating sample data. It's more efficient than using multiple UNION ALL statements, especially when dealing with larger datasets. UNNEST treats the entire array as a single operation, whereas UNION ALL would process each row individually.

Collectives™ on Stack Overflow

Emulating VALUES in GoogleSQL?

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related