UNION is used for combining data from two or more tables. The following legacy SQL query will return the number of rows from the combined result set of both tables specified in the query. The output value of this query will match the sum of the number of records in both tables:

#legacySQL
SELECT COUNT(1) 
FROM [bigquery-public-data:census_bureau_usa.population_by_zip_2000], 
     [bigquery-public-data:census_bureau_usa.population_by_zip_2010]

The equivalent query for the preceding one in standard SQL is given next. This will return the record count from both the tables. The UNION ALL option used in the query will combine results from both the queries and will not look for duplicates:

#standardSQL
SELECT COUNT(1) 
FROM `bigquery-public-data.census_bureau_usa.population_by_zip_2000`
UNION ALL
SELECT COUNT(1)
FROM `bigquery-public-data.census_bureau_usa.population_by_zip_2010`

The following query in standard SQL uses UNION DISTINCT to return only the distinct rows from both the queries. Replace UNION DISTINCT with UNION ALL and see the difference in the number of rows returned by both the queries:

#standardSQL
SELECT zipcode, gender
FROM `bigquery-public-data.census_bureau_usa.population_by_zip_2000`
UNION DISTINCT
SELECT zipcode, gender
FROM `bigquery-public-data.census_bureau_usa.population_by_zip_2010`

Another way to perform UNION ALL in standard SQL is to use the table wildcard character. If the tables in UNION have similar names, then we can use wildcard to combine them. Here is an example of one such query. This query combines data from all the tables in the given dataset whose name starts with population_by_zip_:

#standardSQL
SELECT zipcode, gender
FROM `bigquery-public-data.census_bureau_usa.population_by_zip_*`

If you want to perform UNION DISTINCT using the preceding query, then add the DISTINCT clause as shown in this query:

#standardSQL
SELECT DISTINCT zipcode, gender
FROM `bigquery-public-data.census_bureau_usa.population_by_zip_*`