SQL join simple problem

This is simple SQL JOIN question and my solution works while trying with sample data but when i do the same with huge data, it fails.

I have two table tbl1

a   b
0   10
1   2
4   5
2    2

Another table tbl2

a    c
1   22
2   18
10   9
98   8

Now i want final table like this

a    b     c

0   10
1    2     22
2    2     18
4    5
10         9
98         8

What i did is:

1) temptbl = select a from tbl1 UNION select a from tbl2;

2) valueA = temptbl left join tbl1 on a

3) valueB = temptbl left join tbl2 on a

4) inner join valueA and ValueB on a

My solution works on small data when i try it locally, but while running it on server, left join produces some random data (steps 1 works, but after step 2, it does not work). Can 开发者_运维问答somebody help me on this? AM i doing wrong? Are there any other solution

Please note, value in column a is unique in both the table.

---

You can do this in pieces:

-- Pick out records whose "a" values are in T1
SELECT T1.a, T1. b, T2.c 
FROM   T1
LEFT OUTER JOIN T2
ON  T1.a=T2.a

UNION

-- Add records whose "a" values are NOT in T1
SELECT T2.a, NULL 'b', T2.c
FROM   T2
WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE T1.a = T2.a)

---

You probably want something like this:

SELECT coalesce(tbl1.a, tbl2.a) as a, /* one of them will be non-null */
       tbl1.b,
       tbl2.c
FROM   tbl1 FULL OUTER JOIN tbl2
ON     tbl1.a = tbl2.a

If your database doesn't support the FULL JOIN, you can UNION together a LEFT JOIN for each of the tables. A LEFT JOIN with only tbl1 on the "left side" will not yield records where only tbl2 has an a value.

---

Edit: Per the OP's request here's the equivalent as a UNION -- since apparently this database doesn't support @DVK's good suggestion of an anti-join:

SELECT a,
       b,
       NULL as c
FROM   tbl1
UNION
SELECT a,
       NULL as b,
       c
FROM   tbl2

---

Try something like

SELECT all.a, b, c
FROM (SELECT DISTINCT a FROM tbl1 UNION SELECT a from tbl 2) all
LEFT OUTER JOIN tbl1 on tbl1.a = all.a
LEFT OUTER JOIN tbl2 on tbl2.a = all.a