我应该加入还是应该联合

I have four different tables I am trying to query on, the first table is where I will be doing most of the querying, but if there is no match in car I am to look in other fields in the other tables to see if there is a match from a VIN parameter.

我有四个不同的表我想查询,第一个表是我最会做的查询,但是如果没有匹配的车我看着在其他领域的其他表是否有VIN的匹配参数。

Example:

例子:

Select 
    c.id,
    c.VIN,
    c.uniqueNumber,
    c.anotheruniqueNumber
FROM Cars c, Boat b
WHERE
    c.VIN = @VIN(parameter),
    b.SerialNumber = @VIN

Now say that I have no match in Cars, but there is a match in Boat, how would I be able to pull the matching Boat record vs the car record? I have tried to JOIN the tables, but the tables have no unique identifier to reference the other table.

现在假设我在车里没有比赛，但是在船里有比赛，我怎么能把匹配的船记录和赛车记录扯到一起?我尝试加入表，但是表没有唯一标识符来引用另一个表。

I am trying to figure out what is the best way to search all the tables off of a parameter but with the least amount of code. I thought about doing UNION ALL, but not sure if that what I really want for this situation, seeing as the number of records could get extremely large.

我试图找出用最少的代码搜索所有表的最佳方法。我想过做联盟，但不确定这是否是我真正想要的，因为记录的数量会变得非常大。

I am currently using SQL Server 2012. Thanks in advance!

我目前正在使用SQL Server 2012。提前谢谢!

UPDATED:

更新:

CAR table
ID  VIN               UniqueIdentifier      AnotherUniqueIdentifier
1   2002034434        HH54545445            2016-A23
2   2002035555        TT4242424242          2016-A24
3   1999034534        AGH0000034            2016-A25


BOAT table
ID  SerialNumber    Miscellaneous
1   32424234243     545454545445
2   65656565656     FF24242424242
3   20023232323     AGH333333333

Expected Result if @VIN parameter matches a Boat identifier:

如果@VIN参数与Boat标识符匹配，则期望结果为:

BOAT
ID  SerialNumber    Miscellaneous
2   65656565656     FF24242424242

1 个解决方案

#1

Some sort of union all might be the best approach -- at least the fastest with the right indexes:

某种联合可能是最好的方法——至少是用正确的索引最快的方法:

Select c.id, c.VIN, c.uniqueNumber, c.anotheruniqueNumber
from Cars c
where c.VIN = @VIN
union all
select b.id, b.VIN, b.uniqueNumber, b.anotheruniqueNumber
from Boats b
where b.VIN = @VIN and
      not exists (select 1 from Cars C where c.VIN = @VIN);

This assumes that you have the corresponding columns in each of the tables (which your question implies is true).

这假定您在每个表中都有相应的列(您的问题暗示这是正确的)。

The chain of not exists can get longer as you add more entity types. A simple way around is to do sorting instead -- assuming you want only one row:

随着添加更多的实体类型，不存在链可能会变得更长。一种简单的方法是排序——假设你只想要一行:

select top 1 x.*
from (Select c.id, c.VIN, c.uniqueNumber, c.anotheruniqueNumber, 1 as priority
      from Cars c
      where c.VIN = @VIN
      union all
      select b.id, b.VIN, b.uniqueNumber, b.anotheruniqueNumber, 2 as priority
      from Boats b
      where b.VIN = @VIN 
     ) x
order by priority;

There is a slight overhead for the order by. But frankly speaking, ordering 1-4 rows is trivial from a performance perspective.

这批订货要付一点费用。但是坦率地说，从性能的角度来看，为1-4行排序并不重要。

#1