How to join two tables

Question

Table1

Date        v1  
05/01/2010  26          
05/02/2010  31   
05/03/2010  50  

Table2

Date        v2 v3  
05/01/2010  42 jkl  
05/02/2010  28 mno  
05/03/2010  12 pqr  
05/04/2010  13 stv  
05/06/2010  48 8965 

How can I join the above two tables so that my result look similar to below

Date        v1   v2   v3  
05/01/2010  26   42   jkl   
05/02/2010  31   28   mno  
05/03/2010  50   12   pqr  
05/04/2010   0   13   stv  
05/06/2010   0   48   8965 

Answer 1

You use the JOIN keyword:

SELECT table2.Date, COALESCE(v1, 0) AS v1, v2, v3
FROM Table2
LEFT JOIN Table1
ON table1.Date = table2.Date

There are different types of join for example:

• INNER JOIN
• LEFT OUTER JOIN
• RIGHT OUTER JOIN
• FULL OUTER JOIN

If you don't specify which type of join you want, by default you get an INNER join. It seems here that you want a LEFT JOIN or a FULL OUTER JOIN although it isn't clear which from your question.

See this question for an explanation of the different types of joins:

• What is the difference between Left, Right, Outer and Inner Joins?