Is this sql command I wrote going to join these tables as desired?

Question

Problem

I want to join two tables into one table. I have simplified the problem I am dealing at work considerably, but this is the basic idea.

Table 1

ID Value 
1  0 
2  0 
3  0
4  1
5  1
6  0
7  -1 
8  -1 
9  1 
10 0 

Now we have table 2

ID Value 
1  -1 
2  -1 
3  -1 
10  1 

I want to join table1 and table 2 so that I get the following:

ID Value 
1  -1 
2  -1 
3  -1 
4  1 
5  1
6  0 
7 -1 
8 -1 
9  1
10 1 

Attempt

My first attempt was very stupid and wasted a lot of my time since the database is large. It went something like this:

SELECT ID, 
       CASE WHEN Table1.ID = Table2.ID THEN Table2.ID ELSE Table1.ID 
FROM Table1, Table2 

Well, that was an honest mistake, but I still felt pretty n00b doing it. I felt like this when I did it.

I am thinking the following will solve my problem, but I am not that confident. Can someone please comment and let me know if I am on the right path?

SELECT A.ID, A.Value
INNER JOIN B 
ON A.ID = B.ID  

I feel I am so very close, but would appreciate any kind assistance. I am going to go ahead and try this, but not sure if I am maintaining data integrity. I will be more then happy to contribute rep to anyone willing to comment.

Answer 1

You want a left join and coalesce():

select t1.id, coalesce(t2.value, t1.value) as value
from table1 t1 left join
     table2 t2
     on t1.id = t2.id;