Standard SQL and BigQuery SQL have a lot in common because BigQuery SQL is largely compliant with the SQL 2011 standard. However, there are some differences due to the specific features and design of Google BigQuery.

Here's a conversion of that comparison into Markdown, written in a way that avoids plagiarism:

Differences Between Standard SQL and BigQuery SQL #

While BigQuery SQL is designed to be largely compliant with the SQL 2011 standard, there are certain differences owing to the unique features and design of Google BigQuery.

Syntax #

BigQuery SQL incorporates a few extensions that aren't part of the standard SQL. For example, it includes syntax for specific operations such as:

  • Using backticks (`) to escape identifiers, like database and table names.
  • Array handling using functions like UNNEST.

Data Types #

BigQuery SQL introduces specific data types that are not typically present in standard SQL:

  • ARRAY: It is used for ordered list of zero or more elements of the same data type.
  • STRUCT or RECORD: It is used for structured data.

Functions #

BigQuery SQL supports additional functions that aren't in standard SQL:

  • String functions like REGEXP_CONTAINS, REGEXP_EXTRACT, and REGEXP_REPLACE.
  • Mathematical functions like LOG, POW, and SQRT.

Nested and Repeated Fields #

BigQuery SQL uniquely supports nested and repeated fields, allowing a more flexible schema design.

Querying #

In BigQuery SQL, you query data using the FROM clause with a dataset-table combination or use a fully-qualified table name.

Cost Model #

The cost model in BigQuery SQL is different, based on data processed rather than on data returned. It uses a columnar storage system, which means you are charged according to the amount of data read by your query, not by the total amount of data stored in the table or tables.

Federated Query #

BigQuery allows federated querying, where you can query data directly from external sources like Google Sheets, Google Cloud Storage, and Google Drive.

Each of these differences contributes to the distinctive functionality of BigQuery SQL, optimized for working with Google Cloud's big data capabilities.

Read previous:

BigQuery SQL