Relational Algebra II — Joins & Division

Relational Algebra II — join ও division

Read: ~45 min Hard 16 practice problems Live SQL runner

1. From Filtering to Combining

Module 08 gave you the unary and set operators — they take one or two relations and stay on their schemas. This module is about the real reason we have multiple tables: combining them. Joins glue rows from different relations together using a predicate; division answers “for all” questions.

Five join flavours, one cross product, and one division operator — once these click, every multi-table SQL query you read becomes obvious.

Module 08-এ ছিল unary ও set operator। এই module-এ আমরা শিখব কীভাবে দুটি relation-কে একসাথে যুক্ত করা যায় — সেটাই আসল কারণে আমরা একাধিক table রাখি। পাঁচ ধরনের join, একটি cross product, এবং একটি division operator — এগুলো বুঝে গেলে যেকোনো বহু-table SQL আপনার কাছে পরিষ্কার মনে হবে।
The big idea Every join, no matter how exotic, is just (R × S) filtered by a predicate, optionally with extra rows added back from the unmatched side (outer joins). Once you see this, joins are no longer scary.

2. Cartesian Product — R × S

Cartesian product pairs every row of R with every row of S. If R has m rows and S has n rows, R × S has m × n rows. The output schema is the union of both schemas.

In SQL, this is FROM R, S with no WHERE, or R CROSS JOIN S. It’s rarely useful directly — but every other join is built from it.

cartesian.sql
-- color × size  →  9 rows (3 × 3)
SELECT c, s FROM color CROSS JOIN size;
Cartesian product মানে R-এর প্রতিটি row-কে S-এর প্রতিটি row-এর সাথে জোড় বাঁধা। যদি R-এ ৩টি row থাকে এবং S-এ ৩টি, ফলাফলে ৯টি row আসবে। সরাসরি কাজে আসে কম, কিন্তু সব join আসলে cross product + filter।

3. Theta Join — R ⋈θ S

A theta join is a Cartesian product followed by a selection: R ⋈θ S = σθ(R × S). The predicate θ can use any comparison operator: <, <=, =, !=, >, >=.

theta_join.sql
-- Each student matched with EVERY scholarship they qualify for: cgpa >= min_cgpa
SELECT s.name, s.cgpa, sc.label, sc.amount
FROM student s, scholarship sc
WHERE s.cgpa >= sc.min_cgpa
ORDER BY s.name, sc.amount;
Theta-join = cross product + ফিল্টার। ফিল্টার-শর্তে যেকোনো comparison ব্যবহার করা যায় (≥, <, =, ইত্যাদি)। উপরের উদাহরণে প্রতিটি ছাত্রকে সেইসব scholarship-এর সাথে join করা হয়েছে যেগুলোর জন্য তিনি যোগ্য (cgpa ≥ min_cgpa)।

4. Equijoin and Natural Join

An equijoin is a theta join where every comparison uses =. By far the most common join in real systems — it’s how you connect a foreign key to its primary key.

A natural join (written R ⋈ S) is an equijoin on every column with the same name in both relations, after which the duplicated columns are dropped — leaving each shared column exactly once. Convenient, but dangerous: a renamed column silently turns it into a Cartesian.

equijoin.sql
-- Equijoin (the standard FK→PK join)
SELECT s.name, d.dept_name
FROM student s
JOIN department d ON d.dept_id = s.dept_id;

4.1 Natural join — same idea with auto-detected columns

natural_join.sql
-- Natural join: SQLite supports it; the join column dept_id is implicit.
SELECT name, dept_name
FROM student
NATURAL JOIN department;
Why production code rarely uses NATURAL JOIN If someone later adds a column called created_at to both tables, NATURAL JOIN will start matching on it too. Suddenly your query returns far fewer rows — silently. Most teams ban it and use explicit JOIN ... ON ....
Equijoin = comparison শুধু = দিয়ে; বাস্তবে FK → PK match করতে এটিই ব্যবহৃত হয়। Natural join দু-টি relation-এ একই নামের সব column-এ নিজে নিজে equijoin করে এবং duplicate column বাদ দেয়। সুবিধাজনক, কিন্তু বিপজ্জনক — পরে কেউ একই নামের নতুন column যোগ করলে query চুপচাপ ভিন্ন ফলাফল দিতে পারে।

