Wildcard tables

A wildcard table enables you to query multiple tables using concise SQL statements. A wildcard table represents a union of all the tables that match the wildcard expression.

Wildcard tables are available only in GoogleSQL. For equivalent functionality in legacy SQL, see Table wildcard functions.

For an overview of wildcard tables, see Querying multiple tables using a wildcard table.

Wildcard table syntax

Wildcard table syntax:

SELECT
FROM
  `<project-id>.<dataset-id>.<table-prefix>*`
WHERE
  bool_expression

<project-id>

Cloud Platform project ID. Optional if you use your default project ID.

<dataset-id>

BigQuery dataset ID.

<table-prefix>

A string that is common across all tables that are matched by the wildcard character. The table prefix is optional. Omitting the table prefix matches all tables in the dataset.

* (wildcard character)

The wildcard character, "*", represents one or more characters of a table name. The wildcard character can appear only as the final character of a wildcard table name.

Queries with wildcard tables support the _TABLE_SUFFIX pseudo column in the WHERE clause. This column contains the values matched by the wildcard character, so that queries can filter which tables are accessed. For example, the following WHERE clauses use comparison operators to filter the matched tables:

WHERE
  _TABLE_SUFFIX BETWEEN '29' AND '40'

WHERE
  _TABLE_SUFFIX = '1929'

WHERE
  _TABLE_SUFFIX < '1941'

For more information about the _TABLE_SUFFIX pseudo column, see Filtering selected tables using _TABLE_SUFFIX.

Enclose table names with wildcards in backticks

The wildcard table name contains the special character (*), which means that you must enclose the wildcard table name in backtick (`) characters. For example, the following query is valid because it uses backticks:

#standardSQL
/* Valid SQL query */
SELECT
  max
FROM
  `bigquery-public-data.noaa_gsod.gsod*`
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

The following query is NOT valid because it isn't properly quoted with backticks:

#standardSQL
/* Syntax error: Expected end of statement but got "-" at [4:11] */
SELECT
  max
FROM
  # missing backticks
  bigquery-public-data.noaa_gsod.gsod*
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Quotation marks do not work:

#standardSQL
/* Syntax error: Unexpected string literal: 'bigquery-public-data.noaa_gsod.gsod*' at [4:3] */
SELECT
  max
FROM
  # quotes are not backticks
  'bigquery-public-data.noaa_gsod.gsod*'
WHERE
  max != 9999.9 # code for missing data
  AND _TABLE_SUFFIX = '1929'
ORDER BY
  max DESC

Limitations

See the limitation section in Querying multiple tables using a wildcard table.