22 Sep 19 · npack ·       Add to Favorites  

UNION, UNION DISTINCT in Google Bigquery - Syntax

Set operators:

  • For UNION ALL, R appears exactly m + n times in the result.
  • For UNION DISTINCT, the DISTINCT is computed after the UNION is computed, so R appears exactly one time.
  • For INTERSECT DISTINCT, the DISTINCT is computed after the result above is computed.
  • For EXCEPT DISTINCT, row R appears once in the output if m > 0 and n = 0.
  • If there are more than two input queries, the above operations generalize and the output is the same as if the inputs were combined incrementally from left to right.

UNION

The UNION operator combines the result sets of two or more input queries by pairing columns from the result set of each query and vertically concatenating them

SELECT * FROM Employee_Asia UNION SELECT * from Employee_Europe;
SELECT * FROM Employee_Asia UNION DISTINCT SELECT * from Employee_Europe;

INTERSECT

The INTERSECT operator returns rows that are found in the result sets of both the left and right input queries. Unlike EXCEPT, the positioning of the input queries (to the left vs. right of the INTERSECT operator) does not matter

This query returns the last names that are present in both Roster and PlayerStats.

SELECT LastName FROM Roster
INTERSECT DISTINCT
SELECT LastName FROM PlayerStats;

EXCEPT

The EXCEPT operator returns rows from the left input query that are not present in the right input query

The query below returns last names in Roster that are not present in PlayerStats.

SELECT LastName FROM Roster
EXCEPT DISTINCT
SELECT LastName FROM PlayerStats;

npack

posted on 22 Sep 19

Enjoy great content like this and a lot more !

Signup for a free account to write a post / comment / upvote posts. Its simple and takes less than 5 seconds

Copied