MySQL में एक पूर्ण बाहरी जॉइन कैसे करें?




join outer-join (11)

मैं एक पूर्ण बाहरी करना चाहता हूं MySQL में शामिल हों। क्या यह संभव है? क्या एक पूर्ण बाहरी MySQL द्वारा समर्थित है?


MySQL में पूर्ण-बाहरी-जॉइन वाक्यविन्यास नहीं है। आपको बाएं जॉइन और दाएं जॉइन दोनों द्वारा अनुकरण करना होगा जैसा कि निम्न-

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

लेकिन MySQL में भी सही जॉइन वाक्यविन्यास नहीं है। माईएसक्ल के बाहरी सम्मिलन सरलीकरण के अनुसार, सही जुड़ाव क्वेरी में FROM और ON खंड में t1 और t2 को स्विच करके समकक्ष बाएं जुड़ने में परिवर्तित हो जाता है। इस प्रकार, MySQL क्वेरी ऑप्टिमाइज़र मूल क्वेरी को निम्न में अनुवादित करता है -

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id  
UNION
SELECT * FROM t2
LEFT JOIN t1 ON t2.id = t1.id

अब, मूल क्वेरी को लिखने में कोई हानि नहीं है, लेकिन कहें कि क्या आपने WHERE क्लॉज की भविष्यवाणी की है, जो कि before-join भविष्यवाणी है या एक ON क्लॉज पर अनुमान लगाता है, जो during-join अनुमान है, फिर आप शैतान को देखना चाहते हैं; जो विवरण में है।

MySQL क्वेरी ऑप्टिमाइज़र नियमित रूप से भविष्यवाणी करता है कि क्या वे शून्य-अस्वीकार हैं अब, यदि आपने सही जॉइन किया है, लेकिन जहां टी 1 से कॉलम पर अनुमान लगाया गया है, तो हो सकता है कि आपको शून्य-अस्वीकार परिदृश्य में भागने का जोखिम हो।

उदाहरण के लिए, ये निम्नलिखित क्वेरी - टी 1.आईडी = टी 2.आईडी पर टी 1 बाएं जॉइन टी 2 से चुनें * t1.col1 = 'someValue' यूनियन चुनें * टी 1 राइट से t1.id = t2.id पर t1 राइट में शामिल हों t1.col1 = 'someValue' को क्वेरी ऑप्टिमाइज़र द्वारा निम्नलिखित में अनुवादित किया गया है- चयन करें * टी 1 बाएं से जुड़ें t1.id = t2.id पर t1.col1 = 'someValue' यूनियन चुनें * t2 से जुड़ें t1.id = t1.id जहां t1.col1 = 'someValue' तो तालिकाओं का क्रम बदल गया है, लेकिन भविष्यवाणी अभी भी t1 पर लागू होती है, लेकिन टी 1 अब 'चालू' खंड में है। यदि t1.col1 को न्यूल कॉलम के रूप में परिभाषित किया गया है, तो यह क्वेरी शून्य-अस्वीकार कर दी जाएगी

कोई भी बाहरी-शामिल (बाएं, दाएं, पूर्ण) जो शून्य-अस्वीकार है, को MySQL द्वारा आंतरिक-शामिल में परिवर्तित किया जाता है।

इस प्रकार जिन परिणामों की आप उम्मीद कर रहे हैं वे MySQL वापस लौटने से पूरी तरह अलग हो सकते हैं। आप माइस्क्ल के अधिकार में शामिल होने के साथ एक बग सोच सकते हैं, लेकिन यह सही नहीं है। यह बस MySQL क्वेरी-ऑप्टिमाइज़र कैसे काम करता है। इसलिए जब डेवलपर इन-चार्ज को क्वेरी बना रहा है तो इन बारीकियों पर ध्यान देना होगा।


MySQL जैसे पूर्ण आउटर नामक किसी भी कमांड का समर्थन नहीं करता है। समर्थित तीन शामिल हैं इनर जॉइन, बाएं जॉइन एंड राइट जॉइन।

