JOINs — INNER, LEFT, RIGHT, FULL, SELF, CROSS

Join — সব ধরনের join এক জায়গায়

Read: ~35 min Medium 22 practice problems Live SQLite runner

1. Why Do JOINs Even Exist?

In a well-designed relational database, data is split across many tables — students in one table, courses in another, enrolments in a third. This is called normalization, and it eliminates duplication. But on its own, a single table is rarely enough to answer a real question. To answer "which student is enrolled in which course?" you must recombine the rows from different tables. That recombination operation is called a JOIN.

একটি ভালো ডিজাইন করা relational database-এ data বিভিন্ন table-এ ভাগ করা থাকে — student এক table-এ, course আরেক table-এ, আর enrolment তৃতীয় table-এ। এতে duplication কমে যায়। কিন্তু কোনো একটি প্রশ্নের উত্তর পেতে গেলে — যেমন "কোন ছাত্র কোন course-এ ভর্তি?" — সেই table-গুলোকে আবার একসাথে জোড়া দিতে হয়। এই জোড়া দেওয়ার operation-ই হলো JOIN।

Every join is, mathematically, a filtered Cartesian product. You start with every possible pair of rows from two tables and keep only the pairs that satisfy a condition. The seven flavours below differ only in which non-matching rows you also keep.

Mental model JOIN = (CROSS JOIN) WHERE (some condition). All other joins are sugar over this idea — INNER drops everything that fails, LEFT keeps the leftover left rows, FULL keeps both leftovers.

JOIN আসলে দুই table-এর সব সম্ভাব্য জোড়া (cross join) থেকে condition-মতো বেছে নেওয়া। বাকিগুলো — LEFT, FULL, RIGHT — শুধু "যেগুলো মিলেনি, সেগুলোর মধ্যে কাকে রাখব" সেই প্রশ্নের আলাদা আলাদা উত্তর।

2. The Dataset — Two Tables We'll Reuse

Throughout this lecture we work with a tiny university registration system: a students table and a courses table, plus an enrolments table that connects them. Every code block below is independent — it sets up its own copy of the data via data-setup, so you can re-run any block as often as you like.

পুরো lecture জুড়ে আমরা একটি ছোট university registration system ব্যবহার করব: students, courses ও enrolments তিনটি table। প্রতিটি code block স্বাধীনভাবে চলে — নিজস্ব setup দিয়ে data বানিয়ে নেয়, তাই যত খুশি বার চালানো যাবে।

peek.sql

-- Peek at the three tables we'll join repeatedly
SELECT 'students' AS tbl, COUNT(*) AS rows FROM students
UNION ALL SELECT 'courses',    COUNT(*) FROM courses
UNION ALL SELECT 'enrolments', COUNT(*) FROM enrolments;

Figure 16.1 — তিনটি table-এর foreign key সম্পর্ক।

3. INNER JOIN — Only the Rows That Match

INNER JOIN returns only those rows where the join condition is true on both sides. If a student has no enrolment, they vanish. If an enrolment refers to a non-existent student (which should never happen if your foreign keys are sound), it also vanishes.

INNER JOIN শুধু সেই row-ই ফেরত দেয় যেগুলো দুই দিকেই মিলে। যেসব student-এর কোনো enrolment নেই, তারা ফলাফলে আসবে না। মনে রাখুন — INNER JOIN মানেই "intersection" বা "অংশীদারি"।

inner_join.sql

-- Every (student, course, grade) where an enrolment actually exists
SELECT s.name, c.title, e.grade
FROM   students s
INNER JOIN enrolments e ON e.sid   = s.id
INNER JOIN courses    c ON c.code  = e.ccode
ORDER BY s.name, c.code;

Notice that Sajid (BBA) is missing from the result — he isn't enrolled in any course. INNER JOIN drops him silently. That is by design, but it can be a bug if you forgot.

Style tip The keyword JOIN by itself means INNER JOIN in every SQL dialect. Spelling out INNER is purely for readability — many teams require it in code review for exactly that reason.

শুধু JOIN লিখলেই সেটি INNER JOIN বোঝায়। তবে INNER লিখে দিলে অন্য developer পড়তে গিয়ে দ্বিধায় পড়ে না।

4. LEFT, RIGHT, and FULL OUTER JOIN

