代码之家 › 专栏 › 技术社区 › arcee123

大查询SELECT语句中的联接错误:将OR运算符与左联接一起使用

google-bigquery google-cloud-platform sql

arcee123 · 技术社区 · 6 年前

我有下面的select语句。我使用左连接将两个表推到一起。左连接应在两个条件下工作:

条件1:ATTOM\u ID。ATTOM ID是唯一标识符

任何其他条件都应导致NULL,因此使用左连接。如果两个条件中的任何一个都通过了,则应该发生连接,这就是为什么我希望在这里使用OR语句。

出于某种原因,googlebigquery不喜欢这个查询,因为我在其中有一个OR。我得到的错误是:

LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

下面是SQL语句。本声明的所有其他问题都有效。使用左连接和“OR”运算符是否有GBQ限制?谢谢。

  SELECT 
  Source, 
  FirstName, 
  LastName,
  MiddleName, 
  Gender, 
  Age, 
  DOB, 
  Address, 
  Address2,
  City, 
  State, 
  Zip, 
  Zip4, 
  TimeZone, 
  Income, 
  HomeValue, 
  Networth, 
  MaritalStatus, 
  IsRenter, 
  HasChildren, 
  CreditRating, 
  Investor, 
  LinesOfCredit, 
  InvestorRealEstate, 
  Traveler, 
  Pets, 
  MailResponder, 
  Charitable, 
  PolicalDonations, 
  PoliticalParty, 
  coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
  coalesce(P.GEOID, T.GEOID) as GEOID,
  Score,
  Score1,
  Score2,
  Score3,
  Score4,
  Score5,
  PropertyLatitude AS Latitude,
  PropertyLongitude AS Longitude
  FROM `db.ds.table1` P
 LEFT JOIN `db.ds.table2` T
ON 1 = 
CASE 
    WHEN (P.ATTOM_ID = T.ATTOM_ID) 
        THEN 1
    WHEN P.Zip = T. PropertyAddressZIP
            AND ( 
                    LOWER(P.LastName) = LOWER(T.DeedOwner1NameLast)
                    OR LOWER(P.LastName) = LOWER(T.PartyOwner1NameLast)
                )
            AND ( 
                    STRPOS(LOWER(P.Address), LOWER(T.PropertyAddressFull) ) > 0
                    OR STRPOS(LOWER(T.PropertyAddressFull), LOWER(P.Address) ) > 0 
                )
            AND IFNULL(T.PropertyAddressFull,'') != ''
        THEN 1
    ELSE 0 END

4 回复 | 直到 6 年前

digital.aaron 6 年前

也许是另一种方法?你能分头吃吗 OR 在你的 JOIN INNER JOIN UNION 他们在一起?

SELECT 
    Source, 
    FirstName, 
    LastName,
    MiddleName, 
    Gender, 
    Age, 
    DOB, 
    Address, 
    Address2,
    City, 
    State, 
    Zip, 
    Zip4, 
    TimeZone, 
    Income, 
    HomeValue, 
    Networth, 
    MaritalStatus, 
    IsRenter, 
    HasChildren, 
    CreditRating, 
    Investor, 
    LinesOfCredit, 
    InvestorRealEstate, 
    Traveler, 
    Pets, 
    MailResponder, 
    Charitable, 
    PolicalDonations, 
    PoliticalParty, 
    coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
    coalesce(P.GEOID, T.GEOID) as GEOID,
    Score,
    Score1,
    Score2,
    Score3,
    Score4,
    Score5,
    PropertyLatitude AS Latitude,
    PropertyLongitude AS Longitude
FROM `db.ds.Table1` P
INNER JOIN `db.ds.Table2` T ON (P.ATTOM_ID = T.ATTOM_ID) 

UNION

SELECT 
    Source, 
    FirstName, 
    LastName,
    MiddleName, 
    Gender, 
    Age, 
    DOB, 
    Address, 
    Address2,
    City, 
    State, 
    Zip, 
    Zip4, 
    TimeZone, 
    Income, 
    HomeValue, 
    Networth, 
    MaritalStatus, 
    IsRenter, 
    HasChildren, 
    CreditRating, 
    Investor, 
    LinesOfCredit, 
    InvestorRealEstate, 
    Traveler, 
    Pets, 
    MailResponder, 
    Charitable, 
    PolicalDonations, 
    PoliticalParty, 
    coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
    coalesce(P.GEOID, T.GEOID) as GEOID,
    Score,
    Score1,
    Score2,
    Score3,
    Score4,
    Score5,
    PropertyLatitude AS Latitude,
    PropertyLongitude AS Longitude
