UNION ALL vs UNION DISTINCT SET Operators in Google Bigquery

UNION ALL 

The UNION ALL operator combines the result sets of two or more input queries. Union ALL will allow duplicate records.

SELECT * FROM Employee_Asia UNION ALL SELECT * from Employee_Europe;

UNION DISTINCT

The UNION DISTINCT operator combines the result sets of two or more input queries. Union DISTINCT will remove duplicate records

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. 

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

SELECT LastName FROM Roster
EXCEPT DISTINCT
SELECT LastName FROM PlayerStats;

nVector

posted on 22 Sep 19