🔗 JOINS AND SET OPERATIONS (SQL)

Real databases almost NEVER keep all data in one giant table — instead, related data is split across MULTIPLE tables (like Employees in one table, Departments in another). This chapter teaches you how to combine tables (JOINS) and combine query results (SET Operations) to get the full picture.

8.1 🌟 Introduction

graph LR
    JOIN["🔗 JOIN\nCombines TABLES\n(columns from 2+ tables\ninto one result)"]
    SET["🧮 SET Operations\nCombines QUERY RESULTS\n(rows from 2+ SELECTs\ninto one result)"]

    style JOIN fill:#2196F3,color:#fff
    style SET fill:#9C27B0,color:#fff

Analogy: Imagine your school keeps Student Names in one register and Marks in another register — both linked by Roll Number. A JOIN is like laying both registers side-by-side and matching rows by Roll Number. A SET Operation is like combining TWO different class lists (e.g., "Sports Team" list and "Music Club" list) to see who's in either, both, or only one!

Our Two Sample Tables — used throughout this entire chapter:

Table: EMP

Table: DEPT

Notice: deptno = 40 (IT) exists in DEPT but has NO employees in EMP. Keep this in mind — it becomes important later!

8.2 🔗 Joining Tables

8.2.1 Unrestricted Join (Cartesian Product) 🌐

The simplest (and messiest!) join — combine EVERY row of Table 1 with EVERY row of Table 2, with NO condition at all.

-- cartesian_product.sql
SELECT empno, ename, dname
FROM emp, dept;

Output (Partial — full output has 5 × 4 = 20 rows!):

empno   ename   dname
1       Alam    Sales
1       Alam    HR
1       Alam    Production
1       Alam    IT
2       Srijeeta Sales
...     ...     ...      (20 rows total)

graph LR
    E["EMP\n5 rows"]
    D["DEPT\n4 rows"]
    CP["Cartesian Product\n5 × 4 = 20 rows\n(Every combination!)"]

    E --> CP
    D --> CP

    style CP fill:#F44336,color:#fff

8.2.2 Restricted Join (Join) 🔐

Add a WHERE condition to link the tables MEANINGFULLY — only rows that actually MATCH are shown.

-- restricted_join.sql
SELECT empno, ename, dname
FROM emp, dept
WHERE emp.deptno = dept.deptno;

Output:

empno   ename       dname
1       Alam        Sales
2       Srijeeta    HR
3       Bhaskar     Production
4       Emely       Sales
5       Freddy      Production

Notice: only 5 rows now (not 20!) — because we ONLY kept rows where emp.deptno matches dept.deptno. Also notice: 'IT' (deptno 40) is MISSING — because no employee works in IT!

graph LR
    E["EMP Table"]
    D["DEPT Table"]
    MATCH["WHERE emp.deptno = dept.deptno\n🔗 Only MATCHING rows kept!"]
    RESULT["✅ Meaningful Result\n(5 rows, IT excluded)"]

    E --> MATCH
    D --> MATCH
    MATCH --> RESULT

    style MATCH fill:#4CAF50,color:#fff

8.2.3 Using Table Aliases 🏷️

An Alias is a short, temporary nickname for a table — makes long queries shorter and clearer.

-- table_aliases.sql
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

Output:

ename       dname
Alam        Sales
Srijeeta    HR
Bhaskar     Production
Emely       Sales
Freddy      Production

e is now the nickname for emp, and d is the nickname for dept. Instead of typing emp.deptno, we just type e.deptno — shorter and cleaner!

8.2.4 Additional Search Conditions in Joins 🎯

You can combine the JOIN condition with EXTRA filter conditions using AND.

-- additional_conditions.sql
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.dname = 'HR';

Output:

ename       dname
Srijeeta    HR

The Logic:

WHERE e.deptno = d.deptno    ← Join condition (links the two tables)
AND d.dname = 'HR'           ← Filter condition (narrows the result)

SELECT e.ename, e.salary
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND d.dname = 'Production';

8.2.5 Joining More Than Two Tables 🔗🔗🔗

You're NOT limited to joining just 2 tables — you can join 3, 4, or even more, as long as each pair has a linking condition!

Let's add a third table to demonstrate:

Table: PROJECT

-- joining_three_tables.sql
SELECT e.ename, d.dname, p.projname
FROM emp e, dept d, project p
WHERE e.deptno = d.deptno
AND d.deptno = p.deptno;

Output:

ename       dname         projname
Alam        Sales         Website Redesign
Srijeeta    HR            Payroll System
Bhaskar     Production    Factory Automation
Emely       Sales         Website Redesign
Freddy      Production    Factory Automation

graph LR
    EMP["📋 EMP"]
    DEPT["📋 DEPT"]
    PROJ["📋 PROJECT"]
    R["📊 Combined Result\n(Employee + Dept + Project\nall in ONE row!)"]

    EMP -->|"e.deptno = d.deptno"| R
    DEPT -->|"d.deptno = p.deptno"| R
    PROJ --> R

    style R fill:#4CAF50,color:#fff