What if the question is "list every student, with their courses if any"? Now we must keep Sajid even though he has no enrolment. That is what an OUTER JOIN is for. The flavours differ in which side's leftovers are preserved:

LEFT OUTER JOIN — keep every row from the left table; fill missing right-side columns with NULL.
RIGHT OUTER JOIN — mirror image: every row from the right table.
FULL OUTER JOIN — keep both leftovers, filling whichever side is missing with NULL.

যদি প্রশ্ন হয় "প্রতিটি student-কে দেখাও, তার course থাকুক বা না-থাকুক" — তখন INNER JOIN-এ Sajid বাদ পড়ে যাবে। এই সমস্যার সমাধান OUTER JOIN। LEFT মানে বাঁ-পাশের সব row রাখো, RIGHT মানে ডান-পাশের, আর FULL মানে দুই পাশেরই সব row রাখো — যেখানে মিল নেই, সেখানে NULL বসিয়ে দাও।

Figure 16.2 — JOIN-গুলির ভেন (Venn) ডায়াগ্রাম।

left_join.sql

-- Every student, with course code if enrolled, NULL otherwise
SELECT s.name, e.ccode, e.grade
FROM   students   s
LEFT JOIN enrolments e ON e.sid = s.id
ORDER BY s.id;

Now Sajid appears with NULL in ccode and grade. We can flip the logic to find only students with no enrolment using a classic LEFT-JOIN-IS-NULL pattern:

anti_join.sql

-- "Anti-join" — students NOT in enrolments
SELECT s.id, s.name
FROM   students   s
LEFT JOIN enrolments e ON e.sid = s.id
WHERE e.sid IS NULL;

SQLite history corner Until version 3.39 (2022), SQLite supported LEFT JOIN but not RIGHT JOIN or FULL OUTER JOIN. From 3.39 onwards both work natively. If you are stuck on an older build, the workaround is simple: swap the table order and use LEFT JOIN. A RIGHT JOIN B is equivalent to B LEFT JOIN A.

পুরোনো SQLite-এ RIGHT JOIN ছিল না — table দুটোর order উল্টে LEFT JOIN লিখলেই একই ফল পাওয়া যায়। FULL OUTER-এর জন্য পুরোনো trick হলো দুটো LEFT JOIN-কে UNION দিয়ে জোড়া দেওয়া।

full_outer.sql

-- FULL OUTER between courses and enrolments — find dangling rows on either side
SELECT c.code, c.title, e.sid, e.grade
FROM   courses     c
FULL OUTER JOIN enrolments e ON e.ccode = c.code
ORDER BY COALESCE(c.code, '~'), e.sid;

Course PHY101 exists in courses but has zero enrolments — INNER JOIN would hide it. FULL OUTER reveals both kinds of orphans.

5. SELF JOIN — A Table Joined With Itself

A SELF JOIN is just a join where both inputs happen to be the same table, viewed under two aliases. It is the natural way to express "row A relates to row B in the same table" — manager–employee chains, friend pairs, prerequisite courses.

SELF JOIN মানে একই table-কে দু-বার ব্যবহার করে join করা — দুটি আলাদা alias দিয়ে। যেমন employee–manager সম্পর্ক, course-এর prerequisite, কিংবা একই দপ্তরের ছাত্রদের জোড়া তৈরি করা।

self_join.sql

-- For each employee, who is their manager?
SELECT e.name AS employee,
       m.name AS manager
FROM   employees e
LEFT JOIN employees m ON m.id = e.manager_id
ORDER BY e.id;

We deliberately use LEFT JOIN so that the CEO (Karim, with no manager) still appears, with a NULL manager. With INNER JOIN the CEO would disappear — a classic real-world bug in HR dashboards.

6. CROSS JOIN — The Cartesian Product

CROSS JOIN returns every possible pair of rows. For tables with m and n rows, the result has m × n rows. There is no ON clause — and that is exactly the point.

CROSS JOIN-এ কোনো ON থাকে না। এটি দুই table-এর প্রতিটি row-কে অপরের প্রতিটি row-এর সাথে জোড়া বানায়। ৩ row × ৪ row = ১২ row। বাস্তবে সরাসরি কম ব্যবহৃত হলেও, রিপোর্টিং বা সিরিজ তৈরিতে এর জুড়ি নেই।

