CODEWITHSUNDEEP
sqlsql-serverjoinunion
Hemi81
Hemi81

Reputation: 588

Should I JOIN or should I UNION

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.

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!

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:

BOAT
ID  SerialNumber    Miscellaneous
2   65656565656     FF24242424242

Upvotes: 2

Views: 77

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

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.

Upvotes: 3

Related Questions