How to Use the SELECT Statement in BigQuery

Galaxy Glossary

How do I use the SELECT statement in BigQuery?

SELECT retrieves columns from one or more BigQuery tables and can filter, sort, aggregate, and join data.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What does the SELECT statement do in BigQuery?

SELECT lets you pull specific columns, calculated expressions, or aggregated results from BigQuery tables. It supports WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, JOIN, and subqueries, enabling detailed analysis without exporting data.

How do I write a basic SELECT?

Start with SELECT column_list FROM dataset.table;. Replace column_list with * for all columns or with a comma-separated list of columns you need. Qualify tables with dataset names to avoid ambiguity.

How can I filter rows?

Use WHERE to limit results. Combine conditions with AND/OR and use parameterized queries for safety and caching.

Example

SELECT id, name FROM ecommerce.Customers WHERE created_at > DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY);

How do I sort and limit results?

ORDER BY sorts rows; LIMIT returns the first N rows. Combine both to paginate or preview data.

Example

SELECT id, total_amount FROM ecommerce.Orders ORDER BY total_amount DESC LIMIT 10;

How do I join multiple tables?

INNER, LEFT, RIGHT, and FULL JOINs combine data across tables. Always alias tables to keep queries readable and prevent name clashes.

Example

SELECT c.name, o.order_date, o.total_amount FROM ecommerce.Customers AS c JOIN ecommerce.Orders AS o ON c.id = o.customer_id;

How do I aggregate data?

Use aggregate functions like COUNT, SUM, AVG, MIN, and MAX with GROUP BY. HAVING filters aggregated results.

Example

SELECT customer_id, SUM(total_amount) AS lifetime_value FROM ecommerce.Orders GROUP BY customer_id HAVING lifetime_value > 500;

Best practices for SELECT in BigQuery

Qualify table names with project.dataset.table to avoid accidental cross-project scans. Use partition filters to minimize read cost. Prefer previewing columns in the UI before querying large tables.

Why How to Use the SELECT Statement in BigQuery is important

How to Use the SELECT Statement in BigQuery Example Usage


-- Top 5 customers by total spend in the last year
SELECT c.id, c.name, SUM(o.total_amount) AS total_spend
FROM myproj.ecommerce.Customers AS c
JOIN myproj.ecommerce.Orders AS o
  ON c.id = o.customer_id
WHERE o.order_date BETWEEN DATE_SUB(CURRENT_DATE(), INTERVAL 1 YEAR) AND CURRENT_DATE()
GROUP BY c.id, c.name
ORDER BY total_spend DESC
LIMIT 5;

How to Use the SELECT Statement in BigQuery Syntax


SELECT [DISTINCT] column_expression [, ...]
FROM project.dataset.table AS alias
[WHERE condition]
[GROUP BY column_list]
[HAVING condition]
[ORDER BY column_list [ASC|DESC]]
[LIMIT integer]
[OFFSET integer];

-- Example with ecommerce tables
SELECT p.name, SUM(oi.quantity) AS units_sold
FROM myproj.ecommerce.Products AS p
JOIN myproj.ecommerce.OrderItems AS oi
  ON p.id = oi.product_id
GROUP BY p.name
ORDER BY units_sold DESC;

Common Mistakes

Scanning entire tables without filters: Omitting a WHERE clause on partitioned tables causes full scans and higher costs. Always filter on partition columns or use a LIMIT during exploration.
Using SELECT * in production queries: Selecting all columns increases data processed and slows queries. Specify only required columns to save cost and simplify results.