हालांकि आप कमांड यूनियन का उपयोग करके पूर्ण बाहरी भागीदारी को कार्यान्वित कर सकते हैं
(बाएं प्रश्न पूछें) यूनियन (सही प्रश्न पूछें)

उदाहरण के लिए, निम्नलिखित उदाहरण पर विचार करें जहां मेरे पास दो टेबल छात्र और अंक हैं। पूर्ण बाहरी शामिल करने के लिए, मैं निम्नलिखित कोड निष्पादित करूंगा:

SELECT * FROM students  
LEFT JOIN marks   
ON students.id = marks.id  
UNION ALL  
SELECT * FROM students 
RIGHT JOIN marks  
ON students.id = marks.id;

इस समाधान के बारे में आपने क्या कहा?

SELECT t1.*, t2.*
FROM table1 t1
INNER JOIN table2 t2 
ON 1=1;

SELECT
    a.name,
    b.title
FROM
    author AS a
LEFT JOIN
    book AS b
    ON a.id = b.author_id
UNION
SELECT
    a.name,
    b.title
FROM
    author AS a
RIGHT JOIN
    book AS b
    ON a.id = b.author_id

मैं प्रतिक्रिया को ठीक करता हूं, और कार्यों में सभी पंक्तियां शामिल हैं (पावेल लेकिक की प्रतिक्रिया के आधार पर)

    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    WHERE b.`key` is null
    )
    UNION ALL
    (
    SELECT a.* FROM tablea a
    LEFT JOIN tableb b ON a.`key` = b.key
    where  a.`key` = b.`key`
    )
    UNION ALL
    (
    SELECT b.* FROM tablea a
    right JOIN tableb b ON b.`key` = a.key
    WHERE a.`key` is null
    );

उपर्युक्त उत्तरों में से कोई भी वास्तव में सही नहीं है, क्योंकि डुप्लिकेट मान होने पर वे अर्थशास्त्र का पालन नहीं करते हैं।

एक प्रश्न के लिए (इस duplicate ):

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.Name = t2.Name;

सही समकक्ष है:

SELECT t1.*, t2.*
FROM (SELECT name FROM t1 UNION  -- This is intentionally UNION to remove duplicates
      SELECT name FROM t2
     ) n LEFT JOIN
     t1
     ON t1.name = n.name LEFT JOIN
     t2
     ON t2.name = n.name;

यदि आपको इसके लिए NULL मानों (जो भी आवश्यक हो) के साथ काम करने की आवश्यकता है, तो NULL -safe तुलना ऑपरेटर का उपयोग करें, <=> बजाय =


SQLite में आपको यह करना चाहिए:

SELECT * 
FROM leftTable lt 
LEFT JOIN rightTable rt ON lt.id = rt.lrid 
UNION
SELECT lt.*, rl.*  -- To match column set
FROM rightTable rt 
LEFT JOIN  leftTable lt ON lt.id = rt.lrid

उत्तर:

SELECT * FROM t1 FULL OUTER JOIN t2 ON t1.id = t2.id;

निम्नानुसार पुनर्निर्मित किया जा सकता है:

 SELECT t1.*, t2.* 
 FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp
 LEFT JOIN t1 ON t1.id = tmp.id
 LEFT JOIN t2 ON t2.id = tmp.id;

यूनियन या यूनियन का उपयोग करके सभी उत्तरों में एज केस शामिल नहीं होता है जहां बेस टेबल में डुप्लीकेट प्रविष्टियां होती हैं।

स्पष्टीकरण:

एक बढ़त मामला है कि एक यूनियन या यूनियन सभी शामिल नहीं हो सकते हैं। हम mysql पर इसका परीक्षण नहीं कर सकते क्योंकि यह पूर्ण बाहरी जॉइन का समर्थन नहीं करता है, लेकिन हम इसे ऐसे डेटाबेस पर चित्रित कर सकते हैं जो इसका समर्थन करता है:

 WITH cte_t1 AS
 (
       SELECT 1 AS id1
       UNION ALL SELECT 2
       UNION ALL SELECT 5
       UNION ALL SELECT 6
       UNION ALL SELECT 6
 ),
