Skip to main content

19. UNION & UNION ALL -- Combining Result Sets

Level: Beginner

ℹ️ What You'll Learn
  • UNION syntax -- combine two SELECT queries
  • UNION vs UNION ALL (duplicates)
  • Matching column count and types
  • UNION with ORDER BY
  • UNION vs JOIN difference
  • Stack results vertically
  • Common UNION mistakes
  • UNION performance

JOINs connect tables horizontally (add columns). UNION stacks results vertically (add rows). This article teaches combining query results.

Why UNION Matters for Backend Developers​

APIs need combined data from different sources:

Frontend asks: "Get all people in school (students + teachers + staff)"
    v
Backend needs: Combine Student + Teacher + Staff tables
    v
UNION: Stack rows from 3 tables into 1 result
    v
Single result set with all people

UNION solves "combine multiple queries" problems.

UNION Syntax​

SELECT Column1, Column2 FROM Table1
UNION
SELECT Column1, Column2 FROM Table2;

Rules:

  • Same number of columns in both queries
  • Matching column types
  • UNION removes duplicates
  • ORDER BY applies to final result

Example 1: UNION All Students and Teachers​

Combine students and teachers:

SELECT 
    s.Name AS PersonName,
    'Student' AS PersonType
FROM Student s

UNION

SELECT 
    t.Name AS PersonName,
    'Teacher' AS PersonType
FROM Teacher t;

Result (students and teachers):

PersonName   | PersonType
Ravi Kumar   | Student
Priya Sharma | Student
Dr. Mehta    | Teacher
Mrs. Rao     | Teacher

Stacks: All students (2 rows) + All teachers (2 rows) = 4 rows. No duplicates.

Example 2: UNION vs UNION ALL (duplicates)​

Get students or teachers named "Kumar":

SELECT Name FROM Student WHERE Name LIKE '%Kumar%'
UNION
SELECT Name FROM Student WHERE Name LIKE '%Sharma%';

Result (UNION removes duplicates):

Name
Ravi Kumar
Priya Sharma

Same table queried twice. UNION shows each name once.

With UNION ALL:

SELECT Name FROM Student WHERE Name LIKE '%Kumar%'
UNION ALL
SELECT Name FROM Student WHERE Name LIKE '%Sharma%';

Result (all rows):

Name
Ravi Kumar
Priya Sharma

Same count (no duplicates in this case). But if "Ravi Kumar" matched both conditions, UNION ALL shows both rows.

Example 3: Combine Students from Multiple Classes​

Get top 3 scorers from class 10-A and 10-B:

SELECT 
    s.Name AS Student,
    s.ClassName,
    r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
WHERE s.ClassName = '10-A'
ORDER BY r.MarksObtained DESC

UNION

SELECT 
    s.Name AS Student,
    s.ClassName,
    r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
WHERE s.ClassName = '10-B'
ORDER BY r.MarksObtained DESC;

Result (top scorers from both classes):

Student     | ClassName | MarksObtained
Arjun Reddy | 10-B      | 92
Priya Sharma| 10-A      | 90
Ravi Kumar  | 10-A      | 88

Combines two separate filtered queries. Removes duplicate student records.

Example 4: All People Report (Students, Teachers, Staff)​

Get all people with roles:

SELECT 
    s.Name,
    'Student' AS Role,
    s.ClassName AS Details
FROM Student s

UNION

SELECT 
    t.Name,
    'Teacher' AS Role,
    t.Qualification AS Details
FROM Teacher t

UNION

SELECT 
    st.Name,
    'Staff' AS Role,
    st.Designation AS Details
FROM Staff st
ORDER BY Name;

Result (all school people):

Name        | Role    | Details
Arjun Reddy | Student | 10-B
Dr. Mehta   | Teacher | M.Sc. Physics
Kiran Rao   | Staff   | Librarian
Mrs. Rao    | Teacher | B.A. English
Priya Sharma| Student | 10-A

Stacks 3 different tables. Shows all with consistent columns.

Example 5: UNION to Find Unmatched (alternative to FULL OUTER)​

Get unassigned teachers AND subjects:

SELECT 
    t.Name AS Teacher,
    NULL AS Subject
FROM Teacher t
WHERE NOT EXISTS (
    SELECT 1 FROM Subject WHERE TeacherId = t.TeacherId
)

