Chapter 37: PostgreSQL UNION Operator
Postgre UNION Operator (and its close cousins: UNION ALL, INTERSECT, EXCEPT).
Grab your chai — it’s February 14, 2026, around 12:15 PM in Hyderabad ☕📊 Today we’re going to treat UNION like we’re combining two shopping lists or two class attendance sheets.
1. What does UNION actually do? (Teacher’s plain-English explanation)
UNION takes the result of two or more SELECT queries and combines their rows into one single result set.
The most important rules (PostgreSQL 18 – 2026 reality):
- The number of columns in each SELECT must be the same
- The data types of corresponding columns must be compatible (can be implicitly cast)
- Column names are taken from the first SELECT (others are ignored)
- By default → UNION removes duplicate rows (it does an implicit DISTINCT)
- UNION ALL → keeps all rows, even duplicates (much faster & usually what you want)
Think of it like:
- Query 1 = list of students from Hyderabad campus
- Query 2 = list of students from Secunderabad campus
- UNION = “give me all unique students from both campuses”
2. Basic syntax patterns (the shapes you will write 95% of the time)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
-- Classic UNION (removes duplicates) SELECT column1, column2, … FROM tableA WHERE … UNION SELECT column1, column2, … FROM tableB WHERE …; -- Most common in real apps – keep duplicates SELECT … FROM … UNION ALL SELECT … FROM …; -- With ORDER BY & LIMIT (very important – applies to the final result) SELECT … FROM … UNION ALL SELECT … FROM … ORDER BY column1 LIMIT 100; |
3. Real, practical examples (using our college & bookstore schemas)
Example 1: Simple UNION – combine two similar lists
Imagine we have two separate tables (maybe legacy data):
hyderabad_students
| name | gpa | |
|---|---|---|
| Aarav Patel | aarav@hyd.edu | 8.95 |
| Priya Reddy | priya@gmail.com | 9.60 |
| Sneha Kumar | sneha@gmail.com | 9.20 |
secunderabad_students
| name | gpa | |
|---|---|---|
| Rahul Sharma | rahul@yahoo.com | 7.85 |
| Vikram Naik | vikram@hyd.edu | 8.40 |
| Priya Reddy | priya@gmail.com | 9.60 |
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT name, email, gpa FROM hyderabad_students UNION SELECT name, email, gpa FROM secunderabad_students ORDER BY name; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
name | email | gpa ---------------|------------------------|------ Aarav Patel | aarav@hyd.edu | 8.95 Priya Reddy | priya@gmail.com | 9.60 ← only once Rahul Sharma | rahul@yahoo.com | 7.85 Sneha Kumar | sneha@gmail.com | 9.20 Vikram Naik | vikram@hyd.edu | 8.40 |
→ Priya appears only once (duplicate removed)
Example 2: UNION ALL – keep all rows (usually what you want)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 |
SELECT name, email, gpa, 'Hyderabad' AS campus FROM hyderabad_students UNION ALL SELECT name, email, gpa, 'Secunderabad' AS campus FROM secunderabad_students ORDER BY name; |
Result:
text
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 |
name | email | gpa | campus ---------------|------------------------|------|------------ Aarav Patel | aarav@hyd.edu | 8.95 | Hyderabad Priya Reddy | priya@gmail.com | 9.60 | Hyderabad Priya Reddy | priya@gmail.com | 9.60 | Secunderabad ← kept! Rahul Sharma | rahul@yahoo.com | 7.85 | Secunderabad Sneha Kumar | sneha@gmail.com | 9.20 | Hyderabad Vikram Naik | vikram@hyd.edu | 8.40 | Secunderabad |
→ Much faster than UNION (no deduplication step)
Example 3: Real-world pattern – combine recent & archived data
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
-- Active students (2025–2026) SELECT first_name || ' ' || last_name AS full_name, gpa, 'Active' AS status FROM students WHERE enrollment_year >= 2025 UNION ALL -- Archived / graduated students (older table) SELECT full_name, gpa, 'Graduated' AS status FROM alumni WHERE graduation_year < 2025 ORDER BY full_name; |
→ One unified list of all students ever — very common in reporting
Example 4: UNION with different sources (very powerful)
SQL
|
0 1 2 3 4 5 6 7 8 9 10 11 12 |
-- All possible email addresses from multiple places SELECT email FROM students UNION SELECT email FROM teachers UNION SELECT contact_email FROM parents ORDER BY email; |
→ Gives unique list of all known emails
4. Very important rules & gotchas (2026 PostgreSQL reality)
| Rule / Trap | What happens if you break it | Fix / Best practice |
|---|---|---|
| Different number of columns | ERROR: each UNION query must have same number… | Make sure every SELECT has same # of columns |
| Incompatible data types | ERROR: cannot cast type … | Cast explicitly: ::text, ::numeric, etc. |
| Column names come only from first query | Second query column names ignored | Use same names or AS in first SELECT |
| UNION removes duplicates (slow) | Slower than UNION ALL on large data | Use UNION ALL unless you really need deduplication |
| ORDER BY / LIMIT applies to final result | Can’t ORDER BY inside each SELECT | Put ORDER BY / LIMIT at the very end |
| NULL handling | NULL = NULL is true for deduplication | No special fix needed — behaves correctly |
5. Quick cheat-sheet (keep in your notes)
| Goal | Recommended syntax | When to choose over others |
|---|---|---|
| Combine two similar tables (keep duplicates) | … UNION ALL … | Almost always (faster, safer) |
| Get unique rows from multiple sources | … UNION … | When deduplication is the goal |
| Add a source column | SELECT …, ‘SourceA’ AS source UNION ALL SELECT … | Reporting / auditing |
| Combine with ORDER BY | … UNION ALL … ORDER BY column LIMIT 100 | Final result sorting |
Your mini homework right now
- Write UNION ALL to combine students from two imaginary campuses
- Add a column campus so you know where each student came from
- Change it to UNION → see Priya disappear if duplicated
- Write a UNION query that lists all unique emails from students + teachers (imagine a teachers table)
Next class?
Tell me:
- Want UNION ALL vs UNION performance comparison?
- INTERSECT and EXCEPT (set difference & intersection)?
- How to do UNION of more than two queries?
- Or move to subqueries, EXISTS, CTE, WINDOW functions?
Your teacher is ready — what’s the next topic? 🚀
