JOIN — দুই table একসাথে

JOIN choice business question-এর উপর নির্ভর করে — purely technical না।

INNER JOIN-এর situation:

• "Completed orders + valid customer" — orphan order বা customer-হীন record বাদ চাই।
• Reporting where missing data = data quality problem (raise alert)।
• Aggregations where NULL inflate metric — যেমন "avg revenue per active customer"।
• Performance critical — INNER সাধারণত LEFT-এর চেয়ে দ্রুত।

LEFT JOIN-এর situation:

• "All customers with their orders" — অর্ডার না করা গ্রাহকও দেখতে চাই (churn)।
• "All products with sales count" — না-বিক্রি হওয়া পণ্যও important (dead stock)।
• Funnel analysis — drop-off identify করতে missing record দরকার।
• Data audit — orphan record খুঁজতে।

Hidden trap — accidental INNER:

-- ভুল: WHERE-এ NULL filter
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.order_date >= '2024-01-01';
-- এটি actually INNER হয়ে যায় কারণ
-- order-হীন customer-এর o.order_date NULL → WHERE-এ false

সঠিক — ON-এ filter:

SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o
    ON c.id = o.customer_id
   AND o.order_date >= '2024-01-01';
-- এখন order-হীন customer থাকবে, NULL amount সহ

Decision framework:

1. "Missing record-এ আমি কী করব?" - ignore (INNER) vs preserve (LEFT)?
2. "Aggregation-এ NULL handle করব কীভাবে?" - COALESCE, FILTER।
3. "Stakeholder কোন number আশা করছেন?" - mismatch বের হয়ে গেলে অস্বস্তিকর প্রশ্ন।
4. "Data quality assumption সঠিক?" - foreign key constraints actually enforced?

Bangladesh-context: অনেক legacy database-এ FK constraint enforced নয়। তাই LEFT JOIN দিয়ে orphan ধরা — common audit pattern।

মূল উপলব্ধি: JOIN choice একটি business decision। "কোনটি দ্রুত" নয় — "কোনটি সঠিক"। সন্দেহ হলে LEFT দিয়ে শুরু — তারপর প্রয়োজন অনুযায়ী INNER।

"Row count exploded" — interview question ও real production bug দু'টোতেই।

কেন rows multiply:

• JOIN key এক table-এ unique, অন্যটিতে non-unique।
• "১ customer → ৫ orders" — JOIN-এর পর customer info ৫ বার duplicate।
• একে বলে fan-out। Many-to-many relationship-এ explosion বড়।

Numerical example:

• customers: ১,০০,০০০ row, প্রতিটি unique।
• orders: ৫,০০,০০০ row, প্রতি customer-এ গড়ে ৫।
• JOIN result: ৫,০০,০০০ row (orders-এর equal)।
• এখন SUM(customer.signup_date) = ভুল — কারণ প্রতি customer ৫ বার counted।

Detection strategies:

1. Pre-JOIN row count:

   SELECT COUNT(*) FROM customers; -- 100000
   SELECT COUNT(*) FROM orders;    -- 500000
   -- JOIN-এর পর if 500000 → orders-driven (LEFT/INNER from orders)
   -- যদি 1000000 → fan-out

2. DISTINCT key check:

   SELECT COUNT(*), COUNT(DISTINCT customer_id)
   FROM orders;
   -- দু'টি ভিন্ন হলে → many orders per customer → fan-out

3. JOIN cardinality test:

   SELECT customer_id, COUNT(*)
   FROM orders
   GROUP BY customer_id
   HAVING COUNT(*) > 1
   LIMIT 5;

4. Sanity check post-JOIN: aggregate metric — pre-JOIN-এর সাথে match করছে?

Prevention:

1. Aggregate first, then JOIN:

   -- ভুল: JOIN-এর পর SUM
   SELECT c.name, SUM(o.amount)
   FROM customers c
   JOIN orders o ON c.id = o.customer_id
   GROUP BY c.name;
   
   -- এটি ঠিক কাজ করে (GROUP BY-এ)
   -- কিন্তু মাঝে অপ্রয়োজনীয় rows
   -- alternate: pre-aggregate
   SELECT c.name, agg.total
   FROM customers c
   JOIN (
       SELECT customer_id, SUM(amount) AS total
       FROM orders
       GROUP BY customer_id
   ) agg ON c.id = agg.customer_id;

2. JOIN cardinality declaration: dbt-এর মতো tool-এ relationships test।
3. Documentation: "this JOIN expects 1:1" — comment।
4. Window function alternative: aggregate ছাড়াই row preserve করতে।
5. Schema design: compound primary key, unique constraint।

Real production story:

• একটি e-commerce-এ "monthly revenue" calculate। JOIN customers + orders + payments।
• Payment table-এ retry due multiple row per order। JOIN-এর পর revenue ৩x inflate।
• ৩ মাস ধরে CFO-কে inflated number দেওয়া হচ্ছিল — board-এ presentation, investor pitch।
• একজন junior দেখে detect করেন। পরবর্তীতে policy: "every analytical query peer-review আগে stakeholder-এ যাবে।"

মূল উপলব্ধি: JOIN-এর পর row count মিলছে কিনা সবসময় check করুন। "গণিত ঠিক — সংখ্যাটা ভুল" এর সবচেয়ে common উৎস। Senior analyst-রা এটি instinct।

Database design-এ এক বড় philosophical shift।

Traditional: normalize for OLTP