UNION

SELECT 
    NULL AS Teacher,
    s.Name AS Subject
FROM Subject s
WHERE NOT EXISTS (
    SELECT 1 FROM Teacher WHERE TeacherId = s.TeacherId
);

Result (unmatched):

Teacher    | Subject
Mr. Sharma | NULL
NULL       | Hindi

UNION shows unmatched from both sides.

Example 6: Combine Different Exam Types​

Get all exam results across exam types:

SELECT 
    s.Name AS Student,
    'Unit Test' AS ExamType,
    r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId
WHERE e.ExamType = 'UnitTest'

UNION ALL

SELECT 
    s.Name AS Student,
    'Final' AS ExamType,
    r.MarksObtained
FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId
INNER JOIN Exam e ON r.ExamId = e.ExamId
WHERE e.ExamType = 'Final'
ORDER BY ExamType, MarksObtained DESC;

Result (all exam results):

Student     | ExamType   | MarksObtained
Ravi Kumar  | Final      | 92
Priya Sharma| Final      | 88
Arjun Reddy | Unit Test  | 85

UNION ALL keeps duplicates (same student in both exam types).

UNION vs JOIN Comparison​

UNION (vertically stacks):

SELECT StudentId, Name FROM Student
UNION
SELECT TeacherId, Name FROM Teacher;
-- Combines rows from 2 tables (different schemas, but same columns)

JOIN (horizontally combines):

SELECT s.Name, r.MarksObtained FROM Student s
INNER JOIN ExamResult r ON s.StudentId = r.StudentId;
-- Adds columns from 2 tables

When use UNION:

  • Combining same structure, different tables (students + teachers)
  • Stacking filtered results
  • Unmatched rows from both sides

When use JOIN:

  • Getting related data from multiple tables
  • Adding detail columns
  • Filtering by relationships

Common UNION Mistakes​

Mistake 1: Column count mismatch

Wrong:

SELECT Name, ClassName FROM Student
UNION
SELECT Name FROM Teacher;
-- ERROR: 2 columns vs 1 column

Result: Column count mismatch error.

Fix: Match column count:

SELECT Name, ClassName FROM Student
UNION
SELECT Name, Qualification FROM Teacher;

Mistake 2: Column type mismatch

Wrong:

SELECT Name, ClassId FROM Student
UNION
SELECT Name, Qualification FROM Teacher;
-- ClassId is INT, Qualification is VARCHAR

Result: Type conversion error or wrong data.

Fix: Cast to matching type:

SELECT Name, CAST(ClassId AS VARCHAR) FROM Student
UNION
SELECT Name, Qualification FROM Teacher;

Mistake 3: Forgetting duplicates with UNION

Wrong:

SELECT StudentId FROM ExamResult WHERE MarksObtained > 80
UNION
SELECT StudentId FROM ExamResult WHERE MarksObtained > 75;
-- Student 1: 92 marks appears in both results
-- UNION removes duplicate
-- Gets 5 unique students, loses duplicate

Result: Unexpected row count (fewer rows than expected).

Fix: Use UNION ALL if duplicates needed:

SELECT StudentId FROM ExamResult WHERE MarksObtained > 80
UNION ALL
SELECT StudentId FROM ExamResult WHERE MarksObtained > 75;
-- Both occurrences kept

Mistake 4: ORDER BY only on last query

Wrong:

SELECT Name FROM Student ORDER BY Name
UNION
SELECT Name FROM Teacher;
-- ORDER BY applies to Student only

Result: Unpredictable final order.

Fix: ORDER BY after UNION:

SELECT Name FROM Student
UNION
SELECT Name FROM Teacher
ORDER BY Name;

Mistake 5: Performance -- UNION with large tables

Wrong:

SELECT * FROM Student
UNION
SELECT * FROM Teacher;
-- Both tables have 1M rows
-- UNION removes duplicates = scan + sort both = slow

Result: Slow query, high CPU.

Fix: Use UNION ALL if duplicates acceptable:

SELECT * FROM Student
UNION ALL
SELECT * FROM Teacher;
-- Skip deduplication step, faster