cross_join.sql

-- Every shirt variant a Bashundhara City vendor might list (4 × 3 = 12)
SELECT s.s AS size, c.c AS color
FROM   sizes  s
CROSS JOIN colors c
ORDER BY s.s, c.c;

Footgun If you forget the ON clause in a normal join, most databases will silently turn it into a CROSS JOIN. With two million-row tables you instantly produce a one-trillion-row query. Always double-check the ON clause is present.

ON ভুলে গেলে অনেক database চুপচাপ এটিকে CROSS JOIN বানিয়ে দেয় — আর দু-টি দশ লাখের table-কে cross করলে ১০¹² row! সবসময় ON আছে কিনা যাচাই করুন।

7. NATURAL JOIN — Convenient, but Risky

NATURAL JOIN auto-matches rows on every column with the same name in both tables. It saves typing — but is dangerous because the join condition is implicit. Add a column called created_at to both tables and your NATURAL JOIN silently changes meaning.

NATURAL JOIN দুই table-এ একই নামের column পেলে সেগুলিকে স্বয়ংক্রিয়ভাবে join করে। কোড সংক্ষিপ্ত হয় কিন্তু ভয়ংকর — কেউ একটি অতিরিক্ত column যোগ করলে query-র অর্থ বদলে যেতে পারে। Production code-এ এটি সাধারণত নিষিদ্ধ।

natural.sql

-- Implicit ON a.id = b.id because 'id' is the only shared column name
SELECT * FROM a NATURAL JOIN b;

✅ Use Explicit ON (সবসময় ON ব্যবহার করুন)

Reader sees the join condition directly
Survives column renames / new columns
Code review and refactoring stay safe

⚠️ Avoid NATURAL JOIN (NATURAL JOIN বাদ দিন)

Hidden, schema-dependent semantics
Adding any same-named column changes results
Hard to audit, easy to break

8. Multi-Table Joins & Join Algorithms (Preview)

Joins compose freely. Joining four tables is just three pairwise joins with parentheses determining order. The logical result is order-independent, but the physical execution plan that the optimizer chooses can be a thousand times faster or slower.

চার table-এর join মানে আসলে তিনটি pairwise join। ফলাফল একই, কিন্তু query optimizer যেভাবে সেগুলো execute করে — সেই algorithm-এর ওপর গতি নির্ভর করে।

four_way.sql

-- Show student, course, and the building where the course department lives
SELECT s.name, c.title, d.building, e.grade
FROM   students    s
JOIN   enrolments  e ON e.sid   = s.id
JOIN   courses     c ON c.code  = e.ccode
JOIN   departments d ON d.dept  = c.dept
ORDER BY s.name;

Algorithm	Idea	Best when	বাংলায়
Nested-loop join	For each row in A, scan B for matches.	One side is tiny, or B has an index on the join key.	A-এর প্রতিটি row-এর জন্য B-তে খোঁজা।
Hash join	Build a hash table on B's join column, probe with A.	Both sides are large but fit in memory; equality joins.	B-এর join column দিয়ে hash তৈরি করে A থেকে দ্রুত lookup।
Sort-merge join	Sort both sides on join key, walk in lockstep.	Inputs already sorted, or huge tables on disk.	দু-পাশকে sort করে একসাথে এগিয়ে নিয়ে মিল খুঁজে বের করা।
Index nested-loop	Outer scan + index lookup per outer row.	Selective outer + indexed inner.	বাইরের প্রতিটি row-এর জন্য ভিতরে index দিয়ে instant lookup।

SQLite specifically SQLite is conservative — it almost always uses nested-loop joins, but with smart use of indexes (especially the automatic index on PRIMARY KEY) it is extraordinarily fast for typical workloads. Run EXPLAIN QUERY PLAN <your query> to see exactly what it does. We will cover this in detail in the indexing module.

SQLite প্রায় সবসময় nested-loop join ব্যবহার করে, কিন্তু index-এর সাহায্যে এটি বাস্তবে অত্যন্ত দ্রুত। EXPLAIN QUERY PLAN দিয়ে কেমন plan ব্যবহার হচ্ছে দেখা যায়।

