Week 02: Union, Union All, Intersect, Except

SLO 04

🔁 Set Operators in SQL

Operator	Description	Duplicates	Order Requirement	Supported in SQLite
UNION	Combines results from two queries, removing duplicates	No	Yes (same number and types)	Yes
UNION ALL	Combines results from two queries, keeping duplicates	Yes	Yes	Yes
INTERSECT	Returns rows common to both queries	No	Yes	Yes
EXCEPT	Returns rows from the first query that are not in the second	No	Yes	Yes

⁉️ FAQ

Q1: What is the difference between UNION and UNION ALL?

• UNION combines results from two queries and removes duplicates.
• UNION ALL combines results from two queries and keeps duplicates.

Q2: What does the INTERSECT operator do?

• The INTERSECT operator returns rows that are common to both queries.

Q3: What does the EXCEPT operator do?

• The EXCEPT operator returns rows from the first query that are not present in the second query.

Q4: Do all set operators require the same number of columns and data types in the queries?

• Yes, all set operators require the same number of columns and compatible data types in the queries.

Q5: Are set operators supported in SQLite?

• Yes, all set operators are supported in SQLite.

Q6: Do set operators remove duplicates by default?

• Yes, UNION and INTERSECT remove duplicates by default, while UNION ALL keeps duplicates.

Q7: Can you use set operators with different data types?

• No, set operators require compatible data types in the queries.

Q8: Do set operators require the same order of columns in the queries?

• Yes, set operators require the same order of columns in the queries.

Q9: Can you use set operators with subqueries?

• Yes, you can use set operators with subqueries as long as the subqueries return compatible data types and the same number of columns.

Q10: What happens if the queries in a set operator have different numbers of columns?

• If the queries in a set operator have different numbers of columns, an error will occur.

Q11: Can you use set operators with different table names?

• Yes, you can use set operators with different table names as long as the queries return compatible data types and the same number of columns.

Q12: Can you use set operators with different column names?

• Yes, you can use set operators with different column names as long as the queries return compatible data types and the same number of columns.

Q13: Can you use set operators with different data sources?

• Yes, you can use set operators with different data sources as long as the queries return compatible data types and the same number of columns.

👩🏽‍💻 Queries References

-- Example of UNION
SELECT column1, column2 FROM table1
UNION
SELECT column1, column2 FROM table2;
-- Example of UNION ALL
SELECT column1, column2 FROM table1
UNION ALL
SELECT column1, column2 FROM table2;
-- Example of INTERSECT
SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;
-- Example of EXCEPT
SELECT column1, column2 FROM table1
EXCEPT
SELECT column1, column2 FROM table2;

⚽ Scenario: Premier League vs Champions League

We’ll create two tables:

• premier_league_teams: current Premier League teams.
• champions_league_teams: clubs currently in the UEFA Champions League.

Some Premier League teams also play in the Champions League, but not all Champions League teams are English.

🧱 Table Definitions and Sample Data

%%capture
%load_ext sql
%sql sqlite:///dbs/w02/slo2.4-union.db
%config SqlMagic.style = '_DEPRECATED_DEFAULT'

%%sql

DROP TABLE IF EXISTS premier_league_teams;
DROP TABLE IF EXISTS champions_league_teams;

CREATE TABLE premier_league_teams (
    team_name TEXT,
    jersey_color TEXT,
    founded_year INTEGER,
    city TEXT
);

CREATE TABLE champions_league_teams (
    team_name TEXT,
    jersey_color TEXT,
    founded_year INTEGER,
    city TEXT
);

 * sqlite:///dbs/w02/slo2.4-union.db
Done.
Done.
Done.
Done.

[]

⚽ Sample Inserts

%%sql
INSERT INTO premier_league_teams VALUES
('Manchester City', 'Sky Blue', 1880, 'Manchester'),
('Arsenal', 'Red and White', 1886, 'London'),
('Liverpool', 'Red', 1892, 'Liverpool'),
('Chelsea', 'Blue', 1905, 'London'),
('Manchester United', 'Red', 1878, 'Manchester'),
('Tottenham', 'White and Navy', 1882, 'London');

INSERT INTO champions_league_teams VALUES
('Real Madrid', 'White', 1902, 'Madrid'),
('Barcelona', 'Blue and Red', 1899, 'Barcelona'),
('Manchester City', 'Sky Blue', 1880, 'Manchester'),
('Bayern Munich', 'Red and White', 1900, 'Munich'),
('Paris Saint-Germain', 'Blue and Red', 1970, 'Paris'),
('Arsenal', 'Red and White', 1886, 'London'),
('Inter Milan', 'Blue and Black', 1908, 'Milan'),
('Liverpool', 'Red', 1892, 'Liverpool'),
('Liverpool', 'Red', 1892, 'Liverpool'); -- duplicate

 * sqlite:///dbs/w02/slo2.4-union.db
6 rows affected.
9 rows affected.

[]

🔍 Updated Query Examples with All Columns

1. UNION: All distinct teams with full details

SELECT * FROM premier_league_teams
UNION
SELECT * FROM champions_league_teams;

%%sql
SELECT * FROM premier_league_teams
UNION
SELECT * FROM champions_league_teams;

 * sqlite:///dbs/w02/slo2.4-union.db
Done.

team_name	jersey_color	founded_year	city
Arsenal	Red and White	1886	London
Barcelona	Blue and Red	1899	Barcelona
Bayern Munich	Red and White	1900	Munich
Chelsea	Blue	1905	London
Inter Milan	Blue and Black	1908	Milan
Liverpool	Red	1892	Liverpool
Manchester City	Sky Blue	1880	Manchester
Manchester United	Red	1878	Manchester
Paris Saint-Germain	Blue and Red	1970	Paris
Real Madrid	White	1902	Madrid
Tottenham	White and Navy	1882	London

2. INTERSECT: Full details of teams in both leagues

SELECT * FROM premier_league_teams
INTERSECT
SELECT * FROM champions_league_teams;

%%sql
SELECT * FROM premier_league_teams
INTERSECT
SELECT * FROM champions_league_teams;

 * sqlite:///dbs/w02/slo2.4-union.db
Done.

team_name	jersey_color	founded_year	city
Arsenal	Red and White	1886	London
Liverpool	Red	1892	Liverpool
Manchester City	Sky Blue	1880	Manchester

3. EXCEPT: PL teams not in the CL (full detail)

SELECT * FROM premier_league_teams
EXCEPT
SELECT * FROM champions_league_teams;

%%sql
SELECT * FROM premier_league_teams
EXCEPT
SELECT * FROM champions_league_teams;

 * sqlite:///dbs/w02/slo2.4-union.db
Done.

team_name	jersey_color	founded_year	city
Chelsea	Blue	1905	London
Manchester United	Red	1878	Manchester
Tottenham	White and Navy	1882	London

Some things for you to try out:

• Query for oldest teams only in PL using EXCEPT and ORDER BY founded_year
• Group teams by city and count how many come from each city (with GROUP BY)
• Match color styles (e.g., WHERE jersey_color LIKE ‘%Red%’)
• Join both tables on team_name to compare differences (e.g., color changes)