MySQL OUTER 加入 AS

Question

我非常擅长 SQL 语句，希望能得到一点帮助。我发现我需要对其中一些表使用 OUTER 联接，但 MySQL 没有该功能。

            SELECT validclick.CampaignName AS CampaignName, 
                validclick.Website AS Website, 
                validclick.RevClicks AS RevClicks, 
                validclick.Revenue AS Revenue, 
                validclick.TQ AS TQ,
                validclick.Source AS Source, 
                validclick.ScoredClicks AS ScoredClicks, 
                validclick.Searches AS Searches, 
                vci.Impressions AS Impressions, 
                (validclick.RevClicks / validclick.Searches * 100) AS CTR, 
                (IFNULL(yaho.Spend, 0) + IFNULL(facebook.Spend, 0) + IFNULL(google.Spend, 0)) AS Spend, 
                (IFNULL(yaho.PaidClicks, 0) + IFNULL(facebook.PaidClicks, 0) + IFNULL(google.PaidClicks, 0)) AS PaidClicks, 
                (validclick.Revenue -  
                    (IFNULL(yaho.Spend, 0) + IFNULL(facebook.Spend, 0) + IFNULL(google.Spend, 0))) AS Profit, 
                (validclick.TQ / validclick.ScoredClicks) AS ScoredTQ,  
                (validclick.TQ / validclick.RevClicks) AS UnscoredTQ, 
                (validclick.Revenue /  
                    (IFNULL(yaho.PaidClicks, 0) + IFNULL(facebook.PaidClicks, 0) + IFNULL(google.PaidClicks, 0))) AS RPI, 
                (validclick.Revenue / validclick.RevClicks) AS RPC,  
                ((IFNULL(yaho.Spend, 0) + IFNULL(facebook.Spend, 0) + IFNULL(google.Spend, 0)) / 
                    (IFNULL(yaho.PaidClicks, 0) + IFNULL(facebook.PaidClicks, 0) + IFNULL(google.PaidClicks, 0))) AS CPC, 
                ((validclick.Revenue -  
                    (IFNULL(yaho.Spend, 0) + IFNULL(facebook.Spend, 0) + IFNULL(google.Spend, 0))) / 
                    (IFNULL(yaho.Spend, 0) + IFNULL(facebook.Spend, 0) + IFNULL(google.Spend, 0)) * 100) AS ROI, 
                (validclick.RevClicks /  
                    (IFNULL(yaho.PaidClicks, 0) + IFNULL(facebook.PaidClicks, 0) + IFNULL(google.PaidClicks, 0)) * 100) AS Conversion 
            FROM 
                (SELECT camp.CampaignName AS CampaignName, 
                    MAX(camp.Website) AS Website, 
                    MAX(camp.Source) AS Source, 
                    SUM(vc.Clicks) AS RevClicks, 
                    SUM(vc.Revenue) AS Revenue, 
                    SUM(vc.TQ) AS TQ, 
                    SUM(vc.ScoredClicks) AS ScoredClicks, 
                    SUM(vc.BiddedSearches) AS Searches 
                FROM 
                    (SELECT AffID, 
                        MAX(CampaignName) AS CampaignName, 
                        Website, 
                        MAX(Source) AS Source 
                    FROM campaigns 

                    GROUP BY AffID) AS camp 
                JOIN 
                    (SELECT AffID, 
                        SUM(Clicks) AS Clicks, 
                        SUM(AffiliateRevenue) AS Revenue, 
                        SUM(BiddedSearches) AS BiddedSearches, 
                        SUM(CASE WHEN TQ > 0 THEN (TQ * Clicks) ELSE NULL END) AS TQ, 
                        SUM(CASE WHEN TQ > 0 THEN Clicks ELSE NULL END) AS ScoredClicks 
                    FROM validclickvc 
                    WHERE Date BETWEEN '2018-05-06' AND '2018-05-07' 
                    GROUP BY AffID) AS vc 
                ON vc.AffID = camp.AffID 
                GROUP BY camp.CampaignName) AS validclick 
            LEFT JOIN 
                (SELECT CampaignName, 
                    SUM(Spend) AS Spend,
                    SUM(OutboundClicks) AS PaidClicks 
                FROM facebookads 
                WHERE Date BETWEEN '2018-05-06' AND '2018-05-07' 
                GROUP BY CampaignName) AS facebook 
            ON validclick.CampaignName = facebook.CampaignName 
            LEFT JOIN 
                (SELECT CampaignName, 
                    SUM(Spend) AS Spend,
                    SUM(Clicks) AS PaidClicks 
                FROM yahoo 
                WHERE Date BETWEEN '2018-05-06' AND '2018-05-07' 
                GROUP BY CampaignName) AS yaho 
            ON validclick.CampaignName = yaho.CampaignName 
            LEFT JOIN 
                (SELECT CampaignName, 
                    SUM(Cost) AS Spend,
                    SUM(Clicks) AS PaidClicks 
                FROM adwords 
                WHERE Date BETWEEN '2018-05-06' AND '2018-05-07' 
                GROUP BY CampaignName) AS google 
            ON validclick.CampaignName = google.CampaignName 
            LEFT JOIN 
                (SELECT camp.CampaignName AS CampaignName, 
                    SUM(vc.Impressions) AS Impressions 
                FROM 
                    (SELECT AffID, 
                        MAX(CampaignName) AS CampaignName 
                    FROM campaigns 

                    GROUP BY AffID) AS camp 
                JOIN 
                    (SELECT ty AS AffID, 
                        COUNT(DISTINCT(id)) AS Impressions 
                    FROM validclickimpressions 
                    WHERE ts BETWEEN '2018-05-06 00:00' AND '2018-05-07 23:59' 
                    GROUP BY AffID) AS vc 
                ON vc.AffID = camp.AffID 
                GROUP BY camp.CampaignName) AS vci 
            ON validclick.CampaignName = vci.CampaignName 
            ORDER BY CampaignName;

我知道这可能不是最有效的 MySQL 语句，但我们很快就会改变事物的结构，所以我没什么大不了的。现在我需要一些方法来使用 OUTER join，或者执行 LEFT JOIN UNTION RIGHT JOIN 但使用别名。我需要完全加入的表(别名)是 vci、facebook、google 和 yaho。关于如何解决这个问题的任何想法？

Answer 1

MySQL 不支持FULL [OUTER] JOIN。

我们可以使用两个单独的选择来模拟 MySQL 中的完全外部连接，并合并结果。外连接和反连接的模式。

 -- outer join (all rows from a along with matching rows from b) 
 SELECT ... FROM a LEFT JOIN b ON ...

 UNION ALL

 -- anti-join (rows from b with no matching row in a) 
 SELECT ... FROM b LEFT JOIN a ON ... WHERE a.notnull_col IS NULL 

---

作为一个简单的演示，两个表a和b:

 table a   table b
      an        bn
 -------   -------
       1         2
       2         3
       3         5
       4         7

示例查询

  -- left outer join (rows in a with matching rows from b) 
  SELECT a.an, b.bn
    FROM a
    LEFT
    JOIN b
      ON b.bn = a.an

   UNION ALL

  -- anti-join (rows from b with no match in a)
  SELECT a.an, b.bn
    FROM b
    LEFT
    JOIN a 
      ON a.an = b.bn
   WHERE a.an IS NULL

应该返回这样的东西(没有 ORDER BY 子句，行的顺序是不确定的)

      an        bn
 -------   -------
       1    (NULL)
       2         2
       3         3
       4    (NULL)
  (NULL)         5
  (NULL)         7