SQL syntax reference

Each stored table reference should use connector.dataset.table, copied from Data explorer. Examples here use fictional Lake order data. CTE names and table aliases are local names within a query and do not need the three-part prefix.

SELECT and table aliases

Choose named columns to make the result shape explicit. An alias lets you qualify columns without repeating the full table name.

SELECT o.order_id, o.customer_id, o.revenue AS order_revenue
FROM lake.uploads.orders AS o
LIMIT 50;

SELECT * requests every column. Prefer named columns when sharing queries or transferring data between connectors. Double-quote individual identifiers when needed, for example "lake"."uploads"."orders".

WHERE and comparisons

Use WHERE for row-level rules. Combine comparisons with AND and OR; parentheses make their intended grouping clear. Use IS NULL rather than = NULL.

SELECT order_id, country, revenue
FROM lake.uploads.orders
WHERE status IN ('completed', 'refunded')
  AND revenue >= 100
  AND (country = 'US' OR country IS NULL)
ORDER BY revenue DESC, order_id
LIMIT 100;

BETWEEN includes both endpoints. For date periods, prefer an inclusive start and exclusive end so that adjacent reports do not overlap.

SELECT order_id, order_date, revenue
FROM lake.uploads.orders
WHERE order_date >= DATE '2026-09-01'
  AND order_date < DATE '2026-10-01'
ORDER BY order_date, order_id
LIMIT 100;

CASE expressions

Use a CASE expression to define a business category. The first matching condition wins. Without an ELSE, unmatched rows produce null.

SELECT order_id,
       CASE WHEN revenue >= 1000 THEN 'large'
            WHEN revenue >= 100 THEN 'standard'
            ELSE 'small' END AS order_band
FROM lake.uploads.orders
LIMIT 100;

GROUP BY and HAVING

Define the result grain with GROUP BY. WHERE removes input rows; HAVING removes groups after aggregation. Group selected non-aggregate expressions explicitly.

SELECT country, COUNT(*) AS orders, SUM(revenue) AS revenue
FROM lake.uploads.orders
WHERE status = 'completed'
GROUP BY country
HAVING COUNT(*) >= 10
ORDER BY revenue DESC
LIMIT 100;

Joins

An inner join keeps matching rows; a left join also keeps unmatched rows from the left input. Qualify columns with aliases to avoid ambiguity. Check key uniqueness before summing joined data.

SELECT o.order_id, c.country, o.amount
FROM bq.sales.orders AS o
LEFT JOIN pg.public.customers AS c ON o.customer_id = c.id
LIMIT 100;

The first parts, bq and pg, identify example connectors. See cross-source querying for transfer and execution details. A filter on the right table in WHERE can remove unmatched rows from a left join; put match restrictions in ON when you need to retain them.

Common table expressions

Name intermediate results with WITH. A CTE can clarify the grain before a join or isolate source-side filtering.

WITH totals AS (
  SELECT customer_id, SUM(revenue) AS revenue
  FROM lake.uploads.orders
  WHERE status = 'completed'
  GROUP BY customer_id
)
SELECT customer_id, revenue
FROM totals
WHERE revenue > 1000
ORDER BY revenue DESC
LIMIT 100;

Recursive CTEs are not accepted. CTE names beginning with stg_ followed by digits are reserved.

Window calculations

A window calculation keeps detail rows while calculating across a partition. For a stable row number, include a tie-breaker in its ordering. Filter a calculated rank in an outer query.

WITH ranked AS (
  SELECT order_id, country, revenue,
         ROW_NUMBER() OVER (
           PARTITION BY country ORDER BY revenue DESC, order_id
         ) AS position
  FROM lake.uploads.orders
)
SELECT order_id, country, revenue
FROM ranked
WHERE position <= 3
ORDER BY country, position
LIMIT 100;

RANK leaves gaps after ties; DENSE_RANK does not. LAG and LEAD access adjacent rows in the window order. For running totals, specify the intended window frame explicitly and verify that the execution destination supports it.

Combining results

UNION ALL retains all input rows. UNION removes duplicate result rows. Match column positions and compatible types in both branches.

SELECT 'orders' AS source, order_id, revenue
FROM lake.uploads.orders
UNION ALL
SELECT 'import' AS source, order_id, revenue
FROM lake.uploads.imported_orders
LIMIT 100;

Ordering and limits

Without ORDER BY, row order is unspecified. A limit selects output rows; it is not a scan budget. Use a stable ordering when comparing repeat runs, and inspect the plan for cross-source transfer.

Read-only execution

Submit one SELECT or WITH query. Writes, schema changes, procedure calls, locking reads, and dynamic table functions are rejected. Browse schemas in Data explorer and use the workbench Explain action for plans. See compatibility for the full supported list.