Key Rule: When joining 3+ tables, you need (n-1) join conditions for n tables. Joining 3 tables needs 2 conditions; joining 4 tables needs 3 conditions — all connected with AND!

8.2.6 Equi Join ⚖️

An Equi Join uses the equality operator (=) to match rows — and it's called "Equi" because of that = sign! When you use SELECT *, the common column appears TWICE (once from each table).

-- equi_join.sql
SELECT *
FROM emp, dept
WHERE emp.deptno = dept.deptno;

Output (Partial):

empno  ename      deptno  salary   deptno  dname   dhead
1      Alam       10      10300    10      Sales   Ritika
2      Srijeeta   20      6220     20      HR      Ankit

Notice: deptno appears TWICE — once from EMP, once from DEPT! This is the defining feature of an Equi Join with SELECT *.

8.2.7 Non-Equi Joins 🚫

A Non-Equi Join uses any operator OTHER than = — like >, <, >=, <=, or != — to link or filter rows.

-- non_equi_join.sql
SELECT e.ename, e.salary
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.salary > 15000;

Output:

ename    salary
Emely    20500

The JOIN condition itself (e.deptno = d.deptno) is still an Equi condition — but the ADDITIONAL condition (e.salary > 15000) uses >, making this a Non-Equi filter combined with the join!

8.2.8 Natural Join 🌿

A Natural Join automatically joins tables based on columns having the SAME NAME — no need to write the condition yourself! And unlike Equi Join, the common column appears only ONCE.

-- natural_join.sql
SELECT ename, salary, dname
FROM emp NATURAL JOIN dept;

Output:

ename       salary   dname
Alam        10300    Sales
Srijeeta    6220     HR
Bhaskar     11320    Production
Emely       20500    Sales
Freddy      11320    Production

MySQL automatically found deptno as the common column name in BOTH tables and joined on it — no WHERE needed!

Equi Join vs Natural Join — The Critical Comparison:

8.2.9 Joining Tables using JOIN Clause of SQL SELECT 🆕

The MODERN, cleaner way to write joins — using the explicit JOIN ... ON syntax instead of comma + WHERE.

-- modern_join_syntax.sql
SELECT ename, dname
FROM emp JOIN dept
ON emp.deptno = dept.deptno;

Output:

ename       dname
Alam        Sales
Srijeeta    HR
Bhaskar     Production
Emely       Sales
Freddy      Production

This produces the EXACT SAME result as the traditional comma + WHERE method — just written differently. Both forms are accepted.

Old Style vs New Style — Same Result, Different Syntax:

-- Old (traditional) style:
SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

-- New (modern) style:
SELECT e.ename, d.dname
FROM emp e JOIN dept d
ON e.deptno = d.deptno;

8.3 🧮 Performing SET Operations on Relations

SET Operations combine the RESULTS of two or more SELECT queries — treating them like mathematical sets!

graph TD
    Q1["Query 1: SELECT deptno FROM emp"]
    Q2["Query 2: SELECT deptno FROM dept"]
    RULE["Both must have:\nSame # of columns\nSame data types\nSame order"]

    Q1 --> RULE
    Q2 --> RULE

    style RULE fill:#FF9800,color:#fff

8.3.1 UNION 🔗

UNION combines the results of TWO queries into ONE list, automatically REMOVING duplicate rows.

-- union_demo.sql
SELECT deptno FROM emp
UNION
SELECT deptno FROM dept;

Output:

deptno
10
20
30
40

Why only 4 rows? Let's see what each query alone returns:

emp.deptno:  10, 20, 30, 10, 30    (5 rows, WITH duplicates)
dept.deptno: 10, 20, 30, 40         (4 rows)

UNION merges both AND removes duplicates:
→ 10, 20, 30, 40  (each value appears ONLY ONCE!)

graph LR
    A["emp.deptno:\n10,20,30,10,30"]
    B["dept.deptno:\n10,20,30,40"]
    U["UNION\n(merge + remove duplicates)"]
    R["Result:\n10, 20, 30, 40"]

    A --> U
    B --> U
    U --> R

    style U fill:#4CAF50,color:#fff

8.3.2 MINUS Operator ➖

MINUS shows records that are in the FIRST query's result but NOT in the second query's result — like subtraction!

-- minus_demo.sql
SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

Output:

deptno
40

Why just 40?

dept.deptno: 10, 20, 30, 40    (4 unique values)
emp.deptno:  10, 20, 30         (these appear in emp)

MINUS = "In dept but NOT in emp"
→ Only 40 (IT) remains — because IT has no employees!

graph LR
    D["DEPT deptnos:\n10, 20, 30, 40"]
    E["EMP deptnos:\n10, 20, 30"]
    M["MINUS\n(dept - emp)"]
    R["Result: 40\n(IT — no employees!)"]

    D --> M
    E --> M
    M --> R

    style M fill:#F44336,color:#fff
    style R fill:#FF9800,color:#fff

8.3.3 INTERSECT Operator ✂️

INTERSECT shows only the records that appear in BOTH queries — the COMMON values.

-- intersect_demo.sql
SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

