SQL जॉइन्स

यह क्यों ज़रूरी है

कई data defects tables के बीच रहते हैं: deleted customer की ओर इशारा करता order, बिना order का payment, या कोई import जिसने दूसरे system के ids लिख दिए। हर table अकेले में सही दिखती है; सिर्फ़ join टूटे references दिखाता है। पकड़ी न जाएँ तो ऐसी rows screens पर असल से कम items दिखाती हैं और reports को आपस में असहमत करती हैं।

Joins tester के लिए एक जाल भी हैं। Inner join बिना match वाली rows को बिना किसी error के छिपा देता है, और गलत join condition ठीक दिखने वाली पर गलत rows लौटाती है। सही join type और पहले से अनुमानित row count ही query को false pass की बजाय evidence बनाते हैं।

Join rows का मिलान कैसे करता है

आप FROM … JOIN में दोनों tables लिखते हैं और ON के बाद join condition, जो आम तौर पर वैसा relationship होती है जैसा keys and relationships वाले lesson में था: foreign key orders.customer_id उस primary key के बराबर होती है जिसे वह refer करती है, यहाँ customers.customer_id। Result में rows की हर वह जोड़ी होती है जिसके लिए condition true हो।

नीचे की tables में order 104 customer 9 को refer करता है, जो मौजूद नहीं: वैसा ही टूटा reference जिसे tester खोजता है। c और o aliases हैं, यानी tables के छोटे नाम।

-- customers                    orders
-- customer_id  full_name       order_id  customer_id  total
-- 1            Layla           101       1            250.00
-- 2            Omar            102       1             40.00
-- 3            Priya           103       2            120.00
--                              104       9             75.00

SELECT c.customer_id, c.full_name, o.order_id, o.total
FROM customers c
INNER JOIN orders o ON o.customer_id = c.customer_id
ORDER BY o.order_id;
-- 3 rows: customer 1 with orders 101 and 102, customer 2 with order 103

INNER, LEFT, RIGHT और FULL joins

चारों types matched जोड़ियाँ लौटाते हैं और सिर्फ़ बिना match वाली rows में अलग हैं। पहले लिखी गई customers left table है; orders right table है।

• INNER JOIN, या सिर्फ़ JOIN: केवल matched जोड़ियाँ, 3 rows। Customer 3, जिसके कोई orders नहीं, और order 104, जिसका customer गायब है, बिना किसी warning के गायब हो जाते हैं।
• LEFT JOIN (LEFT OUTER JOIN): left table की हर row, 4 rows। Customer 3 बना रहता है, order columns में NULL के साथ।
• RIGHT JOIN (RIGHT OUTER JOIN): right table की हर row, 4 rows। Order 104 बना रहता है, customer columns में NULL के साथ।
• FULL JOIN (FULL OUTER JOIN): दोनों tables की हर row, 5 rows। PostgreSQL, SQL Server और Oracle Database इसे support करते हैं; MySQL नहीं करता, और SQLite RIGHT व FULL joins version 3.39.0 से ही support करता है।

SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

-- customer_id  order_id  total
-- 1            101       250.00
-- 1            102        40.00
-- 2            103       120.00
-- 3            NULL      NULL     <- kept by LEFT JOIN, dropped by INNER JOIN

आज़माएँ: SQL फ़ॉर्मेटर आज़माएँ: SQL क्वेरी वैलिडेटर

Join condition लिखना

ज़्यादातर गलत join results ON clause से आते हैं, और गलत condition भी ठीक-ठाक दिखने वाली rows लौटाती है, इसलिए हर row count को अपेक्षित संख्या से मिलाएँ।

Outer join में extra filter की जगह मायने रखती है। ON में वह सिर्फ़ तय करता है कि match क्या गिना जाए, इसलिए left table की बिना match वाली rows बनी रहती हैं। WHERE में वह join के बाद चलता है, NULL वाली rows हटा देता है और left join को चुपचाप inner join बना देता है, जैसा नीचे की दो queries दिखाती हैं। IS NULL की जाँच इसका अपवाद है: वह ठीक NULL वाली rows ही रखती है, और अगले section का orphan check इसी पर टिका है।

• उन्हीं columns पर join करें जिन्हें relationship इस्तेमाल करता है: o.customer_id = c.customer_id। Condition o.order_id = c.customer_id भी चल जाती है, पर जहाँ भी दोनों numbers संयोग से बराबर हों, वहाँ असंबंधित rows जोड़ देती है।
• Composite key के हर column का इस्तेमाल करें: अगर customer ids सिर्फ़ किसी tenant (shared, multi-tenant system में एक client organisation) के अंदर unique हैं, तो tenant_id और customer_id दोनों पर join करें; वरना order दूसरे tenants के उसी number वाले customers से भी जुड़ जाएगा।
• One-to-many joins में दोहराव की उम्मीद रखें: customer 1 दो बार दिखता है क्योंकि उसके दो orders हैं, इसलिए rows गिनना orders गिनना है, customers नहीं।
• Condition छोड़ देने पर हर row हर row से जुड़ती है, यानी Cartesian product, जिसे CROSS JOIN जानबूझकर लौटाता है: 3 customers और 4 orders से 12 rows बनती हैं। यह FROM customers, orders (comma से अलग tables) में होता है जब कोई WHERE उन्हें नहीं जोड़ता, और MySQL में बिना ON वाले JOIN में भी; PostgreSQL ऐसा JOIN reject करता है।
• NULL किसी के बराबर नहीं होता, दूसरे NULL के भी नहीं, इसलिए जिस row के join column में NULL हो वह कभी match नहीं होती: inner join उसे हटा देता है, left join उसे रखता है।

