tektiv tektiv - 6 months ago 9
SQL Question

Make a "LEFT UNION" query

I have several databases (

nobu
and
bu
) with exact same tables (one is just a back up of the other).

I need to get values from a table from both databases to join them with other tables then I obviously use an
UNION
. The thing is, some products have different names in the tables from both
bu
and
nobu
.

I then tried to select only one database about this table (I used
nobu
since it's the latest one), but I noticed that some products are not in
nobu
, but are actually in
bu
(which makes it not a backup anymore).

The part of the query in which I need this looks like this :

With this I get duplicates

... INNER JOIN (SELECT * FROM nobu.dbo.product UNION SELECT * FROM bu.dbo.product) AS product
ON [...] INNER JOIN (SELECT * FROM nobu.dbo.name UNION SELECT bu.dbo.name) AS name
ON product.key = name.id ...


With this I get some of the products with
NULL
name since it doesn't exist on
nobu


... INNER JOIN (SELECT * FROM nobu.dbo.product UNION SELECT * FROM bu.dbo.product) AS product
ON [...] INNER JOIN (SELECT * FROM nobu.dbo.name) AS name
ON product.key = name.id ...


I wanted to know if there is a way to perform a
LEFT UNION
or something like that, to get all the values from
nobu
, and if there is no data, take the ones from
bu
, without getting the duplicates (since they can have different names on both databases).

Answer

If only names have been changed and suggesting that table names is not a big table and will not create performance issues then this code below will do the job:

INNER JOIN (SELECT * FROM nobu.dbo.product UNION SELECT * FROM bu.dbo.product) AS product
ON [...] INNER JOIN (SELECT * FROM nobu.dbo.name UNION SELECT bu.dbo.name WHERE id NOT IN (SELECT id FROM nobu.dbo.name)) AS name
ON product.key = name.id
Comments