SQL syntax to merge two tables?

I have two tables I would like to merge as follows.

Table a:

columns :a1     a2
data    :aaa    a2
         bb开发者_如何学Pythonb    b2

Table b:

columns :a1     b2
data    :aaa    a3
         ccc    c3

The final desired output table would include the following:

Table final:

columns :a1     a2    b2
data    :aaa    a2    a3
         bbb    b2
         ccc          c3

---

SELECT
       a.a1, a.a2, b.b2
FROM 
       a 
FULL JOIN 
       b ON a.a1 = b.a1

---

-- Test data
with TableA(a1, a2) as
(
  select 'aaa', 'a1' union all
  select 'bbb', 'b2'
),
TableB(a1, b2) as
(
  select 'aaa', 'a3' union all
  select 'ccc', 'c3'
)


select
  coalesce(A.a1, B.A1) as a1,
  A.a2,
  B.b2
from TableA as A
  full outer join TableB as B
    on A.a1 = B.a1

Result:

a1   a2   b2
---- ---- ----
aaa  a1   a3
ccc  NULL c3
bbb  b2   NULL

---

You need toJOIN the two tables with something like

SELECT a.a1, a.a2, b.b2 FROM table_a a 
JOIN table_b b ON a.a1 = b.a1

Depending on what you need, you may need a lEFT JOIN (sometimes called an OUTER JOIN). The actual syntax also depends on what database you are using. This example will work on MySQL.

---

SELECT a.a1, a.a2, b.b2
INTO NewTable
FROM a,b
Where b.a1=a.a1

---

This should be what you are looking for:

SELECT
       ISNULL(a.a1, b.a1) AS a1, a.a2, b.b2
FROM 
       a 
FULL OUTER JOIN 
       b ON a.a1 = b.a1

For more info on Outer Joins in general see: http://msdn.microsoft.com/en-us/library/ms187518.aspx