SUPERCAT.DEV

SELECT col1 FROM (VALUES (1),(2),(2),(2),(3)) AS t1 (col1)
UNION
SELECT col1 FROM (VALUES (1),            (3)) AS t2 (col1)

-- OUTPUT: 1,2,3

-- ------------
SELECT col1 FROM (VALUES (1),(2),(2),(2),(3)) AS t1 (col1)
UNION ALL
SELECT col1 FROM (VALUES (1),            (3)) AS t2 (col1)

-- OUTPUT: 1,2,2,2,3,1,3

-- ------------
-- Restituisce tutti i valori distinti restituiti da entrambe le query

SELECT col1 FROM (VALUES (1),(2),(3),(4)) AS t1 (col1)
INTERSECT
SELECT col1 FROM (VALUES     (2),(3),(4),(5)) AS t2 (col1)

-- OUTPUT: 2,3,4

-- ------------
-- Restituisce tutti i valori distinti della prima query 
-- a condizione che non siano restituiti anche dalla seconda query

SELECT col1 FROM (VALUES (1),(2),(3),(4)) AS t1 (col1)
EXCEPT
SELECT col1 FROM (VALUES     (2),(3),(4),(5)) AS t2 (col1)

-- OUTPUT: 1