• OLTP (transaction processing): ছোট ছোট insert/update।
• Normalize → duplication কম, integrity strong।
• Disk expensive, RAM scarce।
• JOIN cost সহনীয়।

Modern: denormalize for OLAP

• OLAP (analytics): বড় aggregate query।
• Columnar storage — duplication compressed away।
• Distributed compute — JOIN-এ shuffle expensive।
• Storage cheap (BigQuery $20/TB/month), compute expensive।
• "Wide table" — JOIN পরিহার, scan optimize।

কেন wide table দ্রুত (modern context):

1. Columnar — শুধু needed column read।
2. JOIN avoidance — distributed shuffle এড়ানো।
3. Predicate pushdown — partition pruning সহজ।
4. Cache friendly — single scan।

Cost (যা trade off হয়):

• Storage: ১০-১০০x বেশি (compression-এর পরও)।
• Update complexity: এক fact-এ change → multiple row update।
• Schema evolution: column add কঠিন, migration painful।
• Data quality: single source of truth হারায়।

Modern hybrid pattern — Star Schema + Wide Mart:

• Raw layer: normalized — OLTP source অপরিবর্তিত।
• Staging: cleaned, lightly modeled।
• Mart: denormalized, business-ready, wide table।
• Tools: dbt, dataform — model build করে।

কোথায় normalize রাখুন:

• Production OLTP (orders, payments, inventory)।
• Frequent update with integrity (banking)।
• Limited storage (mobile, embedded)।

কোথায় denormalize:

• BI dashboard (Tableau, Power BI)।
• ML feature store।
• Pre-aggregated metrics (daily revenue)।
• Historical snapshot tables (SCD type-2)।

Bangladesh context:

• bKash/Daraz-এর modern stack: PostgreSQL OLTP → Snowflake/BigQuery analytics → wide mart।
• Smaller orgs: PostgreSQL-এ both OLTP + analytics। Materialized view দিয়ে compromise।
• dbt adoption-এ rapid growth — analytics engineer role emerge।

মূল উপলব্ধি: Normalize/denormalize religion নয়, tradeoff। Layered architecture — OLTP normalize, analytics denormalize — modern best practice। JOIN skill এখনো critical, কিন্তু "JOIN-heavy query" আজ smell।

NULL handling-এ SQL-এর সবচেয়ে nuanced জায়গা।

(১) NULL = NULL is NULL (not TRUE):

-- দু'টি table-এ customer_id NULL
-- INNER JOIN-এ এই row বাদ পড়বে
SELECT * FROM a INNER JOIN b ON a.customer_id = b.customer_id;
-- কারণ NULL = NULL → NULL (not match)

(২) NULL-safe equality (PostgreSQL IS NOT DISTINCT FROM):

-- NULL-NULL match করতে চাই
SELECT *
FROM a JOIN b
ON a.customer_id IS NOT DISTINCT FROM b.customer_id;

(৩) LEFT JOIN-এ NULL inflation:

SELECT c.name, AVG(o.amount)
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.name;
-- order-হীন customer-এ AVG(NULL) = NULL
-- COALESCE দিয়ে handle:
COALESCE(AVG(o.amount), 0)

(৪) WHERE clause "INNER-ifies" LEFT JOIN:

-- বিপজ্জনক:
SELECT c.name, o.amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
WHERE o.amount > 100;
-- এটি INNER হয়ে যায় — order-হীন customer NULL amount > 100 false

(৫) Aggregate functions ignore NULL:

SELECT
    COUNT(*),         -- সব row, NULL সহ
    COUNT(amount),    -- non-NULL only
    AVG(amount)       -- NULL ignore (denominator কম)
FROM orders;

(৬) NOT IN with NULL — silent bug:

-- যদি subquery-তে NULL থাকে
SELECT * FROM customers
WHERE customer_id NOT IN (SELECT id FROM blacklist);
-- blacklist.id-তে একটি NULL থাকলে → empty result!
-- কারণ "id NOT IN (1, 2, NULL)" → NULL → false
-- সমাধান: NOT EXISTS বা WHERE id IS NOT NULL

(৭) String comparison-এ NULL:

-- TRIM/LOWER NULL দিলে NULL
WHERE LOWER(name) = 'rahim'
-- name NULL → false → bypass

(৮) Arithmetic-এ NULL infectious:

SELECT price + tax FROM products;
-- tax NULL → result NULL
SELECT price + COALESCE(tax, 0) FROM products;
-- safer

(৯) ORDER BY NULL placement:

ORDER BY name ASC NULLS LAST  -- PostgreSQL/Oracle
ORDER BY name ASC, name IS NULL  -- MySQL workaround

Best practices:

• JOIN key column-এ NOT NULL constraint যেখানে possible।
• COALESCE/IFNULL/ISNULL সব JOIN/aggregate-এ explicit।
• NULL handling test case write করুন।
• Schema review-এ NULL behavior document।
• "Three-valued logic" (TRUE/FALSE/NULL) মাথায় রাখুন।

Bangladesh real-world:

• Customer phone NULL — registered without phone। JOIN-এ ঐ customer বাদ।
• Foreign student NID NULL — government data merge-এ silently drop।
• Ride-share-এ "rider rating" NULL (নতুন rider) — average inflate বা deflate।

মূল উপলব্ধি: SQL-এ NULL "অজানা" — boolean false নয়। Three-valued logic understand করলে JOIN-এর ৭০% bug avoid। Senior SQL practitioner-রা NULL handling-এ paranoid — এটাই professional discipline।