Output:

deptno
10
20
30

Why these three?

emp.deptno:  10, 20, 30   (unique values in emp)
dept.deptno: 10, 20, 30, 40

INTERSECT = "values present in BOTH lists"
→ 10, 20, 30 (all three exist in both!)
→ 40 is EXCLUDED (only in dept, not in emp)

graph TD
    E["EMP: 10, 20, 30"]
    D["DEPT: 10, 20, 30, 40"]
    I["INTERSECT\n(common values only)"]
    R["Result: 10, 20, 30"]

    E --> I
    D --> I
    I --> R

    style I fill:#9C27B0,color:#fff
    style R fill:#4CAF50,color:#fff

📐 UNION vs MINUS vs INTERSECT — The Ultimate Visual Comparison

graph TD
    Q1["Query 1 Result"]
    Q2["Query 2 Result"]
    UNION["UNION\n= Everything from BOTH\n(no duplicates)"]
    MINUS["MINUS\n= In Query1 ONLY\n(not in Query2)"]
    INTERSECT["INTERSECT\n= Only what's COMMON\nto BOTH"]

    Q1 --> UNION
    Q2 --> UNION
    Q1 --> MINUS
    Q2 --> MINUS
    Q1 --> INTERSECT
    Q2 --> INTERSECT

    style UNION fill:#4CAF50,color:#fff
    style MINUS fill:#F44336,color:#fff
    style INTERSECT fill:#9C27B0,color:#fff

Think of it like a Venn Diagram!

        Query 1        Query 2
       ┌───────┐      ┌───────┐
       │   A   │◄────►│   B   │
       └───────┘      └───────┘

UNION      = A + B (everything, no repeats)
MINUS      = A - B (only in A, remove anything also in B)
INTERSECT  = A ∩ B (only the overlapping middle part)

⚠️ Common Errors and Misconceptions

🎯 Sample Board Exam Questions

Q1: Very Short Answer [1 mark each]

a) What is a Cartesian Product? → The result of joining tables WITHOUT a matching condition — every row of Table 1 pairs with every row of Table 2

b) Which operator shows rows common to two queries? → INTERSECT

c) In an Equi Join using SELECT *, how many times does the common column appear? → Twice

d) Which SQL keyword automatically joins tables based on matching column names? → NATURAL JOIN

e) If Table A has 6 rows and Table B has 3 rows, how many rows result from their Cartesian Product? → 18 (6 × 3)

Q2: Short Answer [2 marks]

Q: Differentiate between Equi Join and Natural Join.

Equi Join requires you to explicitly write the join condition using = (e.g., WHERE emp.deptno = dept.deptno), and when using SELECT *, the joining column appears TWICE in the result. Natural Join automatically detects and joins on columns with the SAME NAME in both tables — no condition needs to be written, and the common column appears only ONCE.

Q3: Query Writing [3 marks]

Q: Using the EMP and DEPT tables, write queries to:

a) Display employee names and their department names.

SELECT e.ename, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno;

b) Display employees earning more than 10000, along with department name.

SELECT e.ename, e.salary, d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND e.salary > 10000;

c) Display department numbers that exist in DEPT but have no employees.

SELECT deptno FROM dept
MINUS
SELECT deptno FROM emp;

Q4: Output Based [3 marks]

Q: Based on the EMP and DEPT tables, what is the output of:

SELECT deptno FROM emp
INTERSECT
SELECT deptno FROM dept;

Output:

deptno
10
20
30

(These 3 department numbers exist in BOTH tables; 40 is excluded since it only exists in DEPT)

Q5: Differentiate [2 marks]

Q: What is the difference between UNION and INTERSECT?

UNION combines ALL rows from both queries into one list, removing duplicates — it shows what exists in EITHER query. INTERSECT shows only the rows that exist in BOTH queries simultaneously — the overlapping/common values.

✏️ Practice Problems

1. Using EMP and DEPT, write a query to display all employee names with their department head's name (dhead).

2. Write a query using Natural Join to display employee name, salary, and department name.

3. Write a query to find department numbers that have employees earning more than 11000 (using a Non-Equi condition combined with the join).

4. Explain, with row counts, why SELECT * FROM emp, dept; returns 20 rows while SELECT * FROM emp, dept WHERE emp.deptno = dept.deptno; returns only 5.

5. Write a query using UNION to combine all department numbers that appear in EITHER the EMP or DEPT table.

6. Write a query using MINUS to find employee department numbers that do NOT exist in the DEPT table (Hint: in our data, this returns nothing — explain why).

7. What is the difference between the traditional comma+WHERE join syntax and the modern JOIN...ON syntax? Are their outputs different?

8. If Table A has 4 rows and Table B has 5 rows, how many rows would their Cartesian Product produce? Show the calculation.

9. Write a query joining THREE tables: EMP, DEPT, and a new PROJECT table (linked via deptno), to display employee name, department name, and project name.

10. A table has columns (Name, Marks) and another has (Name, Marks, Grade). Can you perform a UNION between them directly? Explain why or why not, referencing the Golden Rules of SET Operations.