{"id":43596,"date":"2023-08-16T22:40:14","date_gmt":"2023-08-16T20:40:14","guid":{"rendered":"https:\/\/blog.sheetgo.com\/?p=43596"},"modified":"2025-12-17T18:44:03","modified_gmt":"2025-12-17T17:44:03","slug":"syntaxe-bigquery","status":"publish","type":"post","link":"https:\/\/www.sheetgo.com\/fr\/blog\/data-science\/bigquery-syntax\/","title":{"rendered":"Syntaxe de BigQuery"},"content":{"rendered":"

[et_pb_section fb_built=”1″ _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content” da_is_popup=”off” da_exit_intent=”off” da_has_close=”on” da_alt_close=”off” da_dark_close=”off” da_not_modal=”on” da_is_singular=”off” da_with_loader=”off” da_has_shadow=”on” da_disable_devices=”off|off|off”][et_pb_row _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_column type=”4_4″ _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

BigQuery<\/a> is Google\u2019s fully-managed and serverless data warehouse that allows businesses to store and handle large amounts of data without having to invest in infrastructure. In order to retrieve data from BigQuery, they can use SQL syntax to run queries.<\/p>\n

In this article, you’ll find an easy guide to the BigQuery SQL syntax and learn how to write your first SQL queries.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”syntax” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

BigQuery SQL Syntax<\/h2>\n

SQL syntax refers to the language rules that you need to follow when writing SQL queries. Whenever you write a statement, you’ll use keywords such as SELECT, FROM, WHERE, and ORDER BY.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” background_color=”rgba(240,232,255,0.73)” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

Note: In order to improve readability, we’ll follow some best practices to write SQL queries.<\/p>\n

1) We’ll use uppercase to distinguish between keywords and tables or columns. Therefore, we’ll capitalize keywords but we’ll use lowercase for all tables and columns.\u00a0<\/p>\n

2) Although we can write multiple statements into the same line, we’ll separate statements using new lines.\u00a0<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

If you want to get started with BigQuery, you’ll find a quick-start guide to SQL syntax below.<\/p>\n

[\/et_pb_text][et_pb_text module_id=”select” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

SELECT<\/h3>\n

We’ll use the SELECT statement to select the columns the query will return.<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

SELECT Syntax<\/b><\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

SELECT column1, column2, …<\/p>\n

FROM table_name;<\/p>\n

[\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

In this example, we’ll show you how to use SQL queries to retrieve data from an NCAA basketball dataset.<\/p>\n

First, we’ll execute a SQL query to select three columns – school_ncaa, name, and alias – from this dataset.\u00a0<\/p>\n

After the SELECT statement, add all the columns you want to select, separated by commas. The FROM clause specifies the table we are querying to retrieve the data.\u00a0<\/p>\n

[\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

SELECT school_ncaa, name, alias\u00a0\u00a0\u00a0<\/p>\n

FROM `bigquery-public-data.ncaa_basketball.mbb_teams` ;<\/p>\n

[\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-1.png” alt=”bigquery syntax 1″ title_text=”bigquery syntax 1″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

This query has returned rows with the following columns:<\/p>\n

    \n
  • school_ncaa – This column contains the school name associated with each team.<\/li>\n
  • name – This column has the team name for each row.<\/li>\n
  • alias – This column contains common aliases or abbreviated names used for some teams.<\/li>\n<\/ul>\n

    As you can see, it has returned all the data I’ve requested split into columns.\u00a0<\/p>\n

    In this example, I’ve asked BigQuery to return data from these three columns. If you want to select all columns from the dataset, use an asterisk (*) instead.<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT *\u00a0<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams` ;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-2.png” alt=”bigquery syntax 2″ title_text=”bigquery syntax 2″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”where” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    WHERE<\/h3>\n

    We’ll use the WHERE clause to filter data from the dataset.<\/p>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    WHERE Syntax<\/b><\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT column1, column2, …<\/p>\n

    FROM table_name<\/p>\n

    WHERE condition;<\/p>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Let’s say I want to retrieve data from all the columns but only for the rows where the name column equals ‘Crimson’.\u00a0<\/p>\n

    So let’s break down the formula:<\/p>\n

    SELECT * – This specifies we want all columns returned. The * selects every column.<\/p>\n

    FROM bigquery-public-data.ncaa_basketball.mbb_teams – This specifies the table we are querying, the mbb_teams table.<\/p>\n

    WHERE name = ‘Crimson’ – This WHERE clause filters the rows to only those where the name column value equals ‘Crimson’.<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT *\u00a0\u00a0<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name = ‘Crimson’;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-3.png” alt=”bigquery syntax 3″ title_text=”bigquery syntax 3″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    This BigQuery SQL query has returned all columns for rows from the mbb_teams table where the name column equals ‘Crimson’.<\/p>\n

    Now I’ll use the OR condition because I want BigQuery to retrieve rows that match either ‘Crimson’ or ‘Tigers’ team names. I’ll add an OR condition to the WHERE clause. <\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name = ‘Crimson’ OR name = ‘Tigers’;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-4.png” alt=”bigquery syntax 4″ title_text=”bigquery syntax 4″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Now BigQuery has returned data for rows where the name is either ‘Crimson’ or ‘Tigers’.<\/p>\n

    If I want to return multiple values with a WHERE clause, I can add an IN condition to the formula. When I add IN to the WHERE statement, BigQuery will return all the values in brackets.<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name IN (‘Crimson’,’Tigers’,’Gators’);<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-5.png” alt=”bigquery syntax 5″ title_text=”bigquery syntax 5″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    If you want to add multiple filters or multiple criteria to be met in the query results, you should use the AND condition. The AND condition will return values only when both conditions are satisfied.<\/p>\n

    In this example, I want BigQuery to return values only for these teams: Auburn Tigers or Clemson Tigers. Therefore, both criteria must be met. The name should be “Tigers”, and the school should be Auburn or Clemson. Here’s the formula I’ll use:<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name = ‘Tigers’ AND school_ncaa = ‘Auburn’ OR school_ncaa = ‘Clemson’;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-6.png” alt=”bigquery syntax 6″ title_text=”bigquery syntax 6″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    Now let’s say I want BigQuery to filter rows where the name starts with ‘A’. Unlike the previous examples, when I used the WHERE statement to return exact values, here I’ll use the LIKE operator for partial string matching. Here’s how I can use the LIKE command:<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name LIKE ‘A%’;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-7.png” alt=”bigquery syntax 7″ title_text=”bigquery syntax 7″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    These are some examples of how you can use the LIKE command to filter data based on specific criteria.<\/p>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n\n\n\n\n\n\n\n\n
    \n

    LIKE\u00a0<\/b><\/p>\n<\/td>\n

    		\n

    Result<\/b><\/p>\n<\/td>\n<\/tr>\n

    \n

    LIKE ‘a%’<\/p>\n<\/td>\n

    		\n

    Returns values that start with “a”<\/p>\n<\/td>\n<\/tr>\n

    \n

    LIKE ‘%a’<\/p>\n<\/td>\n

    		\n

    Returns values that end with “a”<\/p>\n<\/td>\n<\/tr>\n

    \n

    LIKE ‘%ty%’<\/p>\n<\/td>\n

    		\n

    Returns values that have “ty” in any position<\/p>\n<\/td>\n<\/tr>\n

    \n

    LIKE ‘_s%’<\/p>\n<\/td>\n

    		\n

    Returns values that have “s” in the second position<\/p>\n<\/td>\n<\/tr>\n

    \n

    LIKE ‘a%s’<\/p>\n<\/td>\n

    		\n

    Returns values that start with “a” and ends with “s”<\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    If I want BigQuery to filter rows where the name starts with ‘A’, but exclude names starting with ‘Ag’, I can add a NOT LIKE command.<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name LIKE ‘A%’ AND name NOT LIKE ‘Ag%’;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-8.png” alt=”bigquery syntax 8″ title_text=”bigquery syntax 8″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”order” _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    ORDER BY<\/h3>\n

    The ORDER BY statement is used to sort values from a specific column in ascending or descending order.<\/p>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    ORDER BY syntax<\/b><\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT column1, column2, …<\/p>\n

    FROM table_name<\/p>\n

    ORDER BY column1, column2, … ASC|DESC;<\/p>\n

    [\/et_pb_text][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    I’ll write a query to retrieve data filtered by a specific team name and order the results by school in ascending order. When I order results in ascending order, the ASC condition is optional.<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name = ‘Tigers’<\/p>\n

    ORDER BY school_ncaa;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-9.png” alt=”bigquery syntax 9″ title_text=”bigquery syntax 9″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text _builder_version=”4.21.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    As you can see, BigQuery has sorted the school names alphabetically. If I add the DESC condition, it will sort the school names in descending order.<\/p>\n

    [\/et_pb_text][et_pb_text module_class=”spreadsheet-function” _builder_version=”4.21.2″ border_width_left=”4px” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    SELECT alias, school_ncaa, name, market<\/p>\n

    FROM `bigquery-public-data.ncaa_basketball.mbb_teams`<\/p>\n

    WHERE name = ‘Tigers’<\/p>\n

    ORDER BY school_ncaa DESC;<\/p>\n

    [\/et_pb_text][et_pb_image src=”https:\/\/static.sheetgo.com\/wp-content\/uploads\/2023\/08\/bigquery-syntax-10.png” alt=”bigquery syntax 10″ title_text=”bigquery syntax 10″ align=”center” _builder_version=”4.22.2″ _module_preset=”default” width=”100%” global_colors_info=”{}” theme_builder_area=”post_content”][\/et_pb_image][et_pb_text module_id=”connect” _builder_version=”4.27.2″ _module_preset=”default” global_colors_info=”{}” theme_builder_area=”post_content”]<\/p>\n

    How to connect BigQuery to Google Sheets<\/h2>\n

    There you have it! That’s how you can write your first queries using SQL in BigQuery. If you’re a spreadsheet user, check out this article on getting data from BigQuery to Google Sheets automatically.<\/a><\/p>\n

    [\/et_pb_text][\/et_pb_column][\/et_pb_row][\/et_pb_section]<\/p>\n","protected":false},"excerpt":{"rendered":"

    BigQuery is Google\u2019s fully-managed and serverless data warehouse that allows businesses to store and handle large amounts of data without having to invest in infrastructure. In order to retrieve data from BigQuery, they can use SQL syntax to run queries. In this article, you’ll find an easy guide to the BigQuery SQL syntax and learn […]<\/p>\n","protected":false},"author":42,"featured_media":43843,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_et_pb_use_builder":"on","_et_pb_old_content":"","_et_gb_content_width":"","footnotes":""},"categories":[31],"tags":[],"class_list":["post-43596","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-data-science"],"acf":[],"permalink_manager":null,"_links":{"self":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/43596","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/users\/42"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/comments?post=43596"}],"version-history":[{"count":0,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/posts\/43596\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media\/43843"}],"wp:attachment":[{"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/media?parent=43596"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/categories?post=43596"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sheetgo.com\/fr\/wp-json\/wp\/v2\/tags?post=43596"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}