9. Quick Cheat Sheet (এক নজরে)

Need	Join	Example pattern
Only matched rows	INNER JOIN	`A JOIN B ON A.k = B.k`
All A; B if match	LEFT JOIN	`A LEFT JOIN B ON A.k = B.k`
All B; A if match	RIGHT JOIN	`A RIGHT JOIN B` or swap and LEFT
Everything from both	FULL OUTER	`A FULL OUTER JOIN B`
Find rows with NO match	LEFT JOIN + IS NULL	`WHERE B.k IS NULL`
Pair a row with itself	SELF JOIN	`A x JOIN A y ON ...`
Every combination	CROSS JOIN	`A CROSS JOIN B`

10. Practice Problems

22 problems covering every join type. Try them on your own first; each answer is a runnable SQLite block.

২২টি অনুশীলনী — সব ধরনের join ছোঁয়া হয়েছে। আগে নিজে চেষ্টা করুন, তারপর উত্তর মিলিয়ে নিন। প্রতিটি উত্তর সরাসরি ব্রাউজারে চালানো যায়।

List every (student, course title, grade) using INNER JOIN.

INNER JOIN দিয়ে প্রতিটি (student, course title, grade) দেখান।
✨ Show Answer
ans1.sql
```
SELECT s.name, c.title, e.grade
FROM students s
JOIN enrolments e ON e.sid=s.id
JOIN courses c ON c.code=e.ccode;
```
Find students who have NO enrolments.

যেসব student-এর কোনো enrolment নেই, তাদের তালিকা দিন।
✨ Show Answer
ans2.sql
```
SELECT s.id, s.name
FROM students s
LEFT JOIN enrolments e ON e.sid=s.id
WHERE e.sid IS NULL;
```
Find courses that no student is enrolled in.

যেসব course-এ কোনো student ভর্তি নেই।
✨ Show Answer
ans3.sql
```
SELECT c.code, c.title
FROM courses c
LEFT JOIN enrolments e ON e.ccode=c.code
WHERE e.ccode IS NULL;
```
Show every employee with their manager's name (CEO included).

প্রতিটি employee ও তার manager-এর নাম দেখান, CEO-ও যেন বাদ না পড়ে।
✨ Show Answer
ans4.sql
```
SELECT e.name, COALESCE(m.name,'(CEO)') AS manager
FROM employees e
LEFT JOIN employees m ON m.id=e.manager_id;
```
Generate every (size, color) pair for a clothing catalog using CROSS JOIN.

CROSS JOIN দিয়ে প্রতিটি (size, color) জোড়া তৈরি করুন।
✨ Show Answer
ans5.sql
```
SELECT s.s AS size, c.c AS color
FROM sizes s CROSS JOIN colors c;
```
Find pairs of students in the same department (no self-pairs, no duplicates).

একই বিভাগের student-জোড়া খুঁজুন — নিজেকে নিজের সাথে মেলানো যাবে না।
✨ Show Answer
ans6.sql
```
SELECT a.name AS s1, b.name AS s2, a.dept
FROM students a
JOIN students b ON a.dept=b.dept
                       AND a.id<b.id;
```
Why does a.id < b.id remove duplicates and self-pairs in the previous problem? Explain.

আগের প্রশ্নে a.id < b.id কেন duplicate ও self-pair বাদ দেয়?

✨ Show Answer

Answer: Without the inequality, every (X, Y) pair appears twice — once as (X,Y) and once as (Y,X) — and (X,X) appears once for every row. The strict-less-than condition picks exactly one ordering and rules out the equal case.

শর্তটি না থাকলে প্রতিটি জোড়া দু-বার আসত (X,Y এবং Y,X), আর প্রত্যেকে নিজেকে নিজের সাথে জোড়া দিত। < ব্যবহার করায় শুধু একটি order রাখা হয় এবং self-pair বাদ পড়ে।
FULL OUTER JOIN: list every (course, sid) including courses with no enrolments and enrolments with no matching course.

FULL OUTER JOIN ব্যবহার করে দু-পাশের অনাথ row-ও দেখান।
✨ Show Answer
ans8.sql
```
SELECT c.code, c.title, e.sid, e.ccode
FROM courses c
FULL OUTER JOIN enrolments e ON c.code=e.ccode;
```
Simulate a FULL OUTER JOIN using two LEFT JOINs and a UNION (the legacy trick).

