Sql – how to union record from another table with only different id by “Union All”

sqlsql server

I have 2 tables with duplicate record including 'Id' field, and I need union them.
But I think [union all] is much more efficient than [union]
for the reason that it need not sorting.

Following is my sql:
SELECT *
FROM [dbo].[RealTime]
UNION ALL
SELECT *
FROM [dbo].[Query] a
WHERE NOT EXISTS(
   SELECT TOP 1 1
   FROM [dbo].[RealTime] b
   WHERE a.Id = b.Id
)
Will you have any suggestion or more elegant statement ?

Best Answer

Option 1 (yours): query dbo.RealTime first, then query dbo.Query, then for each row from dbo.Query, do a lookup on dbo.Realtime.

Option 2 (UNION): query dbo.RealTime, query dbo.Query, then remove duplicates.

Apart from the fact that these two options will give different results (the first option will retain duplicates obtained from dbo.RealTime and dbo.Query), I'd say Option 2 will most probably be less resource intensive since it doesn't have to query dbo.RealTime twice - unless (perhaps) dbo.RealTime is smaller than dbo.Query by an order of magnitude.

Related Solutions

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

SQL update from one Table to another based on a ID match

I believe an UPDATE FROM with a JOIN will help:

MS SQL

UPDATE
    Sales_Import
SET
    Sales_Import.AccountNumber = RAN.AccountNumber
FROM
    Sales_Import SI
INNER JOIN
    RetrieveAccountNumber RAN
ON 
    SI.LeadID = RAN.LeadID;

MySQL and MariaDB

UPDATE
    Sales_Import SI,
    RetrieveAccountNumber RAN
SET
    SI.AccountNumber = RAN.AccountNumber
WHERE
    SI.LeadID = RAN.LeadID;