SQL View where value appears in one of two tables or both of them

Question

I have two tables, both with the same columns.

The first table contains original source data, the second contains revisions to that data OR values inserted where no original data was present.

The tables are to be used in a view where if the data has a revision that is shown - if not then the original source data is shown.

Normally this would be OK using a Left join but it is the inserted revisions that are getting me confused how to do this best for performance reasons.

The following sample should help explain better

DECLARE @t1 TABLE (TimeStamp datetime,Value int)
DECLARE @t2 TABLE (TimeStamp datetime,Value int)

INSERT INTO @t1 (TimeStamp,Value) VALUES ('2000-01-01',10)
INSERT INTO @t1 (TimeStamp,Value) VALUES ('2000-01-02',15)
INSERT INTO @t1 (TimeStamp,Value) VALUES ('2000-01-04',5)
INSERT INTO @t1 (TimeStamp,Value) VALUES ('2000-01-05',18)
INSERT INTO @t1 (TimeStamp,Value) VALUES ('2000-01-06',12)

INSERT INTO @t2 (TimeStamp,Value) VALUES ('2000-01-03',12)
INSERT INTO @t2 (TimeStamp,Value) VALUES ('2000-01-05',20)
INSERT INTO @t2 (TimeStamp,Value) VALUES ('2000-01-06',15)

--SELECT STATEMENT HERE

I need the output to be like:

TimeStamp               Value
----------------------- -----------
2000-01-01 00:00:00.000 10
2000-01-02 00:00:00.000 15
2000-01-03 00:00:00.000 12
2000-01-04 00:00:00.000 5
2000-01-05 00:00:00.000 20
2000-01-06 00:00:00.000 15

So the value for 3rd Jan is present, the values for 5th and 6th have been taken from @t2 and the values from @t1 are not present in the output.

I am using SQL Server 2005 should that make any difference

Answer 1

You could solve it with a union:

SELECT * FROM t1 
  WHERE NOT EXISTS (SELECT * FROM t2 WHERE t2.TimeStamp = t1.TimeStamp)
UNION
SELECT * FROM t2

Obviously, if the tables don't have identical structures, then you need to explicitly list columns (and it's probably good practice to do so anyway).

Answer 2

SELECT 
    isnull(tbl2.timestamp, tbl1.timestamp) as TimeStamp, 
    isnull(tbl2.value,tbl1.value) as Value
FROM 
    @t1 tbl1
FULL OUTER JOIN @t2 tbl2 on tbl1.timestamp=tbl2.timestamp

This is Exactly what you need. This is by the book and the only correct way of doing it. This is a trivial task, with just the right solution. Anything else would be much more complex, and slow.