-- Every customer, with their orders over 100.00 if any: 3 rows
SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o
  ON o.customer_id = c.customer_id AND o.total > 100.00;
-- 1 | 101 | 250.00     2 | 103 | 120.00     3 | NULL | NULL

-- The same filter in WHERE also removes customer 3: 2 rows
SELECT c.customer_id, o.order_id, o.total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.total > 100.00;
-- 1 | 101 | 250.00     2 | 103 | 120.00

LEFT JOIN … IS NULL से orphan rows ढूँढना

Orphan row वह child row है जिसकी foreign key ऐसे parent की ओर इशारा करती है जो मौजूद नहीं, जैसे order 104। Child table से शुरू करें, parent को left join करें, और सिर्फ़ वे rows रखें जिनसे कोई parent match नहीं हुआ। सही database शून्य rows लौटाता है; लौटी हर row bug report का evidence है। उलटी दिशा में यही ढाँचा ऐसे parents ढूँढता है जिनके ज़रूरी children नहीं हैं, जैसे बिना items वाला order।

IS NULL parent की primary key पर जाँचें, जो match होने पर कभी NULL नहीं होती; optional phone number जैसा nullable column उन customers के orders भी दिखा देगा जिनका phone नहीं है। जिन children की foreign key ख़ुद NULL है, वे भी आती हैं: column required हो तो यह defect है, वरना उन्हें AND o.customer_id IS NOT NULL से बाहर रखें।

नीचे के block की दूसरी query यही check NOT EXISTS से लिखती है। Brackets के अंदर की query, यानी subquery, हर order के लिए जाँची जाती है; NOT EXISTS तब true होता है जब उसे कोई row नहीं मिलती। SELECT 1 सिर्फ़ यह बताता है कि subquery के columns मायने नहीं रखते, मायने सिर्फ़ इसका है कि कोई row है या नहीं।

लागू foreign key constraint orphans को रोकता है, फिर भी practice में वे दिखते हैं:

• Constraint कभी घोषित ही नहीं हुआ, जो legacy schemas में आम है।
• Bulk import या migration के लिए constraints बंद किए गए।
• SQLite foreign keys तभी लागू करता है जब connection के लिए PRAGMA foreign_keys = ON set हो।
• Parent और child दो अलग services के databases में हैं।

-- Orders whose customer does not exist (expected: 0 rows)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;
-- returns order 104 with customer_id 9: an orphan

-- The same check written with NOT EXISTS
SELECT o.order_id, o.customer_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id
);

आज़माएँ: SQL INSERT जनरेटर आज़माएँ: SQL डेटासेट जनरेटर

Worked example: order history से गायब orders

पुराने shop system से migration के बाद एक support ticket आता है कि कुछ customers को "My orders" page पर अपने सारे orders नहीं दिखते; यह page orders को customers से inner join करता है। Tester को शक है कि कुछ orders ऐसे customers को refer करते हैं जो मौजूद नहीं।

• Expected: हर migrated order किसी मौजूदा customer का है, इसलिए orphan check 0 rows लौटाता है।
• Actual: 13 rows, सभी में customer_id 900000 से ऊपर, जो पुराने system की numbering है। Migration ने पुराने customer numbers को नए ids में map करने की बजाय सीधे copy कर दिया, इसलिए ये orders किसी मौजूदा customer के नहीं रहे: page का inner join इन्हें logged-in customer से कभी नहीं जोड़ पाता, और कहीं कोई error नहीं आता।
• Report: query, 13 order ids, expected 0 rows, संभावित कारण, और यह सवाल कि क्या foreign key constraint जोड़ा जाए।
• Fix के बाद confirmation testing: वही query 0 rows लौटाती है, और प्रभावित customer को page पर गायब orders दिखने लगते हैं।

-- Orphan check after the migration (expected: 0 rows)
SELECT o.order_id, o.customer_id, o.created_at
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL
ORDER BY o.order_id;

अक्सर पूछे जाने वाले सवाल

संदर्भ

• ISO/IEC 9075-1:2023 — Information technology — Database languages SQL — Part 1: Framework (SQL/Framework)ISO/IEC · 2023अंतरराष्ट्रीय SQL मानक का framework भाग, जिसे PostgreSQL, MySQL, SQLite आदि की बोलियाँ अलग-अलग हद तक लागू करती हैं।
• PostgreSQL Documentation: SQL SyntaxThe PostgreSQL Global Development GroupPostgreSQL में SQL की लेक्सिकल संरचना: आइडेंटिफ़ायर, क्वोटिंग, स्ट्रिंग कॉन्स्टेंट और एस्केपिंग।
• SQL As Understood By SQLiteSQLiteSQLite द्वारा स्वीकृत SQL व्याकरण, स्टेटमेंट दर स्टेटमेंट, सिंटैक्स डायग्राम के साथ।
• Constraints — PostgreSQL DocumentationPostgreSQL Global Development Groupcheck, not-null, unique, primary key, foreign key और exclusion constraints — वे नियम जो डेटाबेस लागू करता है और टेस्टर जाँचता है।