cte_t2 AS
(
      SELECT 3 AS id2
      UNION ALL SELECT 4
      UNION ALL SELECT 5
      UNION ALL SELECT 6
      UNION ALL SELECT 6
)
SELECT  *  FROM  cte_t1 t1 FULL OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2;

This gives us this answer:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

यूनियन समाधान:

SELECT  * FROM  cte_t1 t1 LEFT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2
UNION    
SELECT  * FROM cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

एक गलत जवाब देता है:

 id1  id2
NULL  3
NULL  4
1  NULL
2  NULL
5  5
6  6

यूनियन सभी समाधान:

SELECT  * FROM cte_t1 t1 LEFT OUTER join cte_t2 t2 ON t1.id1 = t2.id2
UNION ALL
SELECT  * FROM  cte_t1 t1 RIGHT OUTER JOIN cte_t2 t2 ON t1.id1 = t2.id2

भी गलत है

id1  id2
1  NULL
2  NULL
5  5
6  6
6  6
6  6
6  6
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

जबकि यह प्रश्न है:

SELECT t1.*, t2.*
FROM (SELECT * FROM t1 UNION SELECT name FROM t2) tmp 
LEFT JOIN t1 ON t1.id = tmp.id 
LEFT JOIN t2 ON t2.id = tmp.id;

निम्नलिखित देता है:

id1  id2
1  NULL
2  NULL
NULL  3
NULL  4
5  5
6  6
6  6
6  6
6  6

आदेश अलग है, लेकिन अन्यथा सही उत्तर से मेल खाता है।


यह भी संभव है, लेकिन आपको चुनिंदा क्षेत्र के नामों का उल्लेख करना होगा।

SELECT t1.name, t2.name FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT t1.name, t2.name FROM t2
LEFT JOIN t1 ON t1.id = t2.id

अधिक स्पष्टता के लिए संशोधित shA.t की क्वेरी:

-- t1 left join t2
SELECT t1.value, t2.value
FROM t1 LEFT JOIN t2 ON t1.value = t2.value   

    UNION ALL -- include duplicates

-- t1 right exclude join t2 (records found only in t2)
SELECT t1.value, t2.value
FROM t1 RIGHT JOIN t2 ON t1.value = t2.value
WHERE t2.value IS NULL 

आपके पास MySQL पर पूर्ण जॉइन नहीं हैं, लेकिन आप निश्चित रूप से उनका अनुकरण कर सकते हैं

इस SO प्रश्न से लिखे गए कोड नमूने के लिए आपके पास है:

दो टेबल टी 1, टी 2 के साथ:

SELECT * FROM t1
LEFT JOIN t2 ON t1.id = t2.id
UNION
SELECT * FROM t1
RIGHT JOIN t2 ON t1.id = t2.id

उपर्युक्त प्रश्न विशेष मामलों के लिए काम करता है जहां एक पूर्ण बाहरी जॉइन ऑपरेशन किसी भी डुप्लिकेट पंक्तियों का उत्पादन नहीं करेगा। उपरोक्त क्वेरी क्वेरी पैटर्न द्वारा प्रस्तुत डुप्लिकेट पंक्तियों को हटाने के लिए UNION सेट ऑपरेटर पर निर्भर करती है। हम दूसरी क्वेरी के लिए एंटी-जॉइन पैटर्न का उपयोग करके डुप्लिकेट पंक्तियों को शुरू करने से बच सकते हैं, और फिर दो सेटों को गठबंधन करने के लिए यूनियन ऑल सेट ऑपरेटर का उपयोग कर सकते हैं। अधिक सामान्य मामले में, जहां एक पूर्ण बाहरी जॉइन डुप्लिकेट पंक्तियां वापस कर देगा, हम यह कर सकते हैं:

  SELECT * FROM t1
  LEFT JOIN t2 ON t1.id = t2.id
  UNION ALL
  SELECT * FROM t1
  RIGHT JOIN t2 ON t1.id = t2.id
  WHERE t1.id IS NULL




full-outer-join