FROM `db.ds.Table1` P
INNER JOIN `db.ds.Table2` T ON P.Zip = T. PropertyAddressZIP
            AND ( 
                    LOWER(P.LastName) = LOWER(T.DeedOwner1NameLast)
                    OR LOWER(P.LastName) = LOWER(T.PartyOwner1NameLast)
                )
            AND ( 
                    STRPOS(LOWER(P.Address), LOWER(T.PropertyAddressFull) ) > 0
                    OR STRPOS(LOWER(T.PropertyAddressFull), LOWER(P.Address) ) > 0 
                )
            AND IFNULL(T.PropertyAddressFull,'') != ''

Mikhail Berlyant 6 年前

下面是您的问题的简化示例

#standardSQL
WITH `db.ds.Table1` AS (
  SELECT NULL id, '12345' zip, 'abc' name UNION ALL
  SELECT 2, '23456', 'vwu' UNION ALL
  SELECT 4 id, '12347' zip, 'abd' name 
), `db.ds.Table2` AS (
  SELECT 2 id, '12346' zip, 'xyz' name UNION ALL
  SELECT 3, '12345' zip, 'abc' name 
)
SELECT p, t FROM `db.ds.Table1` p
LEFT JOIN `db.ds.Table2` t
ON p.id = t.id OR p.zip = t.zip

它产生 Error: LEFT OUTER JOIN cannot be used without a condition that is an equality of fields from both sides of the join.

#standardSQL
WITH `db.ds.Table1` AS (
  SELECT NULL id, '12345' zip, 'abc' name UNION ALL
  SELECT 2, '23456', 'vwu' UNION ALL
  SELECT 4 id, '12347' zip, 'abd' name 
), `db.ds.Table2` AS (
  SELECT 2 id, '12346' zip, 'xyz' name UNION ALL
  SELECT 3, '12345' zip, 'abc' name 
)
SELECT 
  COALESCE(p.id, t.id) AS id,
  p.zip,
  p.name
FROM (
  SELECT ANY_VALUE(p) p , ANY_VALUE(IF(p.id = t.id OR p.zip = t.zip, t, NULL)) t
  FROM `db.ds.Table1` p
  CROSS JOIN `db.ds.Table2` t
  GROUP BY TO_JSON_STRING(p)
)

结果是

Row id  zip     name     
1   3   12345   abc  
2   2   23456   vwu  
3   4   12347   abd

希望,您可以通过这个来具体查询(应该是直接拷贝粘贴)

ravioli 6 年前

尝试将连接条件的最后一部分移动到WHERE子句:

SELECT 
  Source, 
  <lots_of_columns>
  FROM `db.ds.Table1` P
 LEFT JOIN `db.ds.Table2` T
 ON (P.ATTOM_ID = T.ATTOM_ID)
 OR (
   P.Zip = T. PropertyAddressZIP
  AND ( LOWER(P.LastName) = LOWER(T.DeedOwner1NameLast)
    OR LOWER(P.LastName) = LOWER(T.PartyOwner1NameLast))
  AND ( STRPOS(LOWER(P.Address), LOWER(T.PropertyAddressFull) ) > 0
    OR STRPOS(LOWER(T.PropertyAddressFull), LOWER(P.Address) ) > 0 )
  )
  WHERE IFNULL(T.PropertyAddressFull,'') != '';

这可能就是问题的根源,因为它不会引用联接中的两个表。

digital.aaron 6 年前

也许试着用一个 CASE

SELECT 
    Source, 
    FirstName, 
    LastName,
    MiddleName, 
    Gender, 
    Age, 
    DOB, 
    Address, 
    Address2,
    City, 
    State, 
    Zip, 
    Zip4, 
    TimeZone, 
    Income, 
    HomeValue, 
    Networth, 
    MaritalStatus, 
    IsRenter, 
    HasChildren, 
    CreditRating, 
    Investor, 
    LinesOfCredit, 
    InvestorRealEstate, 
    Traveler, 
    Pets, 
    MailResponder, 
    Charitable, 
    PolicalDonations, 
    PoliticalParty, 
    coalesce(P.ATTOM_ID, T.ATTOM_ID) as ATTOM_ID,
    coalesce(P.GEOID, T.GEOID) as GEOID,
    Score,
    Score1,
    Score2,
    Score3,
    Score4,
    Score5,
    PropertyLatitude AS Latitude,
    PropertyLongitude AS Longitude
FROM `db.ds.Table1` P
LEFT JOIN `db.ds.Table2` T ON 1 = 
CASE 
    WHEN (P.ATTOM_ID = T.ATTOM_ID) 
        THEN 1
    WHEN P.Zip = T. PropertyAddressZIP
            AND ( 
                    LOWER(P.LastName) = LOWER(T.DeedOwner1NameLast)
                    OR LOWER(P.LastName) = LOWER(T.PartyOwner1NameLast)
                )
            AND ( 
                    STRPOS(LOWER(P.Address), LOWER(T.PropertyAddressFull) ) > 0
                    OR STRPOS(LOWER(T.PropertyAddressFull), LOWER(P.Address) ) > 0 
                )
            AND IFNULL(T.PropertyAddressFull,'') != ''
        THEN 1
    ELSE 0 END