🔗 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! 🔗📊 > [!TIP] > **How to use these notes:** Focus especially on **Equi Join vs Natural Join** and **UNION vs INTERSECT vs MINUS** — these three comparisons appear in almost every board exam. Type every query yourself! 🎯 --- ## 8.1 🌟 Introduction ```mermaid 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! ::: grid ::: card 🔗 | JOIN | Combines COLUMNS from 2+ tables using a shared/related column | Employee table + Department table → combined info ::: card 🧮 | SET Operations | Combines ROWS from 2+ SELECT queries with the SAME columns | List of Dept IDs in EMP + List of Dept IDs in DEPT ::: **Our Two Sample Tables — used throughout this entire chapter:** **Table: EMP** | empno | ename | deptno | salary | | :--- | :--- | :--- | :--- | | 1 | Alam | 10 | 10300 | | 2 | Srijeeta | 20 | 6220 | | 3 | Bhaskar | 30 | 11320 | | 4 | Emely | 10 | 20500 | | 5 | Freddy | 30 | 11320 | **Table: DEPT** | deptno | dname | dhead | | :--- | :--- | :--- | | 10 | Sales | Ritika | | 20 | HR | Ankit | | 30 | Production | Abuzar | | 40 | IT | Mesha | > **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. ```sql -- 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) ``` ```mermaid 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 ``` > [!WARNING] > **No WHERE clause = Cartesian Product!** > Whenever you list multiple tables in `FROM` WITHOUT a matching condition, MySQL pairs EVERY row with EVERY row — creating a huge, mostly meaningless result. This is almost always a MISTAKE unless you specifically want every combination! > [!IMPORTANT] > **Board Exam Tip** > "What is a Cartesian Product? How many rows result from joining a 5-row table with a 4-row table?" — **2-mark** question! > Answer: A Cartesian Product results when tables are combined WITHOUT a join condition — every row of the first table pairs with every row of the second. Rows = 5 × 4 = **20 rows**. --- ### 8.2.2 Restricted Join (Join) 🔐 Add a `WHERE` condition to link the tables MEANINGFULLY — only rows that actually MATCH are shown. ```sql -- 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! ```mermaid 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. ```sql -- 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! > [!NOTE] > **Aliases are TEMPORARY! 🧠** > The alias `e` and `d` only exist for the DURATION of this one query. The actual table names (`emp`, `dept`) in the database are completely unchanged. --- ### 8.2.4 Additional Search Conditions in Joins 🎯 You can combine the JOIN condition with EXTRA filter conditions using `AND`. ```sql -- 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) ``` > [!IMPORTANT] > **Board Exam Tip** > "Write a query to display employee names and salaries who work in the 'Production' department." — Common **3-mark** question! > Answer: > ```sql > 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** | projno | projname | deptno | | :--- | :--- | :--- | | P1 | Website Redesign | 10 | | P2 | Payroll System | 20 | | P3 | Factory Automation | 30 | ```sql -- 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 ``` ```mermaid 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`! > [!IMPORTANT] > **Board Exam Tip** > "How many join conditions are needed to join 4 tables?" — **1-mark** question! > Answer: **3 conditions** (in general, joining `n` tables needs `n-1` conditions to properly link them all). --- ### 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). ```sql -- 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 *`. > [!IMPORTANT] > **Board Exam Tip** > "What is an Equi Join? Why does the common column appear twice?" — **2-mark** question! > Answer: An **Equi Join** links two tables using the equality (`=`) operator on a common column. When `SELECT *` is used, the joining column appears TWICE in the output — once from EACH table — because both columns are technically separate columns, even though their VALUES match. --- ### 8.2.7 Non-Equi Joins 🚫 A **Non-Equi Join** uses any operator OTHER than `=` — like `>`, `<`, `>=`, `<=`, or `!=` — to link or filter rows. ```sql -- 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. ```sql -- 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:** | Feature | Equi Join | Natural Join | | :--- | :--- | :--- | | **Condition** | You write it explicitly (`WHERE table1.col = table2.col`) | MySQL finds it automatically (same column name) | | **Common column in output** | Appears TWICE | Appears ONLY ONCE | | **Control** | High — you choose the column | Lower — MySQL decides based on names | | **Risk** | Safer, more predictable | Risky if tables share unexpected column names | > [!IMPORTANT] > **Board Exam Tip** > "Differentiate between Equi Join and Natural Join." — **2-mark** question, asked EVERY year! > Answer: **Equi Join** requires an explicit condition (`WHERE t1.col = t2.col`) and shows the joining column TWICE. **Natural Join** automatically joins on ALL columns with the same name (no WHERE needed) and shows the joining column only ONCE. --- ### 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. ```sql -- 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 are 100% correct in CBSE exams! **Old Style vs New Style — Same Result, Different Syntax:** ```sql -- 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; ``` > [!NOTE] > **Which one should YOU use? 🧠** > Both are completely valid for CBSE board exams! The textbook usually teaches the comma + WHERE style first (which we've used throughout this chapter), but the JOIN...ON syntax is the modern industry standard. Learn both — recognise both! --- ## 8.3 🧮 Performing SET Operations on Relations **SET Operations** combine the RESULTS of two or more SELECT queries — treating them like mathematical sets! > [!WARNING] > **The Golden Rules for SET Operations!** > Before you can combine two queries with UNION/MINUS/INTERSECT, they MUST have: > 1. The **SAME number of columns** > 2. **Matching data types** for corresponding columns > 3. Columns in the **SAME order** > Breaking any of these rules causes an ERROR! ```mermaid 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. ```sql -- 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!) ``` ```mermaid 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 ``` > [!IMPORTANT] > **Board Exam Tip** > "What does the UNION operator do? Does it remove duplicates?" — **2-mark** question! > Answer: UNION combines the result rows of two SELECT queries into a single result set, and it **automatically removes duplicate rows**. --- ### 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! ```sql -- 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! ``` ```mermaid 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 ``` > [!WARNING] > **Order MATTERS in MINUS!** > `A MINUS B` is DIFFERENT from `B MINUS A`! > `dept MINUS emp` → finds departments with NO employees (40) > `emp MINUS dept` → would find employee deptnos not in dept table (none, in our case — since every employee's dept exists in DEPT) > Always check WHICH table comes first! --- ### 8.3.3 INTERSECT Operator ✂️ **INTERSECT** shows only the records that appear in **BOTH** queries — the COMMON values. ```sql -- 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) ``` ```mermaid 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 ```mermaid 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) ``` | Operator | Shows | Using our example | | :--- | :--- | :--- | | **UNION** | Everything from both queries (duplicates removed) | 10, 20, 30, 40 | | **MINUS** | In first query, but NOT in second | 40 (dept MINUS emp) | | **INTERSECT** | Only what's common to BOTH | 10, 20, 30 | > [!IMPORTANT] > **Board Exam Tip** > "Differentiate between UNION, MINUS, and INTERSECT with examples." — **3-mark** question, asked constantly! > **UNION:** Combines all rows from both queries, removing duplicates. > **MINUS:** Shows rows from the FIRST query that do NOT appear in the second. > **INTERSECT:** Shows only rows that appear in BOTH queries. --- ## ⚠️ Common Errors and Misconceptions | Mistake | What Goes Wrong | Correct Understanding | | :--- | :--- | :--- | | ❌ Joining tables without a WHERE condition | Produces a massive, meaningless Cartesian Product | ✅ Always add a matching condition (or use JOIN...ON) | | ❌ Thinking Equi Join and Natural Join give identical output | Equi Join shows common column TWICE; Natural Join shows it ONCE | ✅ Natural Join removes the duplicate column automatically | | ❌ `A MINUS B` = `B MINUS A` | Order matters! Different results! | ✅ MINUS is NOT symmetric — always check which table is "first" | | ❌ Using UNION when columns don't match in number/type | Causes an ERROR | ✅ Both queries MUST have same number of columns, same types, same order | | ❌ Thinking UNION keeps duplicates | UNION removes them automatically | ✅ Use `UNION ALL` (not covered here) if you WANT duplicates kept | | ❌ Forgetting IT (deptno 40) has no employees | Assuming Restricted Join shows ALL departments | ✅ Restricted/Equi Join only shows MATCHING rows — unmatched rows (like IT) are excluded | --- ## 🔑 Quick Revision — Exam Ready! **Joins — One-Line Summary:** | Join Type | Key Idea | | :--- | :--- | | **Cartesian (Unrestricted)** | No condition — every row × every row | | **Restricted (Join)** | Has a WHERE condition — only matching rows | | **Equi Join** | Uses `=`; common column appears TWICE | | **Non-Equi Join** | Uses `>`, `<`, etc. instead of `=` | | **Natural Join** | Auto-joins on same column name; appears ONCE | | **JOIN...ON** | Modern syntax; same result as comma+WHERE | **SET Operations — One-Line Summary:** | Operator | What it Shows | | :--- | :--- | | **UNION** | Everything from both (no duplicates) | | **MINUS** | In first query ONLY, not in second | | **INTERSECT** | Common to BOTH queries | **SET Operation Golden Rules:** - Same number of columns ✅ - Same data types ✅ - Same column order ✅ --- ## 🎯 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. ```sql 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. ```sql 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. ```sql 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:** ```sql 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.