দুটি LEFT JOIN ও UNION দিয়ে FULL OUTER JOIN অনুকরণ করুন।
✨ Show Answer
ans9.sql
```
SELECT a.k, a.va, b.vb FROM a LEFT JOIN b ON a.k=b.k
UNION
SELECT b.k, a.va, b.vb FROM b LEFT JOIN a ON a.k=b.k;
```
Count enrolments per student, including students with zero (show zero, not skip).

প্রতিটি student-এর enrolment সংখ্যা দেখান, শূন্য হলেও দেখাবেন।
✨ Show Answer
ans10.sql
```
SELECT s.name, COUNT(e.ccode) AS n
FROM students s
LEFT JOIN enrolments e ON e.sid=s.id
GROUP BY s.id, s.name
ORDER BY s.id;
```
Note: use COUNT(e.ccode) not COUNT(*) — otherwise NULL rows from LEFT JOIN are counted as 1.
Why does COUNT(*) give wrong results in the previous query?

আগের query-তে COUNT(*) কেন ভুল হবে?

✨ Show Answer

Answer: A LEFT JOIN keeps the left row even when the right side is NULL — that "no-match row" still counts as 1 in COUNT(*). Counting a non-NULL right-side column instead skips those NULL rows, giving 0 for students with no enrolments.

LEFT JOIN-এ মিল না থাকলেও বাঁ-পাশের row থাকে (right-side NULL); COUNT(*) সেটিকেও ১ ধরবে। কিন্তু right-side কলামকে count করলে NULL বাদ যায় বলে ফল ০ হয়।
For each customer, find their order count in a bKash-like merchant DB; include zero-order customers.

bKash-এর মতো merchant DB-তে প্রতিটি customer-এর order সংখ্যা — শূন্য হলেও।
✨ Show Answer
ans12.sql
```
SELECT c.phone,
       COUNT(o.id)             AS orders,
       COALESCE(SUM(o.amount),0) AS total_tk
FROM customers c
LEFT JOIN orders o ON o.cid=c.id
GROUP BY c.id, c.phone;
```
Library system: list every book with how many times it has been borrowed; never borrowed → 0.

Library system-এ প্রতিটি বই কত বার নেওয়া হয়েছে, সেই হিসাব দিন।
✨ Show Answer
ans13.sql
```
SELECT b.title, COUNT(l.member) AS borrowed
FROM books b
LEFT JOIN loans l ON l.book_id=b.id
GROUP BY b.id, b.title;
```
List students who took both CSE101 AND CSE220 using a SELF JOIN on enrolments.

যেসব student CSE101 ও CSE220 — উভয়েই নিয়েছে।
✨ Show Answer
ans14.sql
```
SELECT DISTINCT a.sid
FROM enrolments a
JOIN enrolments b ON a.sid=b.sid
WHERE a.ccode='CSE101' AND b.ccode='CSE220';
```
Generate a calendar of every day in October 2025 from a single 31-row numbers table using CROSS JOIN.

৩১-row numbers table থেকে October 2025-এর সব তারিখ তৈরি করুন।
✨ Show Answer
ans15.sql
```
SELECT m.prefix||substr('00'||n.i,-2) AS date
FROM month m CROSS JOIN n
ORDER BY date;
```
Why is NATURAL JOIN considered fragile? Give a concrete failure scenario.

NATURAL JOIN কেন বিপজ্জনক — একটি বাস্তব উদাহরণ দিন।

✨ Show Answer

Answer: Suppose orders joins to customers on customer_id. Six months later someone adds a created_at column to both tables for auditing. NATURAL JOIN suddenly requires both customer_id AND created_at to match — the join silently returns almost nothing. With explicit ON, the migration would not have changed query results.

দু-টি table-এ customer_id-এর সাথে পরে created_at column যোগ করলে NATURAL JOIN হঠাৎ দু-টি column-এর উপর join করতে শুরু করে — query চুপচাপ ভুল ফলাফল দেয়। তাই production-এ এটি এড়িয়ে চলা হয়।
Count the rows of students CROSS JOIN courses. Predict before running.

