Table1 data :-
CREATE TABLE Developers_India (
dev_id INT PRIMARY KEY,
dev_name VARCHAR(50),
tech_stack VARCHAR(50)
);
INSERT INTO Developers_India (dev_id, dev_name, tech_stack)
VALUES
(101, 'Alice', 'Backend'),
(102, 'Bob', 'Frontend'),
(103, 'Charlie', 'DevOps');
Table2 data :-
CREATE TABLE Developers_USA (
dev_id INT PRIMARY KEY,
dev_name VARCHAR(50),
tech_stack VARCHAR(50)
);
INSERT INTO Developers_USA (dev_id, dev_name, tech_stack)
VALUES
(201, 'David', 'Security'),
(102, 'Bob', 'Frontend'),
(202, 'Eve', 'Backend');
UNION
UNION combines results from two or more SELECT statements and
removes duplicate rows.
That last part matters more than people realize.
When you run UNION, you’re not telling SQL: “Append table B to
table A.”
You’re telling SQL: “Create one combined result set, and ensure rows
are unique.”
That means UNION is doing two jobs:
1. Concatenation of results
2. Duplicate elimination
The simplest way to feel UNION is through your superhero example:
Table A: Iron Man, Spider-Man
Table B: Spider-Man, Thor
When you union them:
Iron Man appears once
Spider-Man appears once (duplicate removed)
Thor appears once
So UNION keeps one occurrence of duplicates.
UNION ALL
UNION ALL merges results but does not remove duplicates.
So it’s faster. Why?
Because duplicate elimination is work. SQL must compare rows and
filter them out.
So UNION ALL is your choice when:
You actually want every row preserved
Duplicates are meaningful
You care about speed and will handle uniqueness later if needed
Table A: Iron Man, Spider-Man
Table B: Spider-Man, Thor
When you UNION ALL them:
Iron Man appears once
Spider-Man appears twice (duplicates kept)
Thor appears once
So UNION ALL keeps all occurrences of duplicates.
Intersection
There is no straight forward INTERSECTION command in standard MySQL
the way UNION exists. So if you want intersection, you must build it.
And this is where SQL stops being a set-operator chapter and starts being
a thinking chapter.
The key idea:
1. Combine all values (using UNION ALL)
2. Group them
3. Count appearances
4. Anything with count > 1 is intersection
That’s not just a hack. That’s the SQL mindset: If SQL doesn’t give you a
direct command, you express the result through logic.