mysql - 如何在此查询中模拟完全外连接？

Question

显然 mySQL 不支持完全外连接，但这确实是我所需要的。我看过很多关于用联合来模拟它的博客文章和文章，但这会删除重复项。有人可以帮我从这里出去吗？

这是包含完整外部联接的查询(此处仅完成众多联接之一)；我如何将其转换为 mySQL 可以理解的内容？

SELECT DISTINCT n.title, nr.teaser, n.nid, DATE_FORMAT(FROM_UNIXTIME(n.created), '%M %e, %Y') AS date, f.filepath AS image,tn_img.tid as image_tid

FROM node n 

JOIN node_revisions nr ON n.nid = nr.nid 
LEFT JOIN content_field_related_images cfri ON (n.nid = cfri.nid AND cfri.delta = 0) 
LEFT JOIN content_field_att_file cfaf ON cfri.field_related_images_nid = cfaf.nid 
LEFT JOIN files f ON cfaf.field_att_file_fid = f.fid   
JOIN term_node tn2 ON n.nid = tn2.nid 
FULL OUTER JOIN term_node tn_img ON cfri.field_related_images_nid = tn_img.nid

WHERE n.status = 1 
AND n.type = 'article'   
AND nr.body LIKE '%kimberly-clark%' 
AND tn2.tid = 143

ORDER BY n.created DESC LIMIT 3

Answer 1

您基本上进行了 LEFT 和 RIGHT JOIN 的并集。

你实际上有一个有趣的问题，因为你还想将行数限制为 3。要解决这个问题，你需要

• 将“左”和“右”选择都限制为 3
• 然后在内联 View 中使用 UNION 的结果
• 然后再次将并集限制为 3

更新可悲的是，除非我弄错了，否则您不能直接在 UNION 中执行此操作，因此您需要在 UNION 之前添加另一层内联 View

UNION 内的 LIMITS 将提供一些性能优势，然后后面的限制将为您提供正确的结果。

SELECT title, 
   teaser, 
   nid, 
   DATE, 
   image, 
   image_tid 
FROM   (SELECT title, 
               teaser, 
               nid, 
               DATE, 
               image, 
               image_tid,
               created 
        FROM   (SELECT DISTINCT n.title, 
                                nr.teaser, 
                                n.nid, 
                                Date_format(From_unixtime(n.created), 
                                '%M %e, %Y') AS 
                                DATE, 
                                f.filepath 
                                AS 
                                image, 
                                tn_img.tid 
                                AS 
                                image_tid 
                                       , 
                                n.created 
                FROM   node n 
                       JOIN node_revisions nr 
                         ON n.nid = nr.nid 
                       LEFT JOIN content_field_related_images cfri 
                         ON ( n.nid = cfri.nid 
                              AND cfri.delta = 0 ) 
                       LEFT JOIN content_field_att_file cfaf 
                         ON cfri.field_related_images_nid = cfaf.nid 
                       LEFT JOIN files f 
                         ON cfaf.field_att_file_fid = f.fid 
                       JOIN term_node tn2 
                         ON n.nid = tn2.nid 
                       LEFT OUTER JOIN term_node tn_img 
                         ON cfri.field_related_images_nid = tn_img.nid 
                WHERE  n.status = 1 
                       AND n.TYPE = 'article' 
                       AND nr.body LIKE '%kimberly-clark%' 
                       AND tn2.tid = 143 
                ORDER  BY n.created DESC 
                LIMIT  3) tleft 
        UNION 
        SELECT title, 
               teaser, 
               nid, 
               DATE, 
               image, 
               image_tid,
               created  
        FROM   (SELECT DISTINCT n.title, 
                                nr.teaser, 
                                n.nid, 
                                Date_format(From_unixtime(n.created), 
                                '%M %e, %Y') AS 
                                DATE, 
                                f.filepath 
                                AS 
                                image, 
                                tn_img.tid 
                                AS 
                                image_tid 
                                       , 
                                n.created 
                FROM   node n 
                       JOIN node_revisions nr 
                         ON n.nid = nr.nid 
                       LEFT JOIN content_field_related_images cfri 
                         ON ( n.nid = cfri.nid 
                              AND cfri.delta = 0 ) 
                       LEFT JOIN content_field_att_file cfaf 
                         ON cfri.field_related_images_nid = cfaf.nid 
                       LEFT JOIN files f 
                         ON cfaf.field_att_file_fid = f.fid 
                       JOIN term_node tn2 
                         ON n.nid = tn2.nid 
                       RIGHT OUTER JOIN term_node tn_img 
                         ON cfri.field_related_images_nid = tn_img.nid 
                WHERE  n.status = 1 
                       AND n.TYPE = 'article' 
                       AND nr.body LIKE '%kimberly-clark%' 
                       AND tn2.tid = 143 
                ORDER  BY n.created DESC 
                LIMIT  3) tright) t 
ORDER  BY created DESC 
LIMIT  3 

更新使用 spencer7593 和 ypercube 建议，这是一种使用两个 UNION ALL 语句且没有内联 View 的替代方法。

SELECT DISTINCT n.created, 
                n.title, 
                nr.teaser, 
                n.nid, 
                Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE, 
                f.filepath                                         AS image, 
                tn_img.tid                                         AS image_tid 
FROM   node n 
       JOIN node_revisions nr 
         ON n.nid = nr.nid 
       LEFT JOIN content_field_related_images cfri 
         ON ( n.nid = cfri.nid 
              AND cfri.delta = 0 ) 
       LEFT JOIN content_field_att_file cfaf 
         ON cfri.field_related_images_nid = cfaf.nid 
       LEFT JOIN files f 
         ON cfaf.field_att_file_fid = f.fid 
       JOIN term_node tn2 
         ON n.nid = tn2.nid 
       LEFT OUTER JOIN term_node tn_img 
         ON cfri.field_related_images_nid = tn_img.nid 
WHERE  n.status = 1 
       AND n.TYPE = 'article' 
       AND nr.body LIKE '%kimberly-clark%' 
       AND tn2.tid = 143 

UNION ALL 
SELECT DISTINCT n.created, 
                n.title, 
                nr.teaser, 
                n.nid, 
                Date_format(From_unixtime(n.created), '%M %e, %Y') AS DATE, 
                f.filepath                                         AS image, 
                tn_img.tid                                         AS image_tid 
FROM   node n 
       JOIN node_revisions nr 
         ON n.nid = nr.nid 
       LEFT JOIN content_field_related_images cfri 
         ON ( n.nid = cfri.nid 
              AND cfri.delta = 0 ) 
       LEFT JOIN content_field_att_file cfaf 
         ON cfri.field_related_images_nid = cfaf.nid 
       LEFT JOIN files f 
         ON cfaf.field_att_file_fid = f.fid 
       JOIN term_node tn2 
         ON n.nid = tn2.nid 
       RIGHT JOIN term_node tn_img 
         ON cfri.field_related_images_nid = tn_img.nid 
WHERE  n.status = 1 
       AND n.TYPE = 'article' 
       AND nr.body LIKE '%kimberly-clark%' 
       AND tn2.tid = 143 
       AND cfri.field_related_images_nid IS NULL 
ORDER  BY 1 DESC 
LIMIT 
3