students × courses-এর row সংখ্যা আগে অনুমান করুন, তারপর মিলিয়ে দেখুন।
✨ Show Answer
ans17.sql
```
SELECT COUNT(*) AS total
FROM students CROSS JOIN courses;
```
5 × 4 = 20.
Find pairs of products in the same category that have a price difference greater than 1000 BDT.

একই category-র product-জোড়া যাদের দামের পার্থক্য ১০০০ টাকার বেশি।
✨ Show Answer
ans18.sql
```
SELECT a.name AS p1, b.name AS p2,
       ABS(a.price-b.price) AS diff_tk
FROM products a
JOIN products b ON a.cat=b.cat
                  AND a.id<b.id
WHERE ABS(a.price-b.price) > 1000;
```
Filter in ON vs WHERE in a LEFT JOIN — show why they differ. Find all students with their CSE101 grade if any.

LEFT JOIN-এ ON ও WHERE-এর filter আলাদা — তা কেন? CSE101-এ student-দের grade দেখান।
✨ Show Answer
ans19.sql
```
-- Correct: filter inside ON keeps all students
SELECT s.name, e.grade
FROM students s
LEFT JOIN enrolments e
       ON e.sid=s.id AND e.ccode='CSE101';
```
Putting e.ccode = 'CSE101' in WHERE would drop students with no CSE101 row entirely (because their e.ccode is NULL, which fails the equality). Inside ON, the filter is applied before the LEFT-side preservation — so students stay.
Three-way INNER JOIN: list every (student name, course title, building).

তিন-table-এর INNER JOIN দিয়ে (student, course, building) বের করুন।
✨ Show Answer
ans20.sql
```
SELECT s.name, c.title, d.building
FROM students s
JOIN enrolments  e ON e.sid=s.id
JOIN courses     c ON c.code=e.ccode
JOIN departments d ON d.dept=c.dept;
```
Explain why A RIGHT JOIN B equals B LEFT JOIN A.

কেন A RIGHT JOIN B ≡ B LEFT JOIN A?

✨ Show Answer

Answer: RIGHT JOIN preserves all rows from the right side; LEFT JOIN preserves all rows from the left side. Swap the operands and the "preserved" side stays the same — the result is row-for-row identical (only the column order in the SELECT may differ, which is cosmetic).

RIGHT JOIN ডান-পাশের সব row রাখে, LEFT JOIN বাঁ-পাশের। দু-টি table-এর order উল্টে দিলে "যেদিক রাখতে হবে" সেদিক একই থাকে — তাই row-for-row একই ফল আসে।
Bonus: produce, for each student, a comma-separated list of their course codes (use GROUP_CONCAT).

প্রতিটি student-এর course code-গুলি comma-দিয়ে এক row-তে দেখান।
✨ Show Answer
ans22.sql
```
SELECT s.name,
       COALESCE(GROUP_CONCAT(e.ccode,', '),'(none)') AS courses
FROM students s
LEFT JOIN enrolments e ON e.sid=s.id
GROUP BY s.id, s.name;
```

Summary — Module 16

A JOIN recombines rows from related tables. INNER keeps only matching rows; LEFT, RIGHT, and FULL OUTER additionally preserve unmatched rows from one or both sides. SELF JOIN uses two aliases on a single table to express same-table relationships, and CROSS JOIN is the raw Cartesian product behind every join. Avoid NATURAL JOIN in production — its meaning depends on column names that may change. Behind the scenes, the optimizer picks among nested-loop, hash, and sort-merge algorithms; SQLite leans heavily on indexed nested loops.

JOIN মানে আলাদা table-এর row-গুলোকে আবার একসাথে বসানো। INNER শুধু মেলানো row রাখে; LEFT/RIGHT/FULL মেলেনি এমন row-ও রাখে। SELF JOIN একই table-কে দু-বার ব্যবহার করে, CROSS JOIN সব সম্ভাব্য জোড়া তৈরি করে। NATURAL JOIN সংক্ষিপ্ত হলেও বিপজ্জনক — production code-এ এড়িয়ে চলুন। মূল algorithm — nested-loop, hash, sort-merge — পরবর্তী module-এ বিস্তারিত আলোচনা হবে।

Next Module → Subqueries & Nested Queries — query-র ভেতরে query।

Previous Next: Subqueries