Learning Google BigQuery

UNION,  UNION ALL, and UNION DISTINCT

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:

#legacySQLSELECT 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:

#standardSQLSELECT COUNT(1) ...