UNION से MySQL में FULL OUTER JOIN

FULL OUTER JOIN का असली मतलब

कल्पना कीजिए कागज़ पर दो lists हैं। एक students की, दूसरी classes की। FULL OUTER JOIN का मतलब है — आप बैठकर एक ऐसी combined शीट बनाते हैं जिसमें सब कुछ दिखे: हर वह student जो किसी class में बैठा है, वे students भी जिनकी अभी कोई class नहीं, और वे classes भी जिनमें अभी कोई student नहीं। कोई भी page से बाहर नहीं छूटता।

पूरी बात यही है। सामान्य (inner) join केवल matching rows दिखाता है — दोनों तरफ़ के बचे-खुचे लोग गायब हो जाते हैं। LEFT JOIN सारे students रखता है पर खाली classes छोड़ देता है। FULL OUTER JOIN किसी को नहीं छोड़ता: matched rows, left-only rows और right-only rows सब एक साथ आते हैं।

MySQL में FULL OUTER JOIN क्यों नहीं है

यहीं लोग अटकते हैं: MySQL में FULL OUTER JOIN keyword है ही नहीं। PostgreSQL और SQL Server जैसे databases में यह है, पर MySQL ने कभी नहीं जोड़ा। लिखने पर MySQL सीधे error दे देता है।

SELECT * FROM students
FULL OUTER JOIN classes
  ON students.class_id = classes.class_id;

तो एक keyword की जगह हम वही result दो हिस्सों से बनाते हैं जिन्हें MySQL समझता है: एक LEFT JOIN और एक UNION ALL। एक बार pattern समझ आ गया तो हर जगह आसानी से दोहरा सकते हैं।

हमारी sample tables

चलिए एक छोटा, भरोसेमंद उदाहरण लेते हैं ताकि output हाथ से जाँचना आसान हो। दो tables: students और classes।

CREATE TABLE classes (
    class_id   INT PRIMARY KEY,
    class_name VARCHAR(30)
);

CREATE TABLE students (
    student_id   INT PRIMARY KEY,
    student_name VARCHAR(30),
    class_id     INT   -- assign न हो तो NULL
);

INSERT INTO classes VALUES
(10,'X-A'), (20,'X-B'), (30,'XI-A');

INSERT INTO students VALUES
(1,'Aarav',10),
(2,'Diya',20),
(3,'Kabir',10),
(4,'Meera',20),
(5,'Vihaan',NULL);   -- अभी किसी class में नहीं

Data ध्यान से पढ़िए, क्योंकि इसमें ठीक वही दो दिलचस्प cases हैं:

• Vihaan एक student है जिसका class_id = NULL है — यह left-only row है (बिना class वाला student)।
• XI-A (class 30) की तरफ़ कोई student इशारा नहीं करता — यह right-only row है (बिना student वाली class)।

सही full outer join को ये दोनों दिखाने ही होंगे, साथ में चार सामान्य matches भी। यानी हमें पहले से पता है कि answer में 6 rows होनी चाहिए। पहले row count का अंदाज़ा लगाना बाद में query जाँचने का सबसे आसान तरीका है।

LEFT JOIN + UNION ALL वाला तरीका

Recipe के दो हिस्से हैं। पहला हिस्सा हर student उठाता है (match हो या न हो)। दूसरा हिस्सा हर वह class उठाता है जो पीछे छूट गई, और केवल वही।

-- हिस्सा 1: हर student, match होने पर class भी
SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM students AS s
LEFT JOIN classes AS c
       ON c.class_id = s.class_id

UNION ALL

-- हिस्सा 2: केवल वे classes जिनसे कोई student match नहीं हुआ
SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM classes AS c
LEFT JOIN students AS s
       ON s.class_id = c.class_id
WHERE s.student_id IS NULL;

जादू उस आख़िरी लाइन में है: WHERE s.student_id IS NULL। दूसरे हिस्से में हम classes से शुरू करते हैं और students जोड़ने की कोशिश करते हैं। जिस class को पहले ही कोई student मिल गया, उसे यह filter हटा देता है — क्योंकि वे matches हम पहले हिस्से में गिन चुके हैं। बचती हैं तो केवल अकेली classes (XI-A)। यही चीज़ rows को दो बार आने से रोकती है।

पूरा output समझें

आख़िरी दो rows देखिए — इन्हीं के लिए तो यह सब किया। Vihaan की class columns NULL हैं (बिना class वाला student)। XI-A की student columns NULL हैं (बिना student वाली class)। ऊपर की चार rows सामान्य matches हैं। कुल छह rows, ठीक जैसा हमने अंदाज़ा लगाया था।

UNION बनाम UNION ALL (कौन सा लें)

दोनों एक result को दूसरे के ऊपर जोड़ते हैं, पर एक अहम फ़र्क है।

चूँकि हमारा WHERE ... IS NULL filter पहले ही पक्का कर देता है कि कोई row दोहरी नहीं होगी, UNION चलाना बस उन duplicates को ढूँढने में समय बर्बाद करेगा जो हो ही नहीं सकते। UNION ALL सही और तेज़ विकल्प है। सादा UNION तभी लें जब सच में duplicate rows की उम्मीद हो और उन्हें merge करना हो।

आम गलतियाँ

• FULL OUTER JOIN लिखकर चलने की उम्मीद करना। MySQL इसे सीधे reject कर देगा। इसे चालू करने वाली कोई छिपी setting नहीं है — keyword है ही नहीं।
• सादा UNION इस्तेमाल कर असली rows खो देना। अगर दो सचमुच अलग rows कुछ ही columns select करने पर एक जैसी दिखें, तो UNION उन्हें चुपचाप merge कर देता है और row count बदल जाता है।
• WHERE ... IS NULL filter भूल जाना। इसके बिना पहले हिस्से वाले matched pairs उल्टे हिस्से में दोबारा जुड़ जाते हैं और matches दो बार दिखते हैं।
• Primary key की जगह join key पर filter लगाना। अगर join column ख़ुद NULL हो सकता है, तो उसे NULL जाँचना ग़लत "no match" देगा। हमेशा जिस side को जाँच रहे हैं उसकी non-nullable primary key जाँचिए।

सारांश

• FULL OUTER JOIN सारे matches और दोनों tables की बची rows लौटाता है।
• MySQL में FULL OUTER JOIN keyword नहीं — इसे ख़ुद बनाना पड़ता है।
• Pattern है LEFT JOIN … UNION ALL … उल्टा LEFT JOIN साथ में WHERE key IS NULL।
• UNION ALL लें (UNION नहीं) — filter पहले ही duplicates रोक देता है, तो यह तेज़ है।
• पहले row count का अनुमान लगाइए; इससे query जाँचना बेहद आसान हो जाता है।

यह समझ आते ही बाकी joins आसान लगेंगे। आगे SELECT DISTINCT से duplicate values हटाना देखिए, या cross और self join lesson में basics दोहराइए।