UNION से MySQL में FULL OUTER JOIN

MySQL में FULL OUTER JOIN Keyword नहीं

Full outer result में matched pairs, left-only rows और right-only rows होती हैं। MySQL INNER, LEFT और RIGHT joins देता है, direct FULL OUTER JOIN clause नहीं। Keyword लिखने पर syntax error होगा; set-operation pattern चाहिए।

Expected Full Result Define करें

School lab में पाँच students और तीन classes हैं:

• चार students class 10 या 20 से match होते हैं।
• Vihaan left-only है क्योंकि class_id NULL है।
• XI-A right-only है क्योंकि कोई student class_id 30 use नहीं करता।

Full result में छह rows होनी चाहिए: चार matched + one student-only + one class-only। यह prediction emulation को testable बनाती है।

Correct LEFT JOIN + UNION ALL Pattern

SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM join_students AS s
LEFT JOIN classes AS c
  ON c.class_id = s.class_id

UNION ALL

SELECT s.student_id, s.student_name,
       c.class_id, c.class_name
FROM classes AS c
LEFT JOIN join_students AS s
  ON s.class_id = c.class_id
WHERE s.student_id IS NULL;

First branch हर student और उसका class match देती है। Second preservation reverse करती है, पर WHERE केवल वे class rows रखता है जिनका student नहीं मिला। Matched pairs दूसरी बार append नहीं होते।

Verified Six-Row Output

Display order चाहिए तो set operation को derived table में wrap करके final result sort करें:

SELECT *
FROM (
  SELECT s.student_id, s.student_name,
         c.class_id, c.class_name
  FROM join_students AS s
  LEFT JOIN classes AS c
    ON c.class_id = s.class_id
  UNION ALL
  SELECT s.student_id, s.student_name,
         c.class_id, c.class_name
  FROM classes AS c
  LEFT JOIN join_students AS s
    ON s.class_id = c.class_id
  WHERE s.student_id IS NULL
) AS full_result
ORDER BY COALESCE(class_id, 999999), student_id;

Naive UNION क्यों नहीं?

-- Common but weaker pattern:
left_side_with_matches
UNION
right_side_with_matches

UNION पूरे set में duplicate projected rows हटाता है। Selected columns keys न हों तो legitimate duplicates छिप सकते हैं और distinct processing work बढ़ता है। Anti-match filter + UNION ALL actual logic स्पष्ट करता है—पहले all left rows, फिर केवल missing right rows।

Useful Counts और Variants

Row origin label करें

SELECT CASE
         WHEN student_id IS NULL THEN 'class_only'
         WHEN class_id IS NULL THEN 'student_only'
         ELSE 'matched'
       END AS row_status,
       COUNT(*) AS rows_count
FROM (
  SELECT s.student_id, c.class_id
  FROM join_students AS s
  LEFT JOIN classes AS c
    ON c.class_id = s.class_id
  UNION ALL
  SELECT s.student_id, c.class_id
  FROM classes AS c
  LEFT JOIN join_students AS s
    ON s.class_id = c.class_id
  WHERE s.student_id IS NULL
) AS x
GROUP BY row_status;

Different match rule या composite key हो तो complete predicate दोनों branches में repeat करें। Left-only, right-only और multiple-match data से test करें।

Safety Checklist और अभ्यास

1. Confirm करें कि MySQL में direct FULL syntax नहीं, emulation चाहिए।
2. Absence test के लिए non-nullable key चुनें।
3. Exact relationship दोनों branches में repeat करें।
4. UNION ALL लें और branch two में only unmatched rows रखें।
5. Matched, left-only और right-only counts separately verify करें।
6. ORDER BY final combined result पर लगाएँ।

अभ्यास: no-student class 40 जोड़कर seven rows predict करें; फिर class 30 में assigned student जोड़ें और origin counts का change समझाएँ।

Official संदर्भ

• MySQL 8.4 Reference Manual: JOIN Clause
• MySQL 8.4: Outer Join Optimization
• MySQL 8.4: EXPLAIN Statement

Syntax और optimizer notes official MySQL 8.4 manual से जाँचे गए हैं। अपने schema और data पर plan तथा row count अवश्य test करें।