使用正确的外部联接来匹配来自两个不同数据库的记录

SQL 2005:

SQL 2005：

I am trying to create an outer join that will pull records from two different databases. My objective is to determine which records in database B don't have matching records in database A. When I tried running the query, it returned the error below. I am not sure how to get around this error:

我正在尝试创建一个外连接，它将从两个不同的数据库中提取记录。我的目标是确定数据库B中的哪些记录在数据库A中没有匹配的记录。当我尝试运行查询时，它返回了以下错误。我不知道如何解决这个错误：

'Tables or functions 'AssetCompType_EquipmentProperty_LinkTable' and 'TA-Reporting.dbo.AssetCompType_EquipmentProperty_LinkTable' have the same exposed names. Use correlation names to distinguish them.'

'表或函数'AssetCompType_EquipmentProperty_LinkTable'和'TA-Reporting.dbo.AssetCompType_EquipmentProperty_LinkTable'具有相同的公开名称。使用相关名来区分它们。

          select * 
            from AssetCompType_EquipmentProperty_LinkTable
right outer join [database A].dbo.AssetCompType_EquipmentProperty_LinkTable on
[database A].dbo.AssetCompType_EquipmentProperty_LinkTable.AssetCompTypeID=
[database B].dbo.AssetCompType_EquipmentProperty_LinkTable.AssetCompTypeID

3 个解决方案

#1

It appears you must differentiate between the tables. Try with:

您似乎必须区分表格。试试：

select * 
from AssetCompType_EquipmentProperty_LinkTable T1
  right outer join 
    [database A].dbo.AssetCompType_EquipmentProperty_LinkTable T2 
       on T1.AssetCompTypeID = T2.AssetCompTypeID

#2

Just assign aliases:

只需指定别名：

 select * 
            from AssetCompType_EquipmentProperty_LinkTable tbl_thisDB
right outer join [database A].dbo.AssetCompType_EquipmentProperty_LinkTable tbl_A on
tbl_A.AssetCompTypeID=
tbl_thisDB.AssetCompTypeID

#3

You need to use table aliases when JOINing tables with identical names, if not themselves:

在JOIN具有相同名称的表时，您需要使用表别名，如果不是自己：

          SELECT a.*, b.*
            FROM AssetCompType_EquipmentProperty_LinkTable a
RIGHT OUTER JOIN [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b ON a.AssetCompTypeID = b.AssetCompTypeID

It's necessary for the JOIN syntax alone, but additionally because you can't use SELECT * because of the likelihood of identical columns in the tables as well.

单独使用JOIN语法是必要的，但另外因为您不能使用SELECT *，因为表中的列也可能相同。

That said - to get the result you desire, you should consider:

那就是说 - 为了得到你想要的结果，你应该考虑：

Using NOT IN

---

SELECT b.*
  FROM [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b
 WHERE b.AssetCompTypeID NOT IN (SELECT a.AssetCompTypeID
                                   FROM AssetCompType_EquipmentProperty_LinkTable a)

Using NOT EXISTS

---

SELECT b.*
  FROM [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b
 WHERE NOT EXISTS (SELECT NULL
                     FROM AssetCompType_EquipmentProperty_LinkTable a
                    WHERE a.AssetCompTypeID = b.AssetCompTypeID) 

Using LEFT JOIN/IS NULL

---

   SELECT b.*
     FROM [database A].dbo.AssetCompType_EquipmentProperty_LinkTable b
LEFT JOIN AssetCompType_EquipmentProperty_LinkTable a ON a.AssetCompTypeID = b.AssetCompTypeID
    WHERE a.AssetCompTypeID IS NULL

Conclusion

---

Of the three options, the NOT IN and NOT EXISTS are equivalent - the LEFT JOIN/IS NULL is less efficient. See this article for more details.

在三个选项中，NOT IN和NOT EXISTS是等效的 - LEFT JOIN / IS NULL效率较低。有关详细信息，请参阅此文章。