Skip to content
Tutorials Center
My learning

Topics

Lesson 3 of 37 ยท Getting Started

Your First BigQuery Queries

Write your first BigQuery queries: SELECT, WHERE, ORDER BY, LIMIT and aliases, with the BigQuery habits that matter early: naming tables, avoiding SELECT * and reading the byte estimate.

You now know what BigQuery is and where the buttons are. Time to write SQL. If you have used SQL before, the first queries will feel familiar. This lesson also introduces the first BigQuery-specific habits: how tables are named, why LIMIT does not make a query cheaper, and why you list columns instead of writing SELECT *.

The runnable examples use our shop database as a stand-in. The BigQuery versions differ only in how the table is named, which we show in read-only blocks.

SELECT, FROM and WHERE

A query picks columns from a table and filters rows:

SQL Stand-in: select and filter ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

In BigQuery against your own dataset it reads almost the same (read-only, because it needs a real project):

SQL
SELECT name, price, stock
FROM `my-project.shop.products`
WHERE price > 30
ORDER BY price DESC;

The only visible difference is the fully qualified table name in backticks: project.dataset.table. If you set a default project in the console you can drop the project part, and if you also use a default dataset you can write just products.

Aliases and expressions

Give calculated columns a name with AS. This matters even more in BigQuery, because a result without a name gets an auto-generated one such as f0_.

SQL Stand-in: computed columns ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

BigQuery lets you use a SELECT alias in GROUP BY, HAVING and ORDER BY, but not in WHERE; there you repeat the expression or wrap the query in a subquery or CTE, exactly as in other databases.

Sorting and LIMIT

ORDER BY sorts and LIMIT caps the number of rows returned. The important BigQuery fact is this:

LIMIT does not reduce the cost of a query. BigQuery bills by the bytes it reads from the columns you reference. SELECT * FROM big_table LIMIT 10 still reads every column of the whole table (unless the table is clustered in a way that lets BigQuery stop early). To explore cheaply, use the table's Preview tab, which is free, or select only the few columns you need and add a partition filter. You will see how in Partitioning and Clustering.

Do not use SELECT *

Because BigQuery stores data by column, each column you add is more data read and more money spent. A table with 80 columns and a billion rows makes SELECT * expensive and slow. Name the columns you need.

BigQuery adds two helpers for the case where you want almost everything:

SQL
-- every column except two
SELECT * EXCEPT (internal_notes, raw_payload)
FROM `my-project.shop.products`;

-- every column, but replace one of them
SELECT * REPLACE (ROUND(price, 0) AS price)
FROM `my-project.shop.products`;

We cover these properly in SELECT EXCEPT, REPLACE and Friends.

Filtering with several conditions

AND, OR, NOT, IN, BETWEEN, LIKE and IS NULL work as in standard SQL:

SQL Stand-in: combine conditions ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Two BigQuery details:

  • Strings use single quotes ('Turin') or double quotes ("Turin"). Backticks are for identifiers (table and column names that contain special characters or are reserved words), never for strings.
  • NULL follows three-valued logic. WHERE x = NULL returns nothing; write WHERE x IS NULL.

Distinct values and counting

SQL Stand-in: distinct and count ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

SQL Stand-in: how many rows? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

COUNT(*) on an unfiltered table is answered from metadata in BigQuery, so it scans zero bytes. COUNT(DISTINCT x) is exact but heavier; for huge tables BigQuery offers APPROX_COUNT_DISTINCT(x), which is much cheaper and usually within about one percent.

A short checklist before you press Run

  1. Does the byte estimate look reasonable?
  2. Did I name only the columns I need?
  3. Is there a filter on the partition column? (More on this later.)
  4. Are the table names fully qualified, with backticks?
  5. Did I name every computed column with AS?

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. How is a fully qualified BigQuery table written?

    Backticks quote identifiers in GoogleSQL. Single quotes are for string values.
  2. 2. Does adding LIMIT 10 to SELECT * make the query cheaper?

    LIMIT trims the output, not the scan. Selecting fewer columns and filtering partitions reduces cost.
  3. 3. Which is the correct way to find rows where discount is missing?

    Comparisons with NULL give NULL, which is not true. Use IS NULL.
  4. 4. Why should you name computed columns with AS?

    Unnamed expressions get meaningless names that are awkward for dashboards and downstream code.

Summary

  • Tables are addressed as `project.dataset.table` with backticks.
  • SELECT, WHERE, ORDER BY, DISTINCT and COUNT behave as in standard SQL.
  • LIMIT shortens output but does not cut the bytes scanned; Preview is the free way to look at a table.
  • Avoid SELECT *: name columns, or use EXCEPT and REPLACE.
  • Always alias computed columns and check the byte estimate before running.

Next: Projects, Datasets and Tables