5. Outer Joins — Keeping the Misfits

An inner join silently throws away rows on either side that don’t match. Outer joins bring them back as NULL-padded rows. Three flavours, distinguished by which side is preserved.

AlgebraSymbolSQLMeaning
Left outer joinR ⟕ SLEFT JOINAll R; matched S; unmatched S → NULL
Right outer joinR ⟖ SRIGHT JOINAll S; matched R; unmatched R → NULL
Full outer joinR ⟗ SFULL OUTER JOINEveryone, NULL-padded both sides
Inner join match না-হওয়া row বাদ দেয়। Outer join সেগুলোকে ফিরিয়ে আনে — না-মেলা পাশে NULL বসিয়ে দেয়। বাঁদিক রাখলে LEFT, ডানদিক রাখলে RIGHT, দু-দিক রাখলে FULL OUTER।

5.1 LEFT JOIN — students with all enrollments (even those who took none)

left_join.sql
SELECT s.name, e.course
FROM student s
LEFT JOIN enrollment e ON e.student_id = s.student_id
ORDER BY s.name;

Notice Faria appears with a NULL course — she took nothing, and a plain JOIN would have hidden her.

5.2 FULL OUTER JOIN — bKash users + Nagad users together

full_outer.sql
SELECT b.msisdn AS bkash_msisdn, b.balance AS bkash_bal,
       n.msisdn AS nagad_msisdn, n.balance AS nagad_bal
FROM bkash b
FULL OUTER JOIN nagad n ON n.msisdn = b.msisdn;
Visual cue Think of a Venn diagram. INNER = the lens-shaped intersection. LEFT = full left circle. RIGHT = full right circle. FULL OUTER = both circles entirely.

6. Division — Answering “for all”

Division, written R ÷ S, answers questions of the form “give me every X that is related to every Y in S.” It’s the algebra version of universal quantification.

Definition: if R has schema (X, Y) and S has schema (Y), then R ÷ S has schema (X) and contains every X-value that pairs with every Y-value in S inside R.

Division (R ÷ S) দিয়ে আমরা “for all” টাইপের প্রশ্নের উত্তর দিই — যেমন “এমন ছাত্রদের নাম দাও যারা সব CSE-প্রয়োজনীয় course-এ ভর্তি হয়েছে।” এটি universal quantifier (∀)-এর algebra-সংস্করণ।

6.1 Concrete example — “students enrolled in every required CSE course”

We have two tables: enroll(student_id, course_id) and required(course_id). We want students who appear in enroll for every required course.

division_groupby.sql
-- Trick 1: GROUP BY ... HAVING COUNT(DISTINCT) = total required count
SELECT e.student_id
FROM enroll e
JOIN required r ON r.course_id = e.course_id
GROUP BY e.student_id
HAVING COUNT(DISTINCT e.course_id) = (SELECT COUNT(*) FROM required);

6.2 Same answer, expressed with double NOT EXISTS

The textbook trick: a student qualifies if there does not exist a required course for which there does not exist a matching enrollment row. Two negations = a universal.

division_not_exists.sql
-- Trick 2: double NOT EXISTS — direct translation of the algebra.
SELECT s.student_id, s.name
FROM student s
WHERE NOT EXISTS (
    SELECT 1 FROM required r
    WHERE NOT EXISTS (
        SELECT 1 FROM enroll e
        WHERE e.student_id = s.student_id
          AND e.course_id  = r.course_id
    )
);
Why double-NOT-EXISTS works “Student X took every required course” = “there is no required course that X did not take”. The outer NOT EXISTS asserts “no required course exists for which …”; the inner NOT EXISTS asserts “… X never enrolled.” Together they encode ∀ as ¬∃¬. That’s De Morgan’s law on quantifiers.