Best Practices for UNION​

  1. Match column count and types -- Exactly
  2. Use UNION ALL for performance -- If duplicates acceptable
  3. ORDER BY after UNION -- Applies to final result
  4. Use meaningful column names -- First SELECT determines names
  5. **Avoid UNION with SELECT * ** -- Explicit columns clearer
  6. Test row counts -- Verify expected duplicates removed/kept
  7. Consider derived table for complex logic -- If many UNIONs, use subquery
🎯 Q1: What's the difference between UNION and UNION ALL?

UNION: Removes duplicate rows.

SELECT Name FROM Student
UNION
SELECT Name FROM Teacher;
-- If 'Kumar' appears in both, shown once

UNION ALL: Keeps all rows including duplicates.

SELECT Name FROM Student
UNION ALL
SELECT Name FROM Teacher;
-- If 'Kumar' appears in both, shown twice

Performance: UNION ALL faster (skip deduplication).

🎯 Q2: Can I UNION tables with different structures?

Technically yes, but columns must match:

SELECT StudentId AS PersonId, Name FROM Student
UNION
SELECT TeacherId AS PersonId, Name FROM Teacher;

Different tables (Student vs Teacher), but SELECT returns same columns (PersonId, Name).

Rule: Column count and types match. Names from first SELECT.

🎯 Q3: When should I use UNION vs JOIN?

UNION: Stack rows (different queries, same structure).

-- All people report
SELECT Name, 'Student' FROM Student
UNION
SELECT Name, 'Teacher' FROM Teacher;

JOIN: Add columns from related tables.

-- Student with exam results
SELECT s.Name, r.MarksObtained FROM Student s
JOIN ExamResult r ON s.StudentId = r.StudentId;

Use UNION for vertical stacking, JOIN for horizontal combining.

🎯 Q4: Does UNION remove duplicates from within same table?

No, duplicates within query shown:

SELECT StudentId FROM ExamResult WHERE MarksObtained > 80;
-- Same student appears multiple times (multiple exams)
-- All rows shown (no deduplication within query)

UNION removes between queries:

SELECT StudentId FROM ExamResult WHERE MarksObtained > 80
UNION
SELECT StudentId FROM ExamResult WHERE MarksObtained > 75;
-- Student appearing in both conditions shown once

UNION deduplicates across queries only.

🎯 Q5: Can I ORDER BY in UNION?

Not per query:

SELECT Name FROM Student ORDER BY Name  -- Wrong: ORDER BY not allowed here
UNION
SELECT Name FROM Teacher;

Order final result only:

SELECT Name FROM Student
UNION
SELECT Name FROM Teacher
ORDER BY Name;

ORDER BY after UNION applies to combined result.

🎯 Q6: When would I use UNION to find unmatched records?

Find teachers without subjects AND subjects without teachers:

SELECT t.Name, NULL FROM Teacher t
WHERE NOT EXISTS (SELECT 1 FROM Subject WHERE TeacherId = t.TeacherId)
UNION
SELECT NULL, s.Name FROM Subject s
WHERE NOT EXISTS (SELECT 1 FROM Teacher WHERE TeacherId = s.TeacherId);

Alternative to FULL OUTER JOIN. Clearer intent sometimes.

🤖Use AI to Learn Faster
⚠️ Important for beginners: Do NOT use AI to write your code yet. Type every example yourself. Your brain learns by doing, not by reading AI output. Use AI only to explain and quiz you — not to code for you. Once you have strong fundamentals, AI becomes a powerful productivity tool for repetitive tasks.

Use ChatGPT, Claude, or Copilot to go deeper on UNION and UNION ALL in SQL Server. Try these prompts:

  • "Explain UNION: combine students and teachers into one list"
  • "What's the difference between UNION and UNION ALL?"
  • "Show UNION with 3 tables: students, teachers, staff"
  • "How do I use UNION to find unmatched records?"
  • "Quiz me: ask 5 questions about UNION, column matching, duplicates"

💡 Tip: After reading this article, paste your own code into AI and ask "What could go wrong here and why?" — fastest way to find edge cases and deepen understanding.

Next Article​

20. GROUP BY & HAVING -- Grouping and Filtering Aggregates ->

nexcoding.in
nexcoding.in  ·  Free .NET Tutorials  ·  Sahasra Technologies  ·  info@nexcoding.in  ·  +91-9951510727