Union, intersection, difference in DBMS Theory - Full Explanation

Introduction

When working with sets of data, it is common to combine or compare them to find shared or unique elements. Understanding how to merge, find common parts, or differences between data sets helps organize and analyze information effectively.

Explanation

Union

Union combines all unique elements from two sets into one set. It includes every item that appears in either set, but without duplicates. This operation helps gather all possible values from both sources.

Union creates a set containing all distinct elements from both sets.

Intersection

Intersection finds only the elements that appear in both sets. It filters out anything that is not common to both, showing the shared data. This is useful to identify overlap or commonality between two groups.

Intersection returns only the elements present in both sets.

Difference

Difference takes one set and removes any elements that also appear in the second set. It shows what is unique to the first set by excluding shared items. This helps highlight what is exclusive to one group compared to another.

Difference shows elements in the first set that are not in the second set.

Real World Analogy

Imagine two friends each have a collection of books. Union is like putting all their books together on one shelf without repeating any title. Intersection is finding the books both friends own. Difference is seeing which books one friend has that the other does not.

Union → Combining all books from both friends without duplicates on one shelf

Intersection → Books that both friends have in their collections

Difference → Books that only one friend owns and the other does not

Diagram

Diagram

      ┌─────────────┐      ┌─────────────┐
      │   Set A     │      │   Set B     │
      │  {1, 2, 3}  │      │  {2, 3, 4}  │
      └─────┬───────┘      └─────┬───────┘
            │                    │
            │                    │
            │                    │
            │                    │
            ▼                    ▼
  ┌───────────────────────────────┐
  │ Union: {1, 2, 3, 4}           │
  │ Intersection: {2, 3}           │
  │ Difference (A - B): {1}        │
  └───────────────────────────────┘

This diagram shows two sets A and B with their elements and the results of union, intersection, and difference operations.

Key Facts

Union → Combines all unique elements from two sets into one set.

Intersection → Finds elements common to both sets.

Difference → Shows elements in the first set that are not in the second set.

Set → A collection of distinct elements with no duplicates.

Code Example

DBMS Theory

set_a = {1, 2, 3}
set_b = {2, 3, 4}

union = set_a.union(set_b)
intersection = set_a.intersection(set_b)
difference = set_a.difference(set_b)

print("Union:", union)
print("Intersection:", intersection)
print("Difference (A - B):", difference)

Common Confusions

Union includes duplicates from both sets.

Union includes duplicates from both sets. Union only includes unique elements; duplicates are removed.

Difference is symmetric between two sets.

Difference is symmetric between two sets. Difference is not symmetric; A - B is different from B - A.

Intersection returns all elements from both sets.

Intersection returns all elements from both sets. Intersection returns only elements present in both sets, not all elements.

Summary

Union merges two sets to include all unique elements from both.

Intersection finds only the elements shared by both sets.

Difference shows what is unique to the first set by removing elements found in the second.

Practice

1. Which of the following best describes the union operation in databases?

easy

A. Finds only the common records between two tables

B. Combines all unique records from two tables

C. Returns records from the first table that are not in the second

D. Deletes duplicate records from a table

Solution

1. Step 1: Understand the union operation

   The union operation combines all unique records from two sets or tables, removing duplicates.

2. Step 2: Compare with other operations

   Intersection finds common records, difference finds records in one set but not the other, and deletion removes records.

3. Final Answer:

   Combines all unique records from two tables -> Option B

4. Quick Check:

   Union = Combine unique records [OK]

Hint: Union = all unique items combined [OK]

Common Mistakes:

• Confusing union with intersection
• Thinking union includes duplicates
• Mixing difference with union

2. Which SQL keyword is used to find common records between two tables?

easy

A. UNION

B. DIFFERENCE

C. JOIN

D. INTERSECT

Solution

1. Step 1: Identify the keyword for common records

   The SQL keyword INTERSECT returns only the records common to both tables.

2. Step 2: Differentiate from other keywords

   UNION combines unique records, DIFFERENCE is not a standard SQL keyword, and JOIN combines rows based on a condition.

3. Final Answer:

   INTERSECT -> Option D

4. Quick Check:

   Common records = INTERSECT [OK]

Hint: Common records use INTERSECT keyword [OK]

Common Mistakes:

• Using UNION instead of INTERSECT
• Confusing JOIN with INTERSECT
• Assuming DIFFERENCE is a valid SQL keyword

3. Given two sets A = {1, 2, 3, 4} and B = {3, 4, 5, 6}, what is the result of A INTERSECT B?

medium

A. {3, 4}

B. {1, 2, 3, 4, 5, 6}

C. {1, 2}

D. {5, 6}

Solution

1. Step 1: Identify common elements in A and B

   The common elements between A and B are 3 and 4.

2. Step 2: Understand INTERSECT operation

   INTERSECT returns only the common elements, so the result is {3, 4}.

3. Final Answer:

   {3, 4} -> Option A

4. Quick Check:

   Intersection = common elements [OK]

Hint: Intersection = only common items [OK]

Common Mistakes:

• Listing all elements instead of common ones
• Confusing intersection with union
• Including elements only in one set

4. Consider the SQL query:
SELECT * FROM Table1
EXCEPT
SELECT * FROM Table2;
What is the purpose of the EXCEPT keyword?

medium

A. Returns records in Table1 but not in Table2

B. Returns records in Table2 but not in Table1

C. Returns records common to both tables

D. Returns all records from both tables

Solution

1. Step 1: Understand the EXCEPT keyword

   EXCEPT returns records from the first query that are not present in the second query.

2. Step 2: Apply to given tables

   So, it returns records in Table1 but not in Table2.

3. Final Answer:

   Returns records in Table1 but not in Table2 -> Option A

4. Quick Check:

   Difference = records in first not in second [OK]

Hint: EXCEPT = first table minus second table [OK]

Common Mistakes:

• Confusing EXCEPT with UNION
• Thinking EXCEPT returns common records
• Mixing order of tables in EXCEPT

5. You have two tables:
Employees_A with IDs {101, 102, 103, 104} and Employees_B with IDs {103, 104, 105, 106}.
You want to find IDs that are in either table but not in both. Which SQL operation gives this result?

hard

A. FULL OUTER JOIN with WHERE condition

B. INTERSECT

C. SYMMETRIC DIFFERENCE using UNION and EXCEPT

D. UNION

Solution

1. Step 1: Understand the requirement

   We want IDs in either table but not in both, which is called symmetric difference.

2. Step 2: Identify SQL approach

   SQL does not have a direct symmetric difference keyword, but it can be done by combining UNION and EXCEPT operations.

3. Step 3: Explain why other options are incorrect

   UNION returns all unique IDs including common ones, INTERSECT returns only common IDs, FULL OUTER JOIN with WHERE can be complex but not direct symmetric difference.

4. Final Answer:

   SYMMETRIC DIFFERENCE using UNION and EXCEPT -> Option C

5. Quick Check:

   Symmetric difference = union minus intersection [OK]

Hint: Symmetric difference = union minus intersection [OK]

Common Mistakes:

• Using UNION alone includes common IDs
• Using INTERSECT returns only common IDs
• Assuming FULL OUTER JOIN alone solves it