6.3 SQL has no DIVIDE keyword

SQL never adopted division as a primitive. So in practice we always rewrite it using either the GROUP BY … HAVING COUNT trick or the double NOT EXISTS trick — and both are perfectly valid algebraic transforms.

7. Quick Look at Join Performance

All joins are logically equivalent to σ(R × S), but no real database actually computes the full Cartesian first — that would be ruinous. Engines pick a physical join algorithm based on statistics:

AlgorithmWhen chosenCost intuition
Nested-loop joinSmall inner side; fallback when nothing else fitsO(m × n)
Index nested-loopInner side has a usable index on the join keyO(m × log n)
Hash joinEquijoin; both sides fit in memoryO(m + n)
Sort-merge joinBoth sides sorted on the join keyO(m log m + n log n)
সব join logical-ভাবে cross product + filter, কিন্তু কোনো serious database আসলে cross product বানায় না। Engine বেছে নেয় nested-loop, index nested-loop, hash join, বা sort-merge join — table-এর আকার, index, এবং memory অনুযায়ী।

8. Practice Problems

প্রথমে নিজে চেষ্টা করুন, তারপর Show Answer-এ ক্লিক করুন। কয়েকটি উত্তর সরাসরি runnable SQL সহ দেওয়া আছে।
  1. If R has 4 rows and S has 5 rows, how many rows does R × S produce?
    R-এ ৪টি row, S-এ ৫টি — R × S কত row দেবে?
    ✨ Show Answer

    4 × 5 = 20 rows.

  2. Write the algebra for: “names of students with their department name”, given student(s_id, name, d_id) and department(d_id, dname).
    algebra-তে লিখুন।
    ✨ Show Answer

    πname, dname(student ⋈student.d_id = department.d_id department).

  3. Why might a NATURAL JOIN suddenly return zero rows after someone adds a column?
    কেউ একটি column যোগ করার পর NATURAL JOIN হঠাৎ ০ row দিতে পারে — কেন?
    ✨ Show Answer

    Because the new column shares its name with one in the other table, NATURAL JOIN now also requires that column to match. If the values differ, no rows qualify.

  4. In our LEFT JOIN demo, why does Faria still appear in the result?
    LEFT JOIN-এ Faria কেন থেকে যায়?
    ✨ Show Answer

    LEFT JOIN preserves every row of the left table even when no match exists on the right. Faria has no enrollment, so she is returned with NULL course.

  5. Use a LEFT JOIN to find students who took NO course.
    যেসব ছাত্র কোনো course নেয়নি — LEFT JOIN দিয়ে বের করুন।
    ✨ Show Answer
    p5.sql
    SELECT s.name
FROM student s
LEFT JOIN enrollment e ON e.student_id = s.student_id
WHERE e.student_id IS NULL;
  6. What is the difference between INNER JOIN and CROSS JOIN with a true predicate?
    INNER JOIN vs CROSS JOIN (true predicate)?
    ✨ Show Answer

    None — they’re identical. R CROSS JOIN S is the same as R JOIN S ON TRUE.

  7. Which join would you use to write “customers who never placed an order”?
    যেসব গ্রাহক কখনো order করেননি — কোন join?
    ✨ Show Answer

    LEFT JOIN customers to orders, then WHERE orders.customer_id IS NULL — also called an anti-join.

  8. Translate to algebra: SELECT * FROM R, S WHERE R.x = S.x.
    algebra-তে রূপান্তর করুন।
    ✨ Show Answer

    R ⋈R.x = S.x S = σR.x = S.x(R × S). It is an equijoin.

  9. In the division example, what should the result be? Verify by reasoning, then by running.
    division উদাহরণে ফলাফল কী হবে — যুক্তি দিয়ে এবং চালিয়ে যাচাই করুন।
    ✨ Show Answer

    Required = {CSE201, CSE301, CSE401}. Student 1 took {CSE201, CSE301, CSE401, CSE105} → has all 3 ✅. Student 2 took {CSE201, CSE301} → missing CSE401 ❌. Student 3 took {CSE201, CSE301, CSE401} → all ✅. Student 4 took only CSE401 → ❌. So the answer is {1, 3}.

  10. Why does SQL not have a built-in DIVIDE operator?
    SQL-এ DIVIDE কেন নেই?
    ✨ Show Answer

    It’s rarely needed in practice, and the two equivalent rewrites (GROUP BY-COUNT, double NOT EXISTS) cover every case. Adding it would have complicated the language for little gain.

  11. Express “riders who used Pathao for every district” in SQL using GROUP BY.
    যেসব rider Pathao-তে সব জেলায় গেছেন — GROUP BY দিয়ে লিখুন।
    ✨ Show Answer
    p11.sql
    SELECT r.rider_id
