Search code examples
sqlgoogle-bigquery

Why shall we put LIMIT after WHERE in Big Query SQL and follow it by a semicolon?

When there is no semicolon after LIMIT it doesnt work!

I have tried to understand why WHERE doesnt need a semicolon. I wonder if it is the same rule for all SQL programs or only for Big Query. I would like to understand if there are other words that need a semicolon like LIMIT

Solution

  • In BigQuery, similar to many SQL dialects, the semicolon is used to separate statements from each other, creating a "sequence" of statements if used correctly.

    For example, if you try to run:

    SELECT * FROM `my_dataset.my_table`
SELECT * FROM `my_dataset.my_table2`

    It will not work.

    However, if you try to run:

    SELECT * FROM `my_dataset.my_table`;
SELECT * FROM `my_dataset.my_table2`;

    It will work.

    This is because BigQuery understands exactly where each statement ends and where the next one begins, thanks to the semicolons.

    The semicolon is NOT related specifically to the "LIMIT" clause. My guess is that you had multiple statements in the same query page, and when you provided the semicolon, it was formatted correctly. Once you removed the semicolon - BigQuery was lost in translating your code.

    If you want a specific answer to your scenario, you can provide us with the code you were trying to run.

    See more information about BigQuery semicolon here: https://cloud.google.com/bigquery/docs/reference/standard-sql/lexical#terminating_semicolons