FROM ride r
JOIN district d ON d.name = r.district
GROUP BY r.rider_id
HAVING COUNT(DISTINCT r.district) = (SELECT COUNT(*) FROM district);
  12. When is INNER JOIN exactly equal to LEFT JOIN?
    INNER JOIN ও LEFT JOIN কখন এক?
    ✨ Show Answer

    When every row of the left side has at least one match on the right (e.g. enforced by a NOT NULL FK + matching PK).

  13. Use FULL OUTER JOIN to find users who exist in only one of two tables.
    FULL OUTER JOIN দিয়ে শুধু এক table-এ থাকা user বের করুন।
    ✨ Show Answer
    p13.sql
    SELECT b.id AS bkash_only_or_both, n.id AS nagad_only_or_both
FROM bkash b
FULL OUTER JOIN nagad n ON n.id = b.id
WHERE b.id IS NULL OR n.id IS NULL;
  14. What can go wrong if you forget the ON clause in a JOIN?
    JOIN-এ ON clause ভুলে গেলে কী হবে?
    ✨ Show Answer

    You silently get a Cartesian product. With 100k × 100k tables, the result is 10 billion rows — very long-running queries that bring servers down.

  15. Express “books that have been borrowed by EVERY library member” using double NOT EXISTS.
    যে বইগুলো লাইব্রেরির সব member ধার করেছেন — double NOT EXISTS দিয়ে লিখুন।
    ✨ Show Answer
    p15.sql
    SELECT b.book_id, b.title
FROM book b
WHERE NOT EXISTS (
    SELECT 1 FROM member m
    WHERE NOT EXISTS (
        SELECT 1 FROM borrow br
        WHERE br.member_id = m.member_id
          AND br.book_id   = b.book_id
    )
);
  16. Why is hash join often the fastest equijoin algorithm for big tables?
    বড় table-এ hash join প্রায়ই দ্রুত — কেন?
    ✨ Show Answer

    It builds an in-memory hash on the smaller relation’s join key, then probes once per row of the larger side. That gives O(m + n) instead of O(m × n) — a huge win on big tables, provided the smaller side fits in RAM.

Summary — Module 09

Joins are the engine of relational databases. Cartesian gives you every pair, theta-join filters them by a predicate, equijoin uses only equality, and natural join makes the equality implicit. Outer joins keep the misfits with NULL padding so reports include rows that would otherwise vanish. Division answers “for all” questions — SQL has no DIVIDE keyword, but two equivalent rewrites (GROUP BY + COUNT, or double NOT EXISTS) cover every case. With these in your toolkit, you can express any multi-table query a database can answer.

Joins-ই relational database-এর প্রকৃত শক্তি। Cartesian, theta, equijoin, natural — এই ধাপগুলো মিলিয়েই সব join। Outer join মিল না-হওয়া row-গুলোকে NULL দিয়ে বাঁচিয়ে রাখে। Division (∀)-প্রশ্নের উত্তর দেয় — SQL-এ আলাদা keyword নেই, তবে GROUP BY + COUNT অথবা double NOT EXISTS দিয়ে সমাধান হয়। এই tool-গুলো জানা থাকলে যেকোনো multi-table query সহজ হয়।

Next Module → Relational Calculus & SQL Equivalence.

